函数相关参数 log_bin_trust_function_creators

最新推荐文章于 2024-12-23 10:51:36 发布

kf_panda

最新推荐文章于 2024-12-23 10:51:36 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： MYSQL基础

本文链接：https://blog.youkuaiyun.com/gua___gua/article/details/48314315

MYSQL基础专栏收录该内容

61 篇文章

订阅专栏

本文详细解析了MySQL中`log_bin_trust_function_creators`变量的作用、配置及其对存储过程创建的安全影响，包括其默认值、允许值、变量范围、动态特性等。同时，文章还提供了解决常见错误的实用技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Command-Line Format    --log-bin-trust-function-creators
Option-File Format    log-bin-trust-function-creators
Option Sets Variable    Yes, log_bin_trust_function_creators
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 18.7, “Binary Logging of Stored Programs”.

主要是考虑binlog安全，如果没开binlog的话这个值是无意义的；打开binlog后需要超级权限才能定义，并且在sql上也有需求

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)
这个报错目前知道可以通过两个方式解决：
RETURNS    double 【READS SQL DATA/DETERMINISTIC】
即在自定义函数后增加一个只读数据或者指定字符的信息