MySQL 无权限创建函数、触发器与log_bin_trust_function_creators参数

开发创建触发器报错无权限

如果创建函数则是以下报错：

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)

看到报错里有提到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”.

二进制日志的一个重要功能是用于主从复制，而函数、触发器有可能导致主从数据不一致。所以当开启二进制日志后，参数log_bin_trust_function_creators就会生效，限制函数、触发器的创建、修改、调用。

 

解决方法

1. 给用户SUPER权限

不推荐

2. 将参数log_bin_trust_function_creators设置为1

 数据库没有使用主从，或者信任开发不会写出导致主从数据不一致的函数和触发器，可以选择此方法。

set global log_bin_trust_function_creators=1;

 动态设置的方式会在MySQL重启后失效，还需要修改my.cnf设置，加上log_bin_trust_function_creators=1。

 

3. 定义时指明函数类型

 根据前面官方文档描述，如果参数为0，则只支持定义以下几种类型的函数：DETERMINISTIC, NO SQL 和 READS SQL DATA。相当于明确告诉MySQL这个函数不会修改数据。

MySQL中函数类型如下：

• DETERMINISTIC：确定性函数（只要输入确定，返回结果是一定的）
• NO SQL：无SQL语句
• READS SQL DATA： 只读函数
• MODIFIES SQL DATA：会修改数据
• CONTAINS SQL：包含SQL语句

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> DELIMITER //
mysql> CREATE FUNCTION GET_UPPER_NAME(emp_id INT)
    -> RETURNS VARCHAR(12)
    -> READS SQL DATA
    -> BEGIN
    ->   RETURN(SELECT UPPER(NAME) FROM TEST WHERE ID=emp_id);
    -> END
    -> //
Query OK, 0 rows affected (0.01 sec)
 
mysql> DELIMITER ;
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)

参考：https://www.cnblogs.com/kerrycode/p/7641835.html