MySQL：自定义函数


#MySQL 笔记

本文给出一些示例，可以用于快速入门 MySQL 函数。

注意，下面的创建函数的sql，在mycli下运行会报语法错误，这是mycli的设计问题，它还不支持 delimiter 指令，具体讨论见 https://github.com/dbcli/mycli/issues/383 。所以请用其他客户端。

示例1

创建函数：

drop function if exists hello;

delimiter //

CREATE FUNCTION hello() RETURNS FLOAT(3,2) 
begin
RETURN 1.1;
end //

delimiter ;

测试函数：

mysql> select hello();
+---------+
| hello() |
+---------+
|    1.10 |
+---------+

为什么要delimiter // ？ 默认的 delimiter 是分号;，也就是遇到分号后，mysql会将前面内容看做一个单独sql执行。而函数中会有分号，为了防止函数的部分内容被执行，所以更改 delimiter。 delimiter 也可以是其他值，例如示例2中的$$。

示例2

创建函数：

drop function if exists hello;

delimiter $$

CREATE FUNCTION hello() RETURNS FLOAT(3,2) 
begin
RETURN 1.1;
end $$

delimiter ;

测试函数：

mysql> select hello();
+---------+
| hello() |
+---------+
|    1.10 |
+---------+

示例3

创建函数：

drop function if exists my_add; -- 函数名不能和内置关键词、函数名冲突
delimiter //

create function my_add(num1 int ,num2 int) returns int
begin
return num1+num2;
end //

delimiter ;

测试函数：

mysql> select my_add(1,2);
+-------------+
| my_add(1,2) |
+-------------+
|           3 |
+-------------+

mysql> select my_add(1,2) as result;
+--------+
| result |
+--------+
|      3 |
+--------+

示例4

创建函数：

drop function if exists hello;
delimiter //

create function hello() returns char(20)
begin
return 'hello';
end //

delimiter ;

测试函数：

mysql> select hello();
+---------+
| hello() |
+---------+
| hello   |
+---------+

示例5

创建函数：

drop function if exists hello;
delimiter //

create function hello(msg char(10)) returns char(20)
begin
return concat('Hello ', msg);
end //

delimiter ;

测试函数：

mysql> select hello('world') as result;
+-------------+
| result      |
+-------------+
| Hello world |
+-------------+

示例6：if的使用

创建函数：

drop function if exists human_status;
delimiter //

create function human_status(p_status int) returns char(20)  -- 参数名不要同status，这是关键词
begin
    if p_status=0 then 
        return '未完成';
    elseif p_status=1 then 
        return '已完成';
    elseif p_status=2 then 
        return '取消';
    else 
        return '未知';
    end if;
end //

delimiter ;

测试函数：

mysql> select human_status(1);
+-----------------+
| human_status(1) |
+-----------------+
| ???             |
+-----------------+

竟然是3个问号。。

重建函数，指定返回数据字符集是 utf8mb4 ：

drop function if exists human_status;
delimiter //

create function human_status(p_status int) returns char(20) charset utf8mb4
begin
    if p_status=0 then 
        return '未完成';
    elseif p_status=1 then 
        return '已完成';
    elseif p_status=2 then 
        return '取消';
    else 
        return '未知';
    end if;
end //

delimiter ;

测试函数：

mysql> select human_status(1);
+-----------------+
| human_status(1) |
+-----------------+
| 已完成          |
+-----------------+

示例7

从示例6可以看出，若是字符串类型，一定要指定字符集。那么示例5的实现，就是有问题的，我们需要给参数和返回值指定字符集：

创建函数：

drop function if exists hello;
delimiter //

create function hello(msg char(10) charset utf8mb4) returns char(20) charset utf8mb4
begin
return concat('Hello ', msg);
end //

delimiter ;

测试函数：

mysql> select hello('世界') as result;
+-------------+
| result      |
+-------------+
| Hello world |
+-------------+

示例8

这是一个 case 的示例。

创建函数：

drop function if exists human_status;
delimiter //

create function human_status(p_status int) returns char(20) charset utf8mb4
begin
    case p_status
        when 0 then return '未完成';
        when 1 then return '已完成';
        when 2 then return '取消';
        else return '未知';
    end case;
end //

delimiter ;

测试函数：

mysql> select human_status(2);
+-----------------+
| human_status(2) |
+-----------------+
| 取消            |
+-----------------+

case 也可以针对字符串使用。

示例9

这是一个while的示例，其中包含了定义变量、变量设值的内容。

创建函数：

drop function if exists accumulate;
delimiter //

create function accumulate(p_end int) returns int
begin
    declare result int default 0;
    declare num int default 0;
    while num <= p_end do
        set result = result + num;
        set num = num + 1;
    end while;
    return result;
end //

delimiter ;

测试函数：

mysql> select accumulate(3);
+---------------+
| accumulate(3) |
+---------------+
|             6 |
+---------------+


( 本文完 )