· 自定义函数: 用户自定义函数(user-defined function, UDF)是一种对MySQL扩展的途径,其用法与内置函数相同。
· 自定义函数的两个必要条件:
- 参数
- 返回值
·函数可以返回任意类型的值,同样可以接收这些类型的参数
- 参数理论上不能超过1024个
·创建自定义函数
CREATE FUNCTION function_name RETURNS{STRING | INTEGER|REAL|DECIMAL} routine_body
·删除函数
DROP FUNCTION [IF EXISTS] function_name
关于函数体
- 函数体由合法的SQL语句构成;
- 函数体可以是简单的SELECT或INSERT语句;
- 函数体如果为复合结构则使用BEGIN…END语句;
- 复合结构可以包含声明,循环,控制结构;
不带有参数的函数
mysql> CREATE FUNCTION f1() RETURNS VARCHAR(30)
-> RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT f1();
+--------------------------------------+
| f1() |
+--------------------------------------+
| 2016年10月12日 11点:02分:32秒 |
+--------------------------------------+
带参数的函数
mysql> CREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)
-> RETURNS FLOAT(10,2) UNSIGNED
-> RETURN (num1+num2)/2;
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT f2(200,100);
+-------------+
| f2(200,100) |
+-------------+
| 150.00 |
+-------------+
1 row in set (0.00 sec)
修改MySQL默认的‘;’分隔符
DELIMITER // 把MySQL语句分隔符从‘;’改为‘//’
mysql> SELECT *FROM test2;
+----+----------+
| id | username |
+----+----------+
| 1 | Tom |
| 2 | Json |
| 3 | Hek |
+----+----------+
3 rows in set (0.02 sec)
mysql> CREATE FUNCTION adduser(username VARCHAR(20))
-> RETURNS INT UNSIGNED
-> RETURN test2(username) VALUES(username);
//这里分号是MySQL语句结束,所以根本不存在返回值。
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 'VALUES(username)' at line 3
mysql> DELIMITER //
mysql> SELECT VERSION();
-> //
+-----------+
| VERSION() |
+-----------+
| 5.7.14 |
+-----------+
1 row in set (0.02 sec)
复合结构
mysql> CREATE FUNCTION adduser(username VARCHAR(20))
-> RETURNS INT UNSIGNED
-> RETURN test2(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 'VALUES(username);
LAST_INSERT_ID()' at line 3
//因为有两条语句所以 要用BEGIN...END
mysql> CREATE FUNCTION adduser(username VARCHAR(20))
-> RETURNS INT UNSIGNED
-> BEGIN
-> INSERT test1(username) VALUES(username);
-> RETURN LAST_INSERT_ID();
-> END
-> //
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE FUNCTION addusers(username VARCHAR(20))
-> RETURNS INT UNSIGNED
-> BEGIN
-> INSERT test2(username) VALUES(username);
-> RETURN LAST_INSERT_ID();
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT addusers('dingdo')
-> //
+--------------------+
| addusers('dingdo') |
+--------------------+
| 4 |
+--------------------+
1 row in set (0.05 sec)