MySQL自定义函数

发表于

自定义函数

用户自定义函数(user-defined function, UDF)是一种对MySQL扩展的途径, 其用法与内置函数相同。

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

  1. 参数：最多1024个任意类型的参数
  2. 返回值：任意类型

函数可以返回任意类型的值，同样可以接受这些类型的参数；
函数的参数与返回值之间，没有必然的联系。

创建自定义函数

1
2
3
4
CREATE FUNCTION function_name
RETURNS
{STRING|INTEGER|REAL|DECIMAL}
routine_body

routine_body是函数体

关于函数体

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

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

创建日期时间自定义函数

1
2
3
4
5
CREATE FUNCTION f1() 
RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');
SELECT f1();

创建带有参数的自定义函数

创建带参数的自定义函数：

1
2
3
4
5
6
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED
RETURN (num1+num2)/2;
SELECT f2();        //报错
SELECT f2(10,15);   //结果：12.5

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

修改分隔符：DELEMITER 分隔符

1
2
DELIMITER //  
//将分隔符修改为 '//'

当函数体内执行多条语句时，使用BEGIN…END语句；
当编写函数体内容的时候，使用 DELIMITER 关键字将分隔符;修改为别的，否则写到 ‘;’ 时会执行，导致函数编写失败。

1
2
3
4
5
6
7
8
9
10
DELIMITER //
CREATE FUNCTION ADD_USER(p_id SMALLINT,username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT user(p_id,username) VALUES(p_id,username);
RETURN LAST_INSERT_ID();
END
//
DELIMITER ;

删除函数

1
DROP FUNCTION [IF EXISTS] function_name;