MySQL 学习笔记

MySQL是一个开源的关系型数据库,非常流行。

如无特殊说明,本专题文章的 SQL 均在 5.6 版本中验证。

👉文章列表
MySQL 学习笔记 ⏬ 创建和删除数据库 切换和查看数据库 创建和删除表 在表中增加、删除、修改列 添加和删除索引 增删查改 创建和删除主键 大小写和反引号 字符串类型值的大小写 SQL注释 CHAR类型 VARCHAR类型 整型数字 时间戳 不要使用utf8 唯一索引与NULL 唯一索引的单列长度限制 NULL的判等 DDL、DML、DCL的含义 查看表的状态 show table status InnoDB存储引擎的限制 if和case的使用 自定义函数 InnoDB 索引 事务 事务隔离级别的概念、查看和修改 读已提交(read committed)隔离级别下的锁 可重复读(repeatable read)隔离级别下的锁 修改自增主键id的类型 使用 load data 快速导入数据 使用 select into outfile 导出数据

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 |
+---------------+

( 本文完 )

文章目录