MySQL的参数log_bin_trust_function_creators和binlog的关系真绕呀！

mysql官方文档对这个参数的解释是：

log_bin_trust_function_creators

Property	Value
Command-Line Format	--log-bin-trust-function-creators
System Variable	log_bin_trust_function_creators
Scope	Global
Dynamic	Yes
Type	Boolean
Default Value	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权限（通常创建或修改函数不用SUPER权限）。 设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。 如果变量设置为1，MySQL不会对创建存储函数实施这些限制。

mysql>  DELIMITER //
mysql> CREATE FUNCTION f2()
    -> RETURNS CHAR(36) CHARACTER SET utf8
    -> BEGIN
    ->   RETURN UUID();
    -> 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)
复制

注意：这里是创建或修改存储函数，不是执行函数。对于binlog_format 是否是STATEMENT，控制是不一样的，先看binlog_format 是STATEMENT：

mysql>  set global log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)

mysql>  select id, GET_UPPER_NAME(ID) from test;
+------+--------------------+
| id   | GET_UPPER_NAME(ID) |
+------+--------------------+
|    1 | ZHANGSAN           |
+------+--------------------+
1 row in set (0.02 sec)

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>  show variables like "%format";
+---------------------------+-----------+
| Variable_name             | Value     |
+---------------------------+-----------+
| binlog_format             | STATEMENT |
| default_week_format       | 0         |
| innodb_default_row_format | dynamic   |
| require_row_format        | OFF       |
+---------------------------+-----------+
4 rows in set (0.01 sec)

mysql> 
复制

再看binlog_format 不是STATEMENT时的情况：

mysql>  show variables like "binlog_format";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

mysql>  show variables like "log_bin_trust_function_creators";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.01 sec)

mysql>  select id, GET_UPPER_NAME(ID) from test;
+------+--------------------+
| id   | GET_UPPER_NAME(ID) |
+------+--------------------+
|    1 | ZHANGSAN           |
+------+--------------------+
1 row in set (0.00 sec)

复制

函数的类型：

• DETERMINISTIC 确定的
• NO SQL 没有SQl语句，当然也不会修改数据
• READS SQL DATA 只是读取数据，当然也不会修改数据
• MODIFIES SQL DATA 要修改数据
• CONTAINS SQL 包含了SQL语句

我仔细看了MySQL 5.7官方文档的第23章的最后一节和 mysql 8 文档的第24章的倒数第二节，然后再总结一下要点如下：

• 这个参数在没有binlog启动时不管用；
• 对function和trigger起作用，对stored procedures 和Event Scheduler events不起作用；
• 设置为off时，不准非super用户创建function
• 设置为off时，super用户也只能创建 DETERMINISTIC, NO SQL, or READS SQL DATA的function。
• 执行时，binlog_format为statement时，只能执行安全的function。
• 执行时，binlog_format为非statement时，可以执行任意类型的function。

mysql官方文档对这个参数的解释是：

log_bin_trust_function_creators

Property Value
Command-Line Format --log-bin-trust-function-creators
System Variable log_bin_trust_function_creators
Scope Global
Dynamic Yes
Type Boolean
Default Value 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权限（通常创建或修改函数不用SUPER权限）。 设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。 如果变量设置为1，MySQL不会对创建存储函数实施这些限制。

mysql> DELIMITER //
mysql> CREATE FUNCTION f2()
-> RETURNS CHAR(36) CHARACTER SET utf8
-> BEGIN
-> RETURN UUID();
-> 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)
注意：这里是创建或修改存储函数，不是执行函数。对于binlog_format 是否是STATEMENT，控制是不一样的，先看binlog_format 是STATEMENT：

mysql> set global log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select id, GET_UPPER_NAME(ID) from test;
±-----±-------------------+
| id | GET_UPPER_NAME(ID) |
±-----±-------------------+
| 1 | ZHANGSAN |
±-----±-------------------+
1 row in set (0.02 sec)

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> show variables like “%format”;
±--------------------------±----------+
| Variable_name | Value |
±--------------------------±----------+
| binlog_format | STATEMENT |
| default_week_format | 0 |
| innodb_default_row_format | dynamic |
| require_row_format | OFF |
±--------------------------±----------+
4 rows in set (0.01 sec)

mysql>
再看binlog_format 不是STATEMENT时的情况：

mysql> show variables like “binlog_format”;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| binlog_format | ROW |
±--------------±------+
1 row in set (0.01 sec)

mysql> show variables like “log_bin_trust_function_creators”;
±--------------------------------±------+
| Variable_name | Value |
±--------------------------------±------+
| log_bin_trust_function_creators | OFF |
±--------------------------------±------+
1 row in set (0.01 sec)

mysql> select id, GET_UPPER_NAME(ID) from test;
±-----±-------------------+
| id | GET_UPPER_NAME(ID) |
±-----±-------------------+
| 1 | ZHANGSAN |
±-----±-------------------+
1 row in set (0.00 sec)

函数的类型：

1. DETERMINISTIC 确定的
2. NO SQL 没有SQl语句，当然也不会修改数据
3. READS SQL DATA 只是读取数据，当然也不会修改数据
4. MODIFIES SQL DATA 要修改数据
5. CONTAINS SQL 包含了SQL语句
   我仔细看了MySQL 5.7官方文档的第23章的最后一节和 mysql 8 文档的第24章的倒数第二节，然后再总结一下要点如下：

• 1. 这个参数在没有binlog启动时不管用；
• 2. 对function和trigger起作用，对stored procedures 和Event Scheduler events不起作用；
• 3. 设置为off时，不准非super用户创建function
• 4. 设置为off时，super用户也只能创建 DETERMINISTIC, NO SQL, or READS SQL DATA的function。
• 5. 执行时，binlog_format为statement时，只能执行安全的function。
• 6. 执行时，binlog_format为非statement时，可以执行任意类型的function。
• 7. 导出