mysql创建函数时提示1418。可选关闭二进制日志或者设置log_bin_trust_function_creators=1

报错详情如下：1418--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)

 

出现过程

数据库版本原为5.7.12，新版本为8.0.28，导入函数的时候报错。

查阅相关资料，意思是说binlog启用时，创建的函数没有声明类型，因为binlog在主从复制需要知道这个函数创建语句是什么类型，否则同步数据会有不一致现象。

mysql开启了bin-log, 我们就必须指定我们的函数是否是哪种类型： 
1 DETERMINISTIC 不确定的 
2 NO SQL 没有SQl语句，当然也不会修改数据 
3 READS SQL DATA 只是读取数据，当然也不会修改数据 
4 MODIFIES SQL DATA 要修改数据 
5 CONTAINS SQL 包含了SQL语句

此处报错是因为mysql8.0默认bin_log为开启状态，所以创建函数时会需要判断权限。

解决方案：

1、关闭二进制日志，经查询，二进制日志为非必要开启，所以选择该方案。

5.7版本默认为关闭的，而8.0默认为开启，需要手动关闭。

• 查询当前状态：show variables like 'log_bin';
• 
• 编辑my.cnf文件，一般路径为：/etc/my.cnf，在mysqld下面添加：skip-log-bin
• 重启Mysql，/etc/init.d/mysqld restart;
• 再次查看状态，为关闭状态：show variables like 'log_bin';

2、如存在主从热备，二进制日志为确实需要开启，可以修改函数，声明对应的函数类型

创建子程序(存储过程、函数、触发器)时，声明为DETERMINISTIC或NO SQL与READS SQL DATA中的一个，
例如:

DROP FUNCTION IF EXISTS `t_auto_increment`;
DELIMITER ;;
CREATE FUNCTION `t_auto_increment`(`seq_name` tinyint) RETURNS varchar(50) CHARSET utf8
DETERMINISTIC
BEGIN
declare v_prefix VARCHAR(20);
update t_auto_increment a set a.currentvalue=LAST_INSERT_ID(a.currentvalue+a.incrementBy) where a.autoKey=seq_name;
select prefix into v_prefix from t_auto_increment where autoKey=seq_name;
RETURN CONCAT(CASE WHEN v_prefix  is null THEN '' ELSE  v_prefix END,LAST_INSERT_ID()); 
END
;;
DELIMITER ;

3、如存在主从热备，二进制日志为确实需要开启，函数不会有大的变动的情况下，可以选择信任子程序的创建者，禁止创建、修改子程序时对SUPER权限的要求。初始创建时可能会需要手动创建从机函数（未验证）。

设置log_bin_trust_routine_creators全局系统变量为1。设置方法有三种:

• 1.在客户端上执行SET GLOBAL log_bin_trust_function_creators = 1;
• 2.MySQL启动时，加上--log-bin-trust-function-creators选项，参数设置为1
• 3.在MySQL配置文件my.ini或my.cnf中的[mysqld]段上加log-bin-trust-function-creators=1

 

参考文章：https://blog.csdn.net/itwxming/article/details/90439327

参考文章：https://blog.csdn.net/weixin_42528534/article/details/116102242