理解MySQL存储过程和函数

当前第2页 返回上一页

报错示例

Error Code: 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)
这个报错就是上面注意部分说的问题。原来是因为在主从复制的两台MySQL服务器中开启了二进制日志选项log-bin,slave会从master复制数据，而一些操作，比如function所得的结果在master和slave上可能不同，所以存在潜在的安全隐患。因此，在默认情况下回阻止function的创建。

解决办法有两种：

1.将log_bin_trust_function_creators参数设置为ON，这样一来开启了log-bin的MySQL Server便可以随意创建function。这里存在潜在的数据安全问题，除非明确的知道创建的function在master和slave上的行为完全一致。
  设置该参数可以用动态的方式或者指定该参数来启动数据库服务器或者修改配置文件后重启服务器。需注意的是，动态设置的方式会在服务器重启后失效。

mysql> show variables like 'log_bin_trust_function_creators';
 mysql> set global log_bin_trust_function_creators=1;

  另外如果是在master上创建函数，想通过主从复制的方式将函数复制到slave上则也需在开启了log-bin的slave中设置上述变量的值为ON（变量的设置不会从master复制到slave上，这点需要注意），否则主从复制会报错。

2.明确指明函数的类型
  1 ）、DETERMINISTIC 不确定的
  2 ）、NO SQL 没有SQl语句，当然也不会修改数据
  3 ）、READS SQL DATA 只是读取数据，当然也不会修改数据
比如：CREATE DEFINER=`username`@`%` READS SQL DATA FUNCTION `fn_getitemclock`(i_itemid bigint,i_clock int,i_pos int) RETURNS int(11)...
这样一来相当于明确的告知MySQL服务器这个函数不会修改数据，因此可以在开启了log-bin的服务器上安全的创建并被复制到开启了log-bin的slave上。

修改存储过程函数语法

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
 
characteristic:
  { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
 | SQL SECURITY { DEFINER | INVOKER }
 | COMMENT 'string'

删除存储过程函数语法

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

查看存储过程和函数

1.查看存储过程状态

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
show procedure status like 'Pro_Employee' \G

2.查看存储过程和函数的创建语法

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

SHOW CREATE PROCEDURE Pro_Employee \G;

3.查看存储过程和函数详细信息

代码如下:

SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME='Pro_Employee' \G;

总结 

 存储过程和函数语法不难理解，但是往往存储过程中不单单只包含这种简单的查询语法，还会嵌套循环语句、变量、报错处理、事务等，下一篇文章会单独讲变量，将变量的知识加入到存储过程，包括变量的声明和报错处理，欢迎关注。

更多相关Mysql内容来自木庄网络博客

标签：Mysql

返回前面的内容

相关阅读 >>

mysql服务怎么自动启动不了？

详解mysql中order by多字段排序规则

关系数据库管理系统中所谓的关系指的是什么？

python操作mysql数据库的三种方法总结

怎么检查mysql是否安装成功

聊聊mysql 中常用的日期相关函数

【mysql】information_schema库中包含所有表的字段信息

mysql数据库时间默认设置方法

mysql如何设置远程连接数据库？

mysql子查询简单介绍

更多相关阅读请进入《mysql》频道 >>

书籍

数据库系统概念 第6版

￥41.1元   机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。

转载请注明出处：木庄网络博客 » 理解MySQL存储过程和函数