MySQL 建立函數報錯 ERROR 1418 完整解決方案

MySQL 建立函數報錯 ERROR 1418 完整解決方案

錯誤訊息：

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)

根本原因

啟用了 Binary Log (log_bin=ON) 時，MySQL 為了確保主從複制安全，要求建立函數/觸發器/事件必須明確宣告其確定性特徵：

• DETERMINISTIC：相同輸入必定產生相同輸出 (推薦)
• NO SQL：不包含 SQL 陳述式
• READS SQL DATA：僅讀取資料，不修改
• MODIFIES SQL DATA：會修改資料 (函數不支援)
• CONTAINS SQL：包含 SQL 但不讀寫資料

若未宣告，MySQL 拒絕建立，除非開啟 log_bin_trust_function_creators 信任建立者。

解決方案一：全域開啟信任變數 (立即生效、重啟失效)

mysql> SET GLOBAL log_bin_trust_function_creators = 1;
# 或
mysql> SET GLOBAL log_bin_trust_function_creators = ON;

# 驗證
mysql> SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+

解決方案二：配置檔永久生效 (需重啟)

my.cnf / my.ini：

[mysqld]
log_bin_trust_function_creators = 1
# 5.7+ 也可用
log_bin_trust_routine_creators = 1

修改後執行 systemctl restart mysqld 或 net restart mysql。

解決方案三：函數定義時明確宣告特徵 (最佳實務)

CREATE FUNCTION `fn_calc_total`(price DECIMAL(10,2), qty INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
NO SQL
BEGIN
    RETURN price * qty;
END;

• DETERMINISTIC：告知優化器相同參數結果固定，可啟用查詢快取。
• NO SQL / READS SQL DATA：視函數內容誠實宣告。
• 若函數含 SELECT 查表，需用 READS SQL DATA。

方案比較與建議

常見誤區

• 以為只影響 FUNCTION：同樣影響 TRIGGER、EVENT 建立。
• 忽略 DETERMINISTIC 效能紅利：標註後查詢優化器可快取結果，大幅提升重複呼叫效能。
• 誤用 MODIFIES SQL DATA：函數 不支援 修改資料，僅程序 (PROCEDURE) 可用。

SHOW CREATE FUNCTION fn_calc_total\G
# 輸出中會顯示: DETERMINISTIC, NO SQL 等特徵