16 MySQL 自定义函数:创建、调用和删除

一、自定义函数:SQL创建语法

create function funcName([funcParameter])
returns type
[characteristics...] routine_body

① funcName 函数名
② funcParameter 可选多个参数
③ returns type 返回type数据类型
④ characteristics 指定存储函数的特性，取值举例：
sql security {definer | invoker}:指明谁有执行权限;
invoker 拥有权限的调用者; definer 定义者(默认值);
comment ‘string’ 注释信息，可以用来描述存储函数
⑤ routine_body 函数体:
函数体由SQL代码构成,如,简单SQL查询语句;或复合结构SQL语句;
函数体若是复合结构(多行代码)时,必须使用 begin...end 语句;
复合结构可以包含声明、流程控制; 需结合使用 delimiter 转换(;)结束标识符

示例A：从图书信息表[bookinfo]查询图书发布时间[pubadate]:

select date_format(pubadate,'%Y-%m') from bookinfo;

1、封装为 ym_date() 函数:

create function ym_date(mydate date)
returns varchar(15)
return date_format(pubadate,'%Y-%m');

2、复合结构: 封装为 ym_date2() 函数:必须加入begin...end

delimiter //
	create function ym_date2(mydate date)
	returns varchar(15)
	begin
		return date_format(pubadate,'%Y-%m');
	end //
delimiter ;

示例B：创建 newdate() 无参函数
返回系统日期,格式: xxxx年xx月xx日

create function newdate()
returns varchar(20)
return date_format(curdate(),'%Y年%m月%d日');

示例C：创建 show_name_store(bid int)有参函数:
根据图书ID 返回书名/库存信息,字符串格式’书名----库存’

create function show_name_store(bid int)
returns varchar(50)
return (select concat_ws('----',book_name,store) from bookinfo where book_id=bid);

二、自定义函数:调用

select ym_date(pubadate) from bookinfo;	/*调用函数 ym_date */
select ym_date2(pubadate) from bookinfo;/*调用函数 ym_date2 */
select newdate();						/*调用 newdate 无参函数 */
select show_name_store(20150201);		/*调用 show_name_store 有参函数 */

三、自定义函数:SQL删除语法

drop function [if exists] funcName;

① funcName 函数名
② 预删函数不存在时, 有if exists只会警告; 无if exists会报错;
示例: 删除 newdate 函数

drop function newdate;
drop function if exists newdate; /* 当newdate不存在时,有 if exists 不会报错 */

注意：创建自定义函数报错问题：

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> set global log_bin_trust_function_creators=TRUE;
Query OK, 0 rows affected (0.00 sec)

然后就可以正常 创建自定义函数