MySQL ERROR 1418: 函數/觸發器創建失敗的 binlog 限制與解決方案

錯誤信息

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, 
or READS SQL DATA in its declaration and binary logging is enabled 
(you *might* want to use the less safe log_bin_trust_function_creators variable)

根因：binlog 安全機制

啟用 log_bin 時，MySQL 強制要求創建的函數/存儲過程/觸發器必須聲明以下特性之一，確保主從同步時行為確定性：

函數僅支援前三種聲明；存儲過程支援全部五種。未聲明默認為 CONTAINS SQL，觸發 1418 錯誤。

解決方案一：顯式聲明特性 (推薦)

函數示例

-- 確定性函數
CREATE FUNCTION hello(s CHAR(20)) 
RETURNS CHAR(50) 
DETERMINISTIC
RETURN CONCAT('hello ', s, '!');

-- 只讀數據函數
CREATE FUNCTION get_cid(n CHAR(20)) 
RETURNS INT 
READS SQL DATA
BEGIN
    RETURN (SELECT cid FROM stu WHERE sname = n);
END;

存儲過程示例

CREATE PROCEDURE transfer(IN from_id INT, IN to_id INT, IN amount DECIMAL(10,2))
MODIFIES SQL DATA
BEGIN
    UPDATE account SET balance = balance - amount WHERE id = from_id;
    UPDATE account SET balance = balance + amount WHERE id = to_id;
END;

觸發器示例

CREATE TRIGGER audit_log
AFTER UPDATE ON account
FOR EACH ROW
BEGIN
    -- 觸發器隱含 READS SQL DATA，但建議顯式聲明存儲過程特性
    INSERT INTO audit_log (account_id, old_balance, new_balance) 
    VALUES (OLD.id, OLD.balance, NEW.balance);
END;

解決方案二：放寬信任檢查 (臨時/開發環境)

動態設置 (立即生效，重啟失效)

SET GLOBAL log_bin_trust_function_creators = 1;

啟動參數 (mysqld_safe / systemd)

mysqld --log-bin-trust-function-creators=1

配置文件永久生效

# /etc/my.cnf
[mysqld]
log_bin_trust_function_creators = 1

安全警告：開啟此選項允許創建不確定性函數，可能導致主從數據不一致。生產環境強制要求方案一，嚴禁長期開啟信任模式。

驗證當前設置

SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+

NOW()/RAND() 特殊說明

官方文檔明確：

• NOW()：binlog 記錄時間戳，從庫重放時使用相同時間戳，視為確定性輸入
• RAND()：單次調用種子同步，視為確定性

因此包含 NOW()/RAND() 的函數仍可聲明 DETERMINISTIC，無需因動態函數而放棄聲明。

總結

ERROR 1418 是 MySQL 為保障主從一致性設置的守門機制。標準流程：

1. 分析函數/過程邏輯屬性 (純計算/只讀/寫入)
2. 添加對應特性聲明 (DETERMINISTIC/READS SQL DATA/MODIFIES SQL DATA)
3. 若確需動態 SQL 構建等複雜邏輯，評估風險後臨時開啟 log_bin_trust_function_creators，用完即關
4. 代碼評審環節強制檢查特性聲明完整性