MySQL 快速上手

# 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 存储过程 (opens new window)。

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

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 权限

# 6.删除存储函数

MySQL 使用 DROP FUNCTION 语句可以删除存储函数。

DROP FUNCTION [IF EXISTS] func_name

如果删除的存储函数不存在，将会返回一个错误信息。使用 IF EXISTS 可以避免该错误。

# 参考文献

MySQL 8.0 Reference Manual :: 25 Stored Objects (opens new window)

13.1.17 CREATE PROCEDURE and CREATE FUNCTION ... (opens new window)

13.7.7.20 SHOW FUNCTION STATUS Statement (opens new window)

13.7.7.8 SHOW CREATE FUNCTION Statement (opens new window)

13.1.4 ALTER FUNCTION Statement (opens new window)

13.1.29 DROP PROCEDURE and DROP FUNCTION Statements (opens new window)

← 触发器 →