Mysql ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA

標籤：

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配置了複製，複製功能也就意味著Master資料的變化，會同步到Slave。對於函數，Mysql要求函數不能修改資料。解決辦法：1、建立函數的時候，明確說明我不會修改資料。建立函數的時候有5個選項：a、DETERMINISTIC 確定性b、NO SQL 沒有包含SQl語句，當然也不會修改資料c、READS SQL DATA 只是讀取資料，當然也不會修改資料d、MODIFIES SQL DATA 要修改資料e、CONTAINS SQL 包含了SQL語句怎麼理解DETERMINISTIC確定性？可以認為輸入相同，方法執行過程，輸出也是相同的。也就是方法的可重新進入性。不可重新進入的方法：方法內使用了靜態資料，使用了malloc和free，使用了標準I/O函數。建立函數必須明確指出a、b、c三個選項中的一個，才能執行成功。如下：mysql> show variables like ‘log_bin%‘;+---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| log_bin                         | ON    || log_bin_trust_function_creators | OFF   || log_bin_trust_routine_creators  | OFF   |+---------------------------------+-------+3 rows in setmysql> create function fun1 (num int(9)) returns int(9)beginreturn 1;end;1418 - 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> create function fun1 (num int(9)) returns int(9) reads sql databeginreturn 1;end;Query OK, 0 rows affectedmysql> drop function fun1;Query OK, 0 rows affectedmysql> create function fun1 (num int(9)) returns int(9) deterministicbeginreturn 1;end;Query OK, 0 rows affected2、告訴Mysql，信任我，方法不會修改資料，Mysql不再檢查，即使修改了資料。這個時候要靠你信守承諾，否則後果自負。mysql> drop function fun1;Query OK, 0 rows affectedmysql> set global log_bin_trust_function_creators=on;Query OK, 0 rows affectedmysql> show variables like ‘log_bin%‘;+---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| log_bin                         | ON    || log_bin_trust_function_creators | ON    || log_bin_trust_routine_creators  | ON    |+---------------------------------+-------+3 rows in setmysql> create function fun1 (num int(9)) returns int(9) modifies sql databeginreturn 1;end;Query OK, 0 rows affected

Mysql ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA