1、前置条件
MySQL数据库中存在表user_info,其结构和数据如下:
mysql> desc user_info;
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| id | int(10) | NO | PRI | NULL | |
| name | char(20) | NO | | NULL | |
| passwd | char(40) | NO | | NULL | |
| email | char(20) | NO | | NULL | |
| phone | char(20) | NO | | NULL | |
| role | char(10) | NO | | NULL | |
| sex | char(10) | NO | | NULL | |
| status | int(10) | NO | | NULL | |
| createAt | datetime | NO | | NULL | |
| exprAt | datetime | NO | | NULL | |
| validDays | int(10) | NO | | NULL | |
| delAt | datetime | YES | | NULL | |
+-----------+----------+------+-----+---------+-------+
12 rows in set (0.10 sec)
mysql> select * from user_info;
+----+--------------+----------+------------+-------------+--------+------+--------+---------------------+---------------------+-----------+-------+
| id | name | passwd | email | phone | role | sex | status | createAt | exprAt | validDays | delAt |
+----+--------------+----------+------------+-------------+--------+------+--------+---------------------+---------------------+-----------+-------+
| 1 | StephenWang7 | py123456 | 123@qq.com | 15103887470 | admin | male | 200 | 2019-04-12 20:11:30 | 2019-04-19 20:11:30 | 30 | NULL |
| 2 | StephenWang8 | 123456 | 123@qq.com | 15103887470 | viewer | male | 200 | 2019-04-12 20:11:30 | 2019-04-19 20:11:30 | 30 | NULL |
+----+--------------+----------+------------+-------------+--------+------+--------+---------------------+---------------------+-----------+-------+
2 rows in set (0.00 sec)
2、自定义函数
函数:可以完成特定功能的一段SQL集合。MySQL支持自定义函数来完成特定的业务功能。
创建自定义函数(User Defined Function 简称UDF)的语法如下:
create function <函数名称> ([参数1] [类型1], [参数N] [类型N])
returns <类型>
return
<函数主体>
调用UDF的语法如下:
select <函数名称> ([参数])
创建无参的UDF
示例1:查询user_info表中有多少条记录
#定义函数
mysql> create function user_info_count()
-> returns int(10)
-> return
-> (select count(*) from user_info);
调用函数user_info_count()
mysql> select user_info_count();
+-------------------+
| user_info_count() |
+-------------------+
| 2 |
+-------------------+
1 row in set (0.00 sec)
创建有参UDF
示例2:根据id查询用户name。
#定义函数
mysql> create function queryNameById(uid int(10))
-> returns char(20)
-> return
-> (select name from user_info where id=uid);
Query OK, 0 rows affected (0.01 sec)
调用函数,查询id为1的用户名称。
mysql> select queryNameById(1);
+------------------+
| queryNameById(1) |
+------------------+
| StephenWang7 |
+------------------+
1 row in set (0.00 sec)
查看UDF
查询系统中所有的UDF
show function status;
查询指定的UDF
#
show create function 函数名称;
mysql> show function queryNameById;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'queryNameById' at line 1
mysql> show function queryNameById();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'queryNameById()' at line 1
mysql> show create function queryNameById();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '()' at line 1
mysql> show create function queryNameById;
+---------------+-------------