該篇文章來源于線上案例結合官方文檔翻譯和自己理解。
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)
這個函數(shù)定義中沒有包含deterministic,no sql或者read sql data中的一種贯莺，并且binlog是開啟的孙咪。
很顯然是業(yè)務創(chuàng)建函數(shù)的時候觸發(fā)了該錯誤，那為何會導致這種錯誤呢夺刑，這是由于mysql復制要保證主從數(shù)據(jù)的一致決定的缅疟，這些函數(shù)創(chuàng)建語句會被記錄在binlog中，然后復制到slave執(zhí)行遍愿。但是這些函數(shù)在slave上執(zhí)行的時候有可能導致主從數(shù)據(jù)不一致存淫，為了避免該問題，mysql拒絕直接創(chuàng)建函數(shù)沼填，創(chuàng)建函數(shù)失敗桅咆，返回異常。那為何mysql會有這個機制坞笙，對于函數(shù)的創(chuàng)建又將如何處理岩饼？
在一些場景下，一條語句在分別在主從執(zhí)行可能導致不同的結果薛夜，在slave執(zhí)行復制語句是通過sql線程執(zhí)行的忌愚，而SQL線程是有所有的權限，因此有可能出現(xiàn)一種情況就是却邓，函數(shù)中有危險語句在master上執(zhí)行并不會有問題硕糊，但是當slave的SQL線程權限不同的時候，就會執(zhí)行到這些危險語句腊徙，從而導致主從數(shù)據(jù)不一致简十。如果一個函數(shù)更改數(shù)據(jù)的結果是不確定的，或者是不可重復的撬腾，也會導致主從數(shù)據(jù)不一致螟蝙，或者導致更改的數(shù)據(jù)和原始數(shù)據(jù)不一致（這主要在備份恢復中出現(xiàn)）。
通常這些問題出現(xiàn)在復制是語句模式的情況下民傻，如果使用的行模式胰默，binlog記錄的是執(zhí)行SQL語句影響到的具體的行（不是執(zhí)行的SQL語句），當routines或者觸發(fā)器執(zhí)行漓踢，binlog中記錄的也是更改行信息牵署，而不是影響行變更的SQL語句，對于存儲過程也是一樣喧半，并不是記錄call 語句奴迅，也是記錄更改的行記錄。對于函數(shù)挺据，日志記錄的是函數(shù)更改的行記錄取具，而不是函數(shù)調用語句脖隶。對于觸發(fā)器，記錄的是觸發(fā)器更改之后的行記錄暇检。因此在slave這邊产阱，看的是變更之后的行記錄，而不是這些子程序的調用語句块仆。因此在行模式則不會導致主從不一致心墅。
如果復制模式是混合模式，除非行模式能保證正確的結果榨乎，不然上面的結果記錄到binlog采用的是語句模式怎燥。在混合模式下，當一個存儲過程蜜暑，函數(shù)铐姚，觸發(fā)器，事件包含了對于語句模式不安全的SQL肛捍，這些語句就會標記為不安全的并且采用行模式記錄在binlog隐绵。
在mysql中，下面有一些條件是對函數(shù)有效拙毫，對存儲過程或者事件無效依许，或者沒有開啟binlog也是無效的：
? ? ?1.創(chuàng)建或者更改一個函數(shù)必須要有super權限
? ? ?2.創(chuàng)建一個函數(shù)，必須要定義為確定結果的或者是不更改數(shù)據(jù)的缀蹄。否則峭跳，就會被認為是對復制或者數(shù)據(jù)恢復是不安全的，也就是報錯1418
默認情況下缺前，要創(chuàng)建一個函數(shù)蛀醉，deterministic,no sql,reads sql data中三個屬性中的一個必須被顯示指定，這樣就能確認函數(shù)對結果集的影響衅码，否則就會報錯1418拯刁，函數(shù)創(chuàng)建不成功。
下面這個函數(shù)就是確定結果的逝段，因此是可以創(chuàng)建成功的：
CREATE FUNCTION f1(i INT)
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
? RETURN i;
END;
下面函數(shù)使用了uuid()垛玻，這個函數(shù)的結果是不確定的，因此下面函數(shù)是非確定結果的奶躯，是復制不安全的帚桩，因此創(chuàng)建失敗：
CREATE FUNCTION f2()
RETURNS CHAR(36) CHARACTER SET utf8
BEGIN
? RETURN UUID();
END;
下面這個函數(shù)更改了數(shù)據(jù)巫糙，也是不安全的：
CREATE FUNCTION f3(p_id INT)
RETURNS INT
BEGIN
? UPDATE t SET modtime = NOW() WHERE id = p_id;
? RETURN ROW_COUNT();
END;
評估一個函數(shù)是否安全取決于創(chuàng)建者是否清晰的知道這點朗儒，mysql并不會檢查一個函數(shù)定義為確定結果但實際上產(chǎn)生了不確定的結果。在函數(shù)的定義中可以指定deteministic來顯示的說明函數(shù)是安全的参淹，但是在函數(shù)體中定義可以使用不安全的語句醉锄。
這種情況，mysql會認為是安全的浙值，可以創(chuàng)建函數(shù)恳不，但實際上這種函數(shù)調用對主從數(shù)據(jù)可能導致不一致。
如果試圖執(zhí)行一個函數(shù)开呐，若binlog_format 設置為statement模式烟勋，這個函數(shù)屬性必須要顯示指定為deterministic才行執(zhí)行，否則就會報錯1418異常并且函數(shù)不會被執(zhí)行筐付。但是設置了log_bin_trust_function_creators = 1,則可以正常執(zhí)行卵惦。
比如：
先set global log_bin_trust_function_creators =1 創(chuàng)建了一個沒有指定deterministic的函數(shù):
CREATE FUNCTION f2()
RETURNS CHAR(36) CHARACTER SET utf8
BEGIN
? RETURN UUID();
END;
再set globallog_bin_trust_function_creators? = 0;set binlog_format = statement;
然后調用該函數(shù)
mysql> ?select f2();
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)
調用函數(shù)失敗，報錯1418瓦戚。
如果設置binlog_format=mixed 或者row 模式或者set globallog_bin_trust_function_creators? = 1 沮尿，select f2()則可以正常執(zhí)行了（創(chuàng)建函數(shù)的時候沒有指定determinisric 關鍵字），或者在函數(shù)定義中指定deterministic屬性较解，也是可以正常執(zhí)行的畜疾。
因為mysql并沒有檢查一個函數(shù)在創(chuàng)建的時候是否是正是確定結果的，因此調用一個指定了deterministic關鍵字的函數(shù)在statement模式下可能帶來的結果是不安全的印衔，因為這樣的函數(shù)中可能包含不安全的語句啡捶。在statement模式下，調用這種函數(shù)會觸發(fā)warning奸焙，如果是mixed或者row模式瞎暑，不會有warning,函數(shù)中語句會以row模式進行復制。如下：
創(chuàng)建函數(shù)的時候明確指定deterministic屬性
CREATE FUNCTION f3(p_id INT)
RETURNS INT
deterministic
BEGIN
? UPDATE t1 SET x=1 WHERE x = p_id;
? RETURN ROW_COUNT();
END;
這個函數(shù)不管log_bin_trust_function_creators? ?設置為多少都能創(chuàng)建成功与帆，因此指定的結果是確定的金顿。
在format_format=statement模式下調用：
mysql> select f3(1)//
+-------+
| f3(1) |
+-------+
| ? ? 0 |
+-------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings//
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?|
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note ?| 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
產(chǎn)生了warning，提示語句在statement模式下是不安全的（和log_bin_trust_function_creators 設置無關鲤桥，設置1或者0揍拆，都會有warning產(chǎn)生）
在binlog_format = row模式下調用：
mysql> set binlog_format=row//
Query OK, 0 rows affected (0.00 sec)
mysql> select f3(1)//
+-------+
| f3(1) |
+-------+
| ? ? 0 |
+-------+
1 row in set (0.00 sec)?
并不會提示warning，在mixed模式下也是一樣茶凳，不會有warning嫂拴，都是以row模式進行binlog記錄。
可以看出贮喧，即使我們創(chuàng)建函數(shù)的時候繞過了mysql的檢查筒狠，成功創(chuàng)建了函數(shù)，但是在調用的時候箱沦，mysql還是會根據(jù)binlog_format來確認結果辩恼，從而選擇是row模式，還是statement，并作出提示灶伊。
為了避開創(chuàng)建函數(shù)的檢查條件疆前，可以設置log_bin_trust_function_creators?= 1，這樣mysql就不會進行檢查了聘萨，默認設置0竹椒，這個參數(shù)只能設置global 級別。也可以在server啟動的時候加上--log_bin_trust_function_creators?=1選項米辐。如果binlog沒有開啟胸完，log_bin_trust_function_creators?參數(shù)也就沒作用。
觸發(fā)器和函數(shù)類似翘贮，因此前面講的關于函數(shù)的說明同樣對觸發(fā)器有效赊窥，除了下面這個：create trigger語句沒有deterministic屬性，因此觸發(fā)器總是被假定為deterministic狸页，但是在一些場景下這個假設會失效锨能。例如，uuid()函數(shù)就是nondeterministic的（不能復制的）肴捉，因此在使用這些函數(shù)尤其注意腹侣。觸發(fā)器會更新表，因此create trigger語句沒有更新表所需要的權限時齿穗，會和創(chuàng)建函數(shù)一樣報異常傲隶。在slave端，slave通過觸發(fā)器的definer屬性來決定觸發(fā)器的創(chuàng)建者窃页，這就就決定了觸發(fā)器所需的權限跺株。
如果一個函數(shù)更新了數(shù)據(jù) ，那么mysql會以select語句的形式記錄調用方式脖卖，這就避免了數(shù)據(jù)更新無法記錄日志從而使得無法復制乒省。一般情況下，select語句是不會記錄子binlog中的畦木，但是一個select語句有可能調用一個函數(shù)
導致數(shù)據(jù)變更袖扛。為了解決這種方式，當select使用的函數(shù)更新了數(shù)據(jù)十籍，那么函數(shù)調用方式就以select語句的形式記錄在binlog中（這里有個前提蛆封，binlog_format=statment。row和mixed不存在這個問題）
CREATE FUNCTION f1(a INT) RETURNS INT
BEGIN
? IF (a < 3) THEN
? ? INSERT INTO t2 VALUES (a);
? END IF;
? RETURN 0;
END;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);
SELECT f1(a) FROM t1;
在binlog中可以發(fā)現(xiàn)是以select語句記錄binlog的：
如果是row或者mixed模式勾栗，則都是以row模式進行binlog記錄惨篱。
當一個函數(shù)中調用一個存儲過程的時候發(fā)生了錯誤，這樣mysql同樣會以select語句的形式記錄函數(shù)的調用围俘，這種情況下砸讳，binlog中記錄的select語句同時會記錄
期望的error code,在slave端琢融，如果同樣的錯誤出現(xiàn)，那么這就是期望的結果同時復制不會中斷簿寂，否則復制會中斷漾抬。
binlog記錄函數(shù)的調用方式而不是記錄函數(shù)中的執(zhí)行語句對復制是一種安全的結果，主要有兩個方面的原因：
在master slave上函數(shù)的的調用路徑可能不同陶耍，其次執(zhí)行語句的SQL 線程有所有權限奋蔚，和master可能不同她混，master可能沒這么大的權限烈钞，但是slave上有，就有可能導致主從上執(zhí)行結果不同坤按。
這樣的結果就是雖然一個用戶需要create routine的權限來創(chuàng)建一個函數(shù)毯欣，用戶可以寫很危險的語句在函數(shù)中，而且只能在slave上通過有所有權限的SQL 線程來執(zhí)行臭脓。例如酗钞，master slave有不同的server id 1 和2，一個用戶可以在master上創(chuàng)建一個不安全的函數(shù)unsafe_func():
mysql>?delimiter?//
?mysql>?CREATE?FUNCTION?unsafe_func?()?RETURNS?INT
BEGIN?
IF?@@server_id=2?THEN?dangerous_statement;?
END?IF;?
?RETURN?1;
END;??
//
?mysql>?delimiter?;?
mysql>?INSERT?INTO?t?VALUES(unsafe_func());
函數(shù)創(chuàng)建語句和插入語句都會記錄在binlog中来累，因此slave可以執(zhí)行這些語句砚作，因為SQL 線程有所有權限（往往主庫創(chuàng)建和調用函數(shù)的用戶權限比較有限），因此將會執(zhí)行到這些危險語句嘹锁，因此葫录，在master slave
上這個函數(shù)調用產(chǎn)生來不一樣的結果，所以它不是復制安全的领猾。
為了避免開啟binlog的mysql上的這種危險情況米同，函數(shù)的創(chuàng)建這必須有所有權限，不僅僅是必須的create routine權限摔竿。同樣的面粮，alter function也是一樣。沒有super權限继低，會發(fā)生如下錯誤：
ERROR 1419 (HY000): You do not have the SUPER privilege and
binary logging is enabled (you *might* want to use the less safe
log_bin_trust_function_creators variable)
如果不想創(chuàng)建函數(shù)的用戶擁有super權限熬苍，可以設置全局參數(shù)log_bin_trust_function_creators =1,或者在服務啟動的時候增加參數(shù)--log_bin_trust_function_creatros =1,如果binlog沒有開啟，這個參數(shù)則沒有作用袁翁。
如果一個函數(shù)更新數(shù)據(jù)是不確定的柴底，是不可重復的，這會導致兩個不良后果：
導致slave和master的數(shù)據(jù)不一致
恢復數(shù)據(jù)的時候導致和原始數(shù)據(jù)不同（這里主要是數(shù)據(jù)恢復時出現(xiàn)）
為了處理這些問題梦裂，mysql做如下強制要求：在master上似枕，拒絕創(chuàng)建或者變更一個函數(shù)，除非定義的函數(shù)是確定結果的或者不更新數(shù)據(jù)的年柠。這兩個函數(shù)屬性的作用如下：
deterministic 或者not deterministic屬性決定一個函數(shù)對于給定的輸入是否每次產(chǎn)生相同的結果凿歼，如果沒有屬性給定褪迟，默認是not deteministic的。定義一個函數(shù)是deterministic的答憔，需要明確指定deterministic屬性味赃。
contains sql ,no sql,reads sql data和modify sql data屬性指出一個函數(shù)是讀數(shù)據(jù)還是更新數(shù)據(jù)，no sql 或者reads sql data明確一個函數(shù)是不更新數(shù)據(jù)的虐拓。如果不指定屬性心俗，默認是contains sql,如果一個函數(shù)是明確不更新數(shù)據(jù)的話，
需要特別指定no sql蓉驹、reads sql data中的一種城榛。默認情況下，要使得create function語句能否執(zhí)行态兴，至少需要deteministic ,no sql或者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)
如果設置log_bin_trust_function_creators =1 ,則deterministic 或者不更新數(shù)據(jù)的屬性被忽略。
調用存儲過程瞻润，binlog記錄是在語句執(zhí)行階段喘垂，而不是調用階段。也就是說binlog記錄的不是call 語句绍撞，而是存儲過程中真正執(zhí)行的語句正勒。因此在master更新的數(shù)據(jù)在slave上同樣被更新到，這就避免了同一個存儲過程在主從上導致不一樣的結果傻铣。
通常章贞，binlog中記錄的存儲過程中執(zhí)行的語句只要有同樣的權限就可以在另外一臺機器上被執(zhí)行。有一個特殊場景需要注意:在非標準的上下文中存儲過程的執(zhí)行結果不完全相同：
一個語句記錄在binlog中可以要包含關聯(lián)一個本地的過程變量矾柜，這些變量在存儲過程外部是不存在的阱驾，因此一個引用了變量的語句不能直接按照語句的原樣進行記錄binlog,而是為了寫入binlog每一個被引用的變量用如下的結構進行替換：
?NAME_CONST(var_name, var_value)
var_name 是本地的變量名，var_value是一個常量怪蔑，表示引用這個變量的語句執(zhí)行時候該變量的值,name_const()函數(shù)的值為var_value,名字為var_name.因此里覆，如果直接調用該函數(shù)，可以得到如下結果：
mysql> SELECT NAME_CONST('myname', 14);
+--------+|myname|
+--------+|14|
+--------+
name_const()函數(shù)使得binlog中記錄在slave上執(zhí)行和存儲過程中的原始語句在 master上執(zhí)行產(chǎn)生同樣的效果缆瓣。使用create table...select語句時候喧枷，當select 語句中當列表達式引用本地變量的時候，使用name_cons函數(shù)的時候會導致一些問題弓坞。轉變這些引用為name_const表達式的過程中導致master和slave為不同的列名隧甚，或者name太長也不能作為一個合法的列標識。一種解決方式就是為列名提供一個別名引用本地變量渡冻。下面語句myvar 的值為1:
create table t1 select myvar;
記錄在binlog中會被重寫為如下語句：
create table t1 select name_const(myvar ,1);
為了確保master和slave有相同的列名戚扳，將語句寫成如下方式：
create table t1 select myvar as myvar;
binlog中會被記錄為如下：
create table t1 select name_const(myvar ,1) as myvar; 這樣就能確保主從的列名都是一致的。
另外一條語句記錄到binlog中可能包含引用用戶定義的變量族吻。為處理這個特點帽借，mysql寫入一個set 語句到binlog確保該變量在slave上和master上有相同的值珠增。例如，一條語句引用了變量@my_var,該語句在binlog中會處理為如下語句砍艾，value就是master上@my_var變量的值：
set @my_var = value;
存儲過程調用可以在包含commit或者rollback的事務中蒂教，事務的上下文會被記錄下來，這樣事務中的存儲過程部分就能夠在slave中正確的復制執(zhí)行脆荷。也就是說凝垛，mysql記錄存儲過程中真實執(zhí)行和更改數(shù)據(jù)的語句，同時必要的時候會記錄begin,commit,rollback蜓谋。例如梦皮，一個存儲過程只更新事務表而且在一個事務中執(zhí)行，如果被回滾了孤澎，那么這部分更新不會被記錄在binlog中届氢。如果一個存儲過程調用在一個commit的事務中欠窒，更新前后會記錄begin和commit語句覆旭。如果一個存儲過程調用在一個rollback的事務中，這些被記錄會以同樣的規(guī)則被記錄在binlog中岖妄，當其以獨立的方式執(zhí)行時這些語句都會被應用：
事務表的更新不會記錄在binlog中
非事務表的更新會被記錄在binlog中型将，因為rollback不會回滾掉非事務表的更新
如果同時更新事務表和非事務表，這些記錄會前后會記錄begin和rolleback語句荐虐，因此slave更新和回滾的記錄就和master上更新和回滾的記錄一致七兜。
在statement復制模式下，如果一個函數(shù)調用了一個存儲過程福扬，binlog中不會記錄call語句腕铸。在這種場景下，只有函數(shù)調用語句被記錄（如果調用它的語句被記錄在binlog）或者是一個do語句（如果調用它的數(shù)據(jù)沒有記錄）铛碑，正因如此狠裹，在一個函數(shù)中調用存儲過程需要小心，盡管存儲過程自身是安全的汽烦。也就是說涛菠，在一個函數(shù)中調用存儲過程，binlog中只會記錄函數(shù)的調用撇吞，不會記錄存儲過程的調用俗冻。
說了這么多，就是為了說明mysql對于創(chuàng)建函數(shù)的一些限制牍颈，以及調用函數(shù)如何記錄binlog迄薄，以及我們在日常使用函數(shù)過程中，應該注意什么煮岁。
再回到業(yè)務的報錯讥蔽，創(chuàng)建函數(shù)失敗死姚，函數(shù)中沒有指定deterministic ，no sql,reads sql data 屬性勤篮，從而導致報錯都毒，解決辦法如下：
1.如果函數(shù)只是為了查詢方便而創(chuàng)建的，不更改數(shù)據(jù)碰缔，那么可以指定reads sql data账劲，deterministic屬性中的一種即可
2.如果函數(shù)要更改數(shù)據(jù)，那么可以指定deterministic屬性通過創(chuàng)建函數(shù)金抡，但是為了確保復制安全瀑焦，需要將binlog_format設置為row模式
