MySQL創(chuàng)建Function時(shí)報(bào)的1418錯(cuò)誤代號(hào)解決辦法

以下是引用片段：
ErrorCode:1418
ThisfunctionhasnoneofDETERMINISTIC,NOSQL,orREADSSQLDATAinitsdeclarationandbinaryloggingisenabled(you*might*wanttousethelesssafelog_bin_trust_function_creatorsvariable)-
 

(0mstaken)

　　解決方法如下：

　　1. mysql> SET GLOBAL log_bin_trust_function_creators = 1;

　　2. 系統(tǒng)啟動(dòng)時(shí) --log-bin-trust-function-creators=1

　　3. 在my.ini(linux下為my.conf)文件中 [mysqld] 標(biāo)記后加一行內(nèi)容為 log-bin-trust-function-creators

如果在create function的時(shí)候有 1418的錯(cuò)語(yǔ)的時(shí)候:那么只需要執(zhí)行set global log_bin_trust_routine_creators=1;

然后怎么create function 都不會(huì)有問(wèn)題(當(dāng)然,你的function必段正確哦),這是mysql的一個(gè)bug,搞不懂為什么,反正這樣做就OK了.

另外，也可以直接在配置文件my.cnf中添加如下行[mysqld] log_bin_trust_routine_creators=1;

這個(gè)命令可以在sql editor的環(huán)境中運(yùn)行，并不需要重新啟動(dòng)服務(wù)哦。

MySQL自5.0之後終於支援SP( Store Procedures )，
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) Assessment of the nature of a function is based on the “honesty” of the creator: MySQL does not check that a function declared DETERMINISTIC is free of statements that produce non-deterministic results.

To relax the preceding conditions on function creation (that you must have the SUPER privilege and that a function must be declared deterministic or to not modify data), set the global log_bin_trust_function_creators system variable to 1. By default, this variable has a value of 0, but you can change it like this:

mysql> SET GLOBAL log_bin_trust_function_creators = 1;
You can also set this variable by using the --log-bin-trust-function-creators=1 option when starting the server.
1. mysql> SET GLOBAL log_bin_trust_function_creators = 1;
2. 系統(tǒng)啟動(dòng)時(shí)，加上--log-bin-trust-function-creators 參數(shù)為1
3. 直接在my.ini的[mysqld]區(qū)段加上log-bin-trust-function-creators=1
當(dāng)然我想大多數(shù)人會(huì)用第三種方法，
ok...繼續(xù)我的SP學(xué)習(xí),good luck..

二、mysql ERROR 1418 (HY000)

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)

應(yīng)該是我們開(kāi)啟了bin-log, 我們就必須指定我們的函數(shù)是否是
1 DETERMINISTIC 不確定的
2 NO SQL 沒(méi)有SQl語(yǔ)句，當(dāng)然也不會(huì)修改數(shù)據(jù)
3 READS SQL DATA 只是讀取數(shù)據(jù)，當(dāng)然也不會(huì)修改數(shù)據(jù)
4 MODIFIES SQL DATA 要修改數(shù)據(jù)
5 CONTAINS SQL 包含了SQL語(yǔ)句

其中在function里面，只有   DETERMINISTIC, NO SQL 和   READS SQL DATA 被支持。如果我們開(kāi)啟了 bin-log, 我們就必須為我們的function指定一個(gè)參數(shù)。

我們可以通過(guò)設(shè)置如下的參數(shù)來(lái)關(guān)掉這個(gè)。

SET GLOBAL log_bin_trust_function_creators = 1;

(you *might* want to use the less safe log_bin_trust_function_creators
variable)

服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]