一、报错过程
在MySQL8.0.13上创建自定义函数时出现此错误。
二、报错原因
因为MySQL8.0二进制日志默认开启,二进制日志的一个重要功能是用于主从复制,而存储函数有可能导致主从的数据不一致。所以当开启二进制日志后,参数log_bin_trust_function_creators就会生效,限制存储函数的创建、修改、调用。
点我,查看官方文档
三、解决问题
方法一
如果未使用主从复制,则设置为信任即可。具体做法如下:
- 设备MySQL全局变量
set global log_bin_trust_function_creators=1;
- 前一步设置变量重启MySQL后失效,所以我们还必须在my.cnf配置文件中[mysqld]模块下加上log_bin_trust_function_creators=1,这样就会永久生效。
方法二
明确指明函数的类型,明确告知MySQL服务器这个函数不会修改数据。
1、授予创建创建函数的用户super权限
grant super on *.* to 'user01'@'192.168.1.25';
注意: super是全局级别的权限,所以必须是:*.*
。
2、在创建function的语句中添加函数类型。
声明函数类型的参数有:DETERMINISTIC、NO SQL、READS SQL DATA、MODIFIES SQL DATA和CONTAINS SQL。其中只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。这样一来相当于明确的告知MySQL服务器这个函数不会修改数据,所以它是安全的。
- 例一:求两个整型的和
DELIMITER $$ CREATE FUNCTION `test`.`add1`(m INT, n INT) RETURNS INT NO SQL -- 声明函数没有SQL BEGIN RETURN (m+n); END$$ DELIMITER ;
- 例一:求test库中有几个存储过程和函数
DELIMITER $$ CREATE FUNCTION `test`.`func1`() RETURNS INT READS SQL DATA --声明函数只读数据。 BEGIN DECLARE routine_cnt INT DEFAULT 0; SELECT COUNT(routine_name) INTO routine_cnt FROM `information_schema`.`routines` WHERE routine_schema='test'; RETURN routine_cnt; END$$ DELIMITER ;