目录
若mysql自带的函数不符合自己的要求,可以自定义函数
自定义函数
1.1 创建
1.1.1 语法
命令行:
delimiter $$
create function 函数名称(参数列表) returns 返回类型
begin
sql语句
end
$$
delimiter;
Navicat:
1.1.2 delimiter说明
- delimiter用于命令行里面,navicat不需要使用。
- delimiter用于设置分割符,默认为分号
- 在 "sql语句" 部分编写的语句需要以分号结尾,此时回车会直接执行,所以要创建存储过程,需要指定其它符号作为分割符,此处使用 $$,也可以使用其它字符。
解释:
①、在mysql语句中,都是以分号来判断是否是一条或多条语句,如下语句:
mysql> select * from students
->
-> ;
+-----------+----------+------+----------+------+-------+--------------------+
| studentNo | nane | sex | hometown | age | class | card |
+-----------+----------+------+----------+------+-------+--------------------+
| 001 | 王昭君 | 女 | 北京 | 20 | 1班 | 340322199001247654 |
| 002 | 诸易亮 | 男 | 上海 | 18 | 2班 | 340322199002242354 |
| 003 | 张飞 | 男 | 南京 | 24 | 3班 | 340322199003247654 |
| 004 | 白起 | 男 | 安徽 | 22 | 4班 | 340322199005247654 |
| 005 | 大乔 | 女 | 天津 | 19 | 3班 | 340322199004247654 |
| 006 | 孙测香 | 女 | 河北 | 18 | 1班 | 340322199006247654 |
| 007 | 百里玄闟 | 男 | 山西 | 20 | 2班 | 340322199087247654 |
| 008 | 小乔 | 女 | 河南 | 15 | 3班 | NULL |
| 009 | 百里守约 | 男 | 湖南 | 21 | 1班 | |
| 010 | 妲己 | 女 | 广东 | 26 | 2班 | 340322199607247654 |
| 011 | 李白 | 男 | 北京 | 30 | 4班 | 340322199005267754 |
| 012 | 孙膑 | 男 | 新疆 | 26 | 3班 | 340322199000297655 |
+-----------+----------+------+----------+------+-------+--------------------+
12 rows in set (0.00 sec)
②、使用delimiter后, 用$$代替分号,使分号不能作为结束语句。(只在命令行中使用)
mysql> delimiter $$
mysql> select * from students;
->
-> ;;
->
-> $$
+-----------+----------+------+----------+------+-------+--------------------+
| studentNo | nane | sex | hometown | age | class | card |
+-----------+----------+------+----------+------+-------+--------------------+
| 001 | 王昭君 | 女 | 北京 | 20 | 1班 | 340322199001247654 |
| 002 | 诸易亮 | 男 | 上海 | 18 | 2班 | 340322199002242354 |
| 003 | 张飞 | 男 | 南京 | 24 | 3班 | 340322199003247654 |
| 004 | 白起 | 男 | 安徽 | 22 | 4班 | 340322199005247654 |
| 005 | 大乔 | 女 | 天津 | 19 | 3班 | 340322199004247654 |
| 006 | 孙测香 | 女 | 河北 | 18 | 1班 | 340322199006247654 |
| 007 | 百里玄闟 | 男 | 山西 | 20 | 2班 | 340322199087247654 |
| 008 | 小乔 | 女 | 河南 | 15 | 3班 | NULL |
| 009 | 百里守约 | 男 | 湖南 | 21 | 1班 | |
| 010 | 妲己 | 女 | 广东 | 26 | 2班 | 340322199607247654 |
| 011 | 李白 | 男 | 北京 | 30 | 4班 | 340322199005267754 |
| 012 | 孙膑 | 男 | 新疆 | 26 | 3班 | 340322199000297655 |
+-----------+----------+------+----------+------+-------+--------------------+
12 rows in set (0.00 sec)
③、 自定义完函数需要改为分号结尾,否则后续其他语句无法以分号结尾
mysql> delimiter ;
mysql> select * from students;
+-----------+----------+------+----------+------+-------+--------------------+
| studentNo | nane | sex | hometown | age | class | card |
+-----------+----------+------+----------+------+-------+--------------------+
| 001 | 王昭君 | 女 | 北京 | 20 | 1班 | 340322199001247654 |
| 002 | 诸易亮 | 男 | 上海 | 18 | 2班 | 340322199002242354 |
| 003 | 张飞 | 男 | 南京 | 24 | 3班 | 340322199003247654 |
| 004 | 白起 | 男 | 安徽 | 22 | 4班 | 340322199005247654 |
| 005 | 大乔 | 女 | 天津 | 19 | 3班 | 340322199004247654 |
| 006 | 孙测香 | 女 | 河北 | 18 | 1班 | 340322199006247654 |
| 007 | 百里玄闟 | 男 | 山西 | 20 | 2班 | 340322199087247654 |
| 008 | 小乔 | 女 | 河南 | 15 | 3班 | NULL |
| 009 | 百里守约 | 男 | 湖南 | 21 | 1班 | |
| 010 | 妲己 | 女 | 广东 | 26 | 2班 | 340322199607247654 |
| 011 | 李白 | 男 | 北京 | 30 | 4班 | 340322199005267754 |
| 012 | 孙膑 | 男 | 新疆 | 26 | 3班 | 340322199000297655 |
+-----------+----------+------+----------+------+-------+--------------------+
12 rows in set (0.00 sec)
1.1.3 范例:
要求:创建函数my_trim,用于删除字符串左右两侧的空格
命令行:
- step1:设置分割符
delimiter $$
- step2:创建函数
create function my_trim(str varchar(100)) retures varchar(108)
begin
return ltrim(rtrim(str));
end
$$
- step3:还原分割符
delimiter ;
Navicat
- step1:创建:
set global log_bin_trust_function_creators=TRUE;
create function my_trim(str varchar(100)) returns varchar(100)
begin
return ltrim(rtrim(str));
end
‼注意:创建时的报错问题:
不能直接自定义,
报错:
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)
需要添加:
set global log_bin_trust_function_creators=TRUE;
参考:This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de 错误解决办法_永不放弃的地盘-CSDN博客
- step2:使用:
select my_trim(' ABC ')