MySQL的有個參數log_bin_trust_function_creators，官方文檔對這個參數的介紹、解釋如下所示： log_bin_trust_function_creators Command-Line Format --log-bin-trust-function-creators S... ...
MySQL的有個參數log_bin_trust_function_creators，官方文檔對這個參數的介紹、解釋如下所示：
log_bin_trust_function_creators
| Command-Line Format | --log-bin-trust-function-creators | |
| System Variable | Name | log_bin_trust_function_creators |
| Variable Scope | Global | |
| Dynamic Variable | Yes | |
| Permitted Values | Type | boolean |
| Default | FALSE | |
This variable applies when binary logging is enabled. It controls whether stored function creators can be trusted not to create stored functions that will cause unsafe events to be written to the binary log. If set to 0 (the default), users are not permitted to create or alter stored functions unless they have the SUPER privilege in addition to the CREATE ROUTINE or ALTER ROUTINE privilege. A setting of 0 also enforces the restriction that a function must be declared with the DETERMINISTIC characteristic, or with the READS SQL DATA or NO SQL characteristic. If the variable is set to 1, MySQL does not enforce these restrictions on stored function creation. This variable also applies to trigger creation. See Section 23.7, “Binary Logging of Stored Programs”.
簡單介紹一下，當二進位日誌啟用後，這個變數就會啟用。它控制是否可以信任存儲函數創建者，不會創建寫入二進位日誌引起不安全事件的存儲函數。如果設置為0（預設值），用戶不得創建或修改存儲函數，除非它們具有除CREATE ROUTINE或ALTER ROUTINE特權之外的SUPER許可權。 設置為0還強制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性聲明函數的限制。 如果變數設置為1，MySQL不會對創建存儲函數實施這些限制。 此變數也適用於觸發器的創建。 請參見第23.7節“Binary Logging of Stored Programs”。
下麵我們測試一下，當開啟二進位日誌後，如果變數log_bin_trust_function_creators為OFF，那麼創建或修改存儲函數就會報“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)”這樣的錯誤，如下所示：
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |+---------------+-------+
| log_bin | ON |+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%log_bin_trust_function_creators%';
+---------------------------------+-------+
| Variable_name | Value |+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |+---------------------------------+-------+
1 row in set (0.00 sec)
mysql>
mysql> DELIMITER //mysql> CREATE FUNCTION GET_UPPER_NAME(emp_id INT)
-> RETURNS VARCHAR(12)
-> BEGIN-> RETURN(SELECT UPPER(NAME) FROM TEST WHERE ID=emp_id);
-> END -> //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)
mysql>
在調用存儲函數時，也會遇到這個錯誤,如下測試所示：
mysql> DELIMITER ;
mysql> set global log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER //mysql> CREATE FUNCTION GET_UPPER_NAME(emp_id INT)
-> RETURNS VARCHAR(12)
-> BEGIN-> RETURN(SELECT UPPER(NAME) FROM TEST WHERE ID=emp_id);
-> END -> //Query OK, 0 rows affected (0.00 sec)mysql> SELECT ID,-> GET_UPPER_NAME(ID)
-> FROM TEST; -> //+------+--------------------+
| ID | GET_UPPER_NAME(ID) |
+------+--------------------+
| 100 | KERRY |
| 101 | JIMMY |
+------+--------------------+
2 rows in set (0.00 sec)
mysql> DELIMITER ;
mysql> set global log_bin_trust_function_creators=0;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT ID,-> GET_UPPER_NAME(ID)
-> FROM TEST;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)
mysql>
那麼為什麼MySQL有這樣的限制呢？ 因為二進位日誌的一個重要功能是用於主從複製，而存儲函數有可能導致主從的數據不一致。所以當開啟二進位日誌後，參數log_bin_trust_function_creators就會生效，限制存儲函數的創建、修改、調用。那麼此時如何解決這個問題呢？官方文檔介紹如下，具體可以參考23.7 Binary Logging of Stored Programs
