第七章 自定义函数

Posted on | In | | Visitors:

上一章学习了一些内置函数库，对于日常操作足够啦，但也可能需要到实现某一特定功能的函数，这个函数系统没有提供给我们，这时候就需要自定义函数。

1. 自定义函数简介

定义：

用户自定义函数（UDF）是一种对MySQL扩展的途径，其用法与内置函数相同。

自定义函数的两个必要条件：

  1. 参数
  2. 返回值

自定义函数

  • 函数可以返回任意类型的值，同样可以接收这些类型的参数

创建自定义函数

CREATE FUNCTION function_name

RETURNS

{STRING|INTEGER|REAL|DECIMAL}

routine_body

关于函数体

  1. 函数体由合法的SQL语句构成
  2. 函数体可以是简单的SELECT或INSERT语句
  3. 函数体如果为符合结构则使用BEGIN…END语句
  4. 复合结构可以包含声明，循环，控制结构；

2. 创建不带参数的自定义函数

首先打开mysql，使用test数据库。本小节以创建一个显示“2020年1月7日12:26:11”时间的函数为例。

看一下系统自带的函数NOW()：

1
2
3
4
5
6
7
mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2020-01-07 12:25:16 |
+---------------------+
1 row in set (0.00 sec)

注意：下面要想显示汉字，别忘记设置set names gbk;

使用DATE_FORMAT实现：

1
2
3
4
5
6
7
mysql> SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H:%I:%S');
+--------------------------------------------+
| DATE_FORMAT(NOW(),'%Y年%m月%d日 %H:%I:%S')       |
+--------------------------------------------+
| 2020年01月07日 12:12:13                          |
+--------------------------------------------+
1 row in set (0.00 sec)

注意：命令月和日前使用的是小写的m、d，这里如果改成大写显示就会不同。也就是说这里的大小写务必一一对应，不然会有不同的显示。
下面将其封装成函数：

1
2
3
mysql> CREATE FUNCTION y1() RETURNS VARCHAR(30)
    -> RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H:%I:%S');
Query OK, 0 rows affected (0.00 sec)

调用一下：

1
2
3
4
5
6
7
mysql> SELECT y1();
+-------------------------+
| y1()                    |
+-------------------------+
| 2020年01月07日 12:12:36       |
+-------------------------+
1 row in set (0.00 sec)

如果想要删除一个函数：

1
2
mysql> DROP FUNCTION f1;
Query OK, 0 rows affected (0.00 sec)

3. 创建带参数的自定义函数

下面创建一个带有参数的求两个参数平均值的函数：

1
2
3
4
mysql> CREATE FUNCTION y2(num1 SMALLINT UNSIGNED , num2 SMALLINT UNSIGNED)
 -> RETURNS FLOAT(10) UNSIGNED
 -> RETURN (num1+num2)/2;
Query OK, 0 rows affected (0.00 sec)

如果不传递参数直接调用：

1
2
mysql> SELECT y2();
ERROR 1318 (42000): Incorrect number of arguments for FUNCTION test.y2; expected 2, got 0

出现错误，必须传递参数：

1
2
3
4
5
6
7
mysql> SELECT y2(3,4);
+---------+
| y2(3,4) |
+---------+
|     3.5 |
+---------+
1 row in set (0.00 sec)

4. 创建具有复合结构函数体的自定义函数体

首先看一下test数据表

1
2
3
4
5
6
7
8
mysql> DESC test;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | YES  |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
2 rows in set (0.05 sec)

一共有两个字段，查看一下记录：

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM test;
+----+----------+
| id | username |
+----+----------+
|  1 | hao      |
|  2 | yuan     |
|  3 | ye       |
+----+----------+
3 rows in set (0.00 sec)

一共有三条记录，下面创建一个函数，可以插入记录，并且可以返回id。

1
2
3
4
5
mysql> CREATE FUNCTION adduser(username VARCHAR(20))
    -> RETURNS INT UNSIGNED
    -> RETURN
    -> INSERT test(username) VALUES(username);
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 'test(username) VALUES(username)' at line 4

此时系统提示错误，原因是分号意味着语句的结束，而我们的返回值类型是整数，此时还没有返回整数值，只是执行了插入记录的命令，这时可以先修改mysql默认的分隔符。

1
mysql> DELIMITER //

以后所有的命令都通过//来结束。
举个例子：

1
2
3
4
5
6
7
8
9
mysql> SELECT VERSION();
    ->
    -> //
+-----------+
| VERSION() |
+-----------+
| 5.5.62    |
+-----------+
1 row in set (0.00 sec)

只有//结尾时，才运行命令。
下面重新创建函数：

1
2
3
4
5
6
7
8
mysql> CREATE FUNCTION adduser(username VARCHAR(20))
    -> RETURNS INT UNSIGNED
    -> RETURN
    -> INSERT test(username) VALUES(username);
    -> LAST_INSERT_ID();
    -> //
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 'test(username) VALUES(username);
LAST_INSERT_ID()' at line 4

还是错误，因为这里有两个语句需要执行，所以需要添加BEGIN END 语句。

1
2
3
4
5
6
7
8
mysql> CREATE FUNCTION adduser(username VARCHAR(20))
    -> RETURNS INT UNSIGNED
    -> BEGIN
    -> INSERT test(username) VALUES(username);
    -> RETURN LAST_INSERT_ID();
    -> END
    -> //
Query OK, 0 rows affected (0.04 sec)

验证一下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> SELECT adduser('xiaohua');
    -> //
+--------------------+
| adduser('xiaohua') |
+--------------------+
|                  4 |
+--------------------+
1 row in set (0.03 sec)

mysql> SELECT * FROM test//
+----+----------+
| id | username |
+----+----------+
|  1 | hao      |
|  2 | yuan     |
|  3 | ye       |
|  4 | xiaohua  |
+----+----------+
4 rows in set (0.00 sec)

可以看出创建成功，然后把分隔符变为“；”即可。

小结

自定义函数：简称UDF，是对MySQL扩展的一种途径

创建自定义函数：CREATE FUNCTION

自定义函数两个必要条件：

​ 参数：可以有零个或者duoge

​ 返回值：只能有一个返回值

函数体内可以书写多个sql语句，这种称为复合结构函数，需要使用BEGIN…END来包含。