MySQL 出现错误1418 的原因分析及解决方法

错误描述

在使用 MySQL 进行数据操作时，有可能会出现以下错误提示：

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中的一个常见错误，它的主要问题在于MySQL的二进制日志系统和函数的使用有冲突，导致该错误的出现。本文将详细讲解MySQL错误1418的原因分析及解决方法。

原因分析

该错误通常出现在创建自定义函数时，如果函数使用包含非DETERMINISTIC, NO SQL 或 READS SQL DATA 的操作，则会触发该错误。当MySQL的二进制日志系统启用并且函数使用了带有这些选项的操作时，该错误会出现。

以下是MySQL官方文档对于这些选项的定义：

• DETERMINISTIC 表示函数的返回结果只依赖于其输入参数，而不依赖于任何其他因素。例如，sqrt()函数依赖于传递给它的参数，因此是确定性的；而now()函数不是，因为它的返回结果依赖于当前的时间。

• NO SQL 表示函数不会与数据库中的任何数据进行交互，因此不需要读取或修改数据表。例如，像pi()这样的函数常常使用此选项。

• READS SQL DATA 表示函数在运行期间会读取数据表。例如，像avg()这样的函数必须从数据表中读取数据才能计算平均值。

解决方法

解决方法是为函数添加 DEFINER 和 SQL SECURITY 子句，在函数定义中明确指明它们不具有确定性，或者必须访问数据表，也就是不带 DETERMINISTIC 或 READS SQL DATA 子句是不会出现1418错误的。

首先，可以使用以下命令查询MySQL服务器的当前状态：

SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

如果将其设置为 ON，则可以绕过 MySQL 日志系统中的该错误，如果需要修改该变量，则可以使用以下命令：

SET GLOBAL log_bin_trust_function_creators = 1;

但是这种方法并不推荐使用，因为它会降低 MySQL 的安全性。

推荐的方法是在 MySQL 客户端中执行以下操作：

1. 确保当前的 MySQL 用户具有创建函数的权限。

2. 在定义函数时使用以下语句：

CREATE FUNCTION <function_name>([func_params]) RETURNS <return_type> [DETERMINISTIC, NO SQL, READS SQL DATA] SQL SECURITY INVOKER
BEGIN
    [function_body]
END

这里需要注意的是，DETERMINISTIC, NO SQL, READS SQL DATA 子句应根据函数的实际情况进行选择，而不应该随意添加。

示例

以下是两个示例，分别为使用带有 DETERMINISTIC 子句的 CREATE FUNCTION 命令创建函数和使用不带 DETERMINISTIC 子句的命令创建函数：

示例一

CREATE FUNCTION `add_numbers`(a INT, b INT) RETURNS INT DETERMINISTIC
BEGIN
  DECLARE sum INT;
  SET sum = a + b;
  RETURN sum;
END;

该示例中，DETERMINISTIC 子句被用于表明该函数的结果是确定性的。

示例二

CREATE FUNCTION `get_customer_count`() RETURNS INT
BEGIN
  DECLARE count INT;
  SELECT COUNT(*) INTO count FROM customers;
  RETURN count;
END;

该示例中，未使用 DETERMINISTIC 或 NO SQL 或 READS SQL DATA 子句，因为该函数需要访问名为 customers 的数据表。

结论

在MySQL数据操作时，当出现错误1418时，很可能是创建自定义函数所导致的，通过添加 DEFIENER 和 SQL SECURITY 子句，可以解决该问题，从根本上保证MySQL服务器的安全性和正常运行。