MySQL 存储函数

恋喵大鲤鱼

发布于 2023-10-12 16:09:15

3820

发布于 2023-10-12 16:09:15

文章被收录于专栏：C/C++基础

1.简介

MySQL 存储函数（Stored Function）和存储过程类似，也是存储在数据库中的程序，但它会返回一个计算结果。

存储函数可以和内置函数或者表达式一样用于 SQL 语句，可以提高代码的可读性以及可维护性。

MySQL 存储过程和存储函数统称为存储例程（Stored Routine）。存储程序包含存储例程、触发器和事件。存储对象包括存储程序和视图。

2.创建存储函数

CREATE
    [DEFINER = user]
    FUNCTION [IF NOT EXISTS] func_name([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

routine_body:
    Valid SQL routine statement

其中，sp_name 是存储函数名称；param_name 是参数名称，所有的参数都是输入参数；type 是参数或者返回值的数据类型；RETURNS 定义了返回值的类型；routine_body 是存储函数的具体实现。

在创建存储函数时还可以指定一些可选的属性，这些属性与创建存储过程时的属性是一致的。关于这些属性的含义请参见 MySQL 存储过程。

下面是创建函数的一个实例。

mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
       RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

上面的示例函数接收一个参数，使用 SQL 函数 CONCAT 执行拼接操作，并返回结果。

DETERMINISTIC 属性表示这是一个确定性函数，对于相同的输入参数一定会返回相同的结果；MySQL 默认创建的是非确定性函数（NOT DETERMINISTIC）。

与存储过程不同的是，使用 mysql 客户端创建存储函数没有必要自定义分隔符，因为函数定义不包含语句分隔符分号。

3.调用存储函数

在 MySQL 中，可以通过在 SQL 查询中使用 SELECT 调用存储函数。

比如调用上面创建的存储函数 hello 并传入字符串 world。

mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

4.查看存储函数

SHOW FUNCTION STATUS

查看存储函数的方式和存储过程类似，只需要将 PROCEDURE 替换成 FUNCTION 即可。

SHOW FUNCTION STATUS
    [LIKE 'pattern' | WHERE expr]

例如以下语句将返回了存储函数 hello 的相关信息。

mysql> SHOW FUNCTION STATUS WHERE name = 'hello'\G
*************************** 1. row ***************************
                  Db: test
                Name: hello
                Type: FUNCTION
             Definer: testuser@localhost
            Modified: 2023-08-22 11:10:03
             Created: 2023-08-22 11:10:03
       Security_type: DEFINER
             Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci

SHOW CREATE FUNCTION

如果想查看存储函数的定义，可以使用 SHOW CREATE FUNCTION 语句。

SHOW CREATE FUNCTION func_name

例如查看存储函数 hello 的定义。

mysql> SHOW CREATE FUNCTION test.hello\G
*************************** 1. row ***************************
            Function: hello
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
                      NO_ZERO_IN_DATE,NO_ZERO_DATE,
                      ERROR_FOR_DIVISION_BY_ZERO,
                      NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`testuser`@`localhost`
                      FUNCTION `hello`(s CHAR(20))
                      RETURNS char(50) CHARSET utf8mb4
                      DETERMINISTIC
                      RETURN CONCAT('Hello, ',s,'!')
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci

事实上 SHOW FUNCTION STATUS 和 SHOW CREATE FUNCTION 均是从系统表 INFORMATION_SCHEMA.ROUTINES 获取存储函数元信息，所以我们也可以直接查看 INFORMATION_SCHEMA.ROUTINES 表查看存储函数元信息。

5.修改存储函数

修改存储函数与修改存储过程类似，可以使用 ALTER FUNCTION 语句修改存储函数的属性。

ALTER FUNCTION func_name [characteristic ...]

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

上面的语句不能更改存储函数的参数或主体。要进行此类更改，必须使用 DROP FUNCTION 和 CREATE FUNCTION 删除并重新创建该存储函数。

您必须具有该函数的 ALTER ROUTINE 权限。（该权限会自动授予函数创建者。）如果启用了二进制日志记录，则 ALTER FUNCTION 语句可能还需要 SUPER 权限