MySQL8 创建函数报错：This function has none of DETERMINISTIC

MySQL8 创建函数报错：This function has none of DETERMINISTIC

MySQL8 创建函数报错：

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的函数定义：

CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION functionName ( varName varType [, … ] )
RETURNS returnVarType
[characteristic …]
routine_body

• functionName：函数名，同MySQL内置函数一样，大小写不敏感
• varName: 形参名
• varType: 形参类型，其与varName配对使用。形参数量不限
• returnVarType: 返回值类型。函数必须有且只能有一个返回值
• routine_body：函数体。函数体中必须含有 return 语句，当函数体为复合结构时，需要使用begin … end 语句
• characteristic：函数特性定义值：{ not deterministic | deterministic |contains sql | no sql | reads sql data | modifies sql data }

函数特性 ：
自MySQL 5.0.0之后，DETERMINISTIC默认值为NOT DETERMINISTIC ，指明函数体使用sql语句的限制。
DETERMINISTIC – 表示函数或过程是纯函数或过程，即它的输出完全由输入参数确定。
contains sql 函数体包含sql语句，但不包含读写数据的sql语句；
no sql 函数体不包含sql语句；
reads sql data 函数体包含读数据sql语句；
modifies sql data 函数体包含写数据的sql语句。

错误提示和二进制日志相关，原因是：

（1）二进制日志记录是MySQL用于记录数据更改的机制。当启用二进制日志记录时，MySQL会记录每个数据更改并将其存储在二进制日志文件中。这些日志文件可以在备份和复制MySQL数据库时使用。

（2）MySQL需要确定函数或过程的影响，以正确记录其输出结果。如果函数或过程是确定的，则其输出结果始终相同，可以通过记录输入参数而不是结果减轻日志记录的负担。

处理方式：

（1）使用参数 DETERMINISTIC

mysql> DELIMITER $$
mysql> CREATE function fun_test( ) returns int 
    -> BEGIN
    ->    declare i int;
    ->  set i = 0;
    ->    select count(*) into i from chcw ;
    ->    return i;
Display all 1122 possibilities? (y or n) 
    ->    return i;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)
mysql> 
1
2
3
4
5
6
7
8
9
10
11

使用参数DETERMINISTIC

mysql> DELIMITER $$
mysql> CREATE function fun_test( ) returns int DETERMINISTIC
    -> BEGIN
    ->    declare i int;
    ->    select count(*) into i from chcw ;
    ->    return i;
    ->    return i;end $$
Query OK, 0 rows affected (0.00 sec)

1
2
3
4
5
6
7
8
9

由于默认参数是：NO DETERMINISTIC
修改参数，DETERMINISTIC ，创建函数成功。

DELIMITER $$
CREATE function fun_test( )  returns int DETERMINISTIC 
BEGIN
   declare i int;
   select count(*) into i from chcw ;
   return i;		
end $$
1
2
3
4
5
6
7

（2）调整参数 log_bin_trust_function_creators

log_bin_trust_function_creators，8.0版本默认是off

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)

1
2
3
4
5
6
7
8

打开开关参数：

set global log_bin_trust_function_creators = 1;

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

1
2
3

再次创建函数，不使用 DETERMINISTIC 参数

mysql> DELIMITER $$
mysql> CREATE function fun_test_noDETERMINISTIC ( )  returns int 
    -> BEGIN
    ->    declare i int;
    ->    select count(*) into i from chcw ;
    ->    return i;
    ->    return i;end $$
Query OK, 0 rows affected (0.01 sec)
1
2
3
4
5
6
7
8

使用默认参数 NO DETERMINISTIC 创建函数成功。