創建存儲函數時遇到 ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled。本文從二進制日志複製原理出發,系統講解錯誤成因、兩種標準解決方案與生產環境最佳實踐。
MySQL 主從複製基於二進制日志。存儲程序(函數/過程/觸發器)的創建/調用語句會寫入 Binlog,在從庫重放。若程序非確定性(相同輸入產生不同結果)或修改數據,從庫重放結果可能與主庫不一致,導致數據漂移。
為防風險,MySQL 強制要求創建程序時顯式聲明以下特性之一:
DETERMINISTIC — 相同輸入必產生相同輸出NO SQL(不含 SQL)或 READS SQL DATA(僅讀數據)-- 函數示例
CREATE FUNCTION `calc_bonus`(base_salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC -- 關鍵聲明
READS SQL DATA -- 或 NO SQL
BEGIN
RETURN base_salary * 0.15;
END;
-- 過程示例
CREATE PROCEDURE `log_access`(IN user_id INT)
DETERMINISTIC
MODIFIES SQL DATA -- 明確聲明會修改數據
BEGIN
INSERT INTO access_log(user_id, access_time) VALUES (user_id, NOW());
END;
聲明規則:
DETERMINISTIC / NOT DETERMINISTIC 二選一,預設 NOT DETERMINISTICCONTAINS SQL / NO SQL / READS SQL DATA / MODIFIES SQL DATA 四選一,預設 CONTAINS SQLDETERMINISTIC) 或 (NO SQL 或 READS SQL DATA)NOW()、RAND() 不影響確定性判定:Binlog 記錄時間戳/隨機種子,從庫重放結果一致設置全域變量 log_bin_trust_function_creators = 1,允許創建未聲明特性的程序:
SET GLOBAL log_bin_trust_function_creators = 1;
# mysqld 啟動命令添加
--log-bin-trust-function-creators=1
[mysqld]
log_bin_trust_function_creators = 1
| 維度 | 方案一:顯式聲明 | 方案二:信任創建者 |
|---|---|---|
| 安全性 | ✅ 強制審視程序特性 | ⚠️ 依賴開發者自覺,易引入非確定性邏輯 |
| 維護成本 | 創建時一次性聲明 | 零成本,但隱患長期存在 |
| 主從一致性 | 有保障 | 可能漂移 |
| 適用場景 | 所有生產環境、規範團隊 | 僅開發/測試環境、極其信任的內部工具 |
DETERMINISTIC + READS SQL DATA/MODIFIES SQL DATAUUID()、SYSDATE() 等)log_bin_trust_function_creatorsCHECKSUM TABLE 對比主從數據一致性錯誤 1418 不是阻礙,是護欄。花 30 秒添加聲明,換來的是主從架構下的數據安寧。