我的个人技术笔记

自定义函数

7 min read

以下是对 MySQL 自定义函数（存储函数） 的总结，涵盖创建、使用、管理与注意事项。

一、什么是自定义函数

自定义函数（Stored Function）是用户自己编写的、可重复使用的 SQL 代码块，接收零或多个参数，通过计算返回一个单一值。可以直接在 SQL 语句中像内置函数一样调用。

二、创建函数的语法

DELIMITER $$
 
CREATE FUNCTION 函数名(参数1 类型1, 参数2 类型2, ...)
RETURNS 返回值类型
[DETERMINISTIC | NOT DETERMINISTIC]   -- 特性声明
[SQL SECURITY {DEFINER | INVOKER}]    -- 权限上下文
[COMMENT '描述']
BEGIN
    -- 函数体：变量声明、流程控制、RETURN 语句
    RETURN 表达式;
END$$
 
DELIMITER ;

关键点：

  • 参数只允许 IN（输入），不能指定 OUT 或 INOUT。
  • 函数体必须包含至少一条 RETURN 语句。
  • 需要先使用 DELIMITER 临时更换结束符，避免语句被错误截断。

三、函数特性选项

创建时需合理声明特性，影响复制与优化：

选项含义
DETERMINISTIC同一输入总是返回相同结果（用于主从复制、生成列等）
NOT DETERMINISTIC返回值可能变化（默认）
CONTAINS SQL函数体包含 SQL 语句，但不读写数据（默认）
NO SQL不包含 SQL 语句
READS SQL DATA只读取数据（SELECT），不修改
MODIFIES SQL DATA会修改数据（INSERT/UPDATE/DELETE）

注意： 默认特性为 NOT DETERMINISTIC, CONTAINS SQL。若函数只读且确定性，建议显式声明，否则创建生成列或函数索引时会失败。

四、函数体常用语法

1. 变量声明与赋值

DECLARE 变量名 数据类型 [DEFAULT 默认值];
SET 变量名 = 表达式;

2. 条件判断

IF 条件 THEN
   ...
ELSEIF 条件 THEN
   ...
ELSE
   ...
END IF;

或使用 CASE WHEN 表达式。

3. 循环

WHILE 条件 DO
   ...
END WHILE;
 
REPEAT
   ...
UNTIL 条件 END REPEAT;
 
loop_label: LOOP
   ...
   IF 条件 THEN LEAVE loop_label; END IF;
END LOOP;

五、完整示例

示例 1：拼接全名

DELIMITER $$
CREATE FUNCTION full_name(first_name VARCHAR(50), last_name VARCHAR(50))
RETURNS VARCHAR(101)
DETERMINISTIC
BEGIN
    RETURN CONCAT(first_name, ' ', last_name);
END$$
DELIMITER ;
 
-- 调用
SELECT full_name('张', '三');   -- 返回 '张 三'

示例 2：计算阶乘（递归不安全，用循环）

DELIMITER $$
CREATE FUNCTION factorial(n INT)
RETURNS BIGINT
DETERMINISTIC
BEGIN
    DECLARE result BIGINT DEFAULT 1;
    WHILE n > 1 DO
        SET result = result * n;
        SET n = n - 1;
    END WHILE;
    RETURN result;
END$$
DELIMITER ;
 
-- 调用
SELECT factorial(5);   -- 返回 120

六、调用函数

在 SQL 语句中直接使用，与内置函数完全一致：

SELECT full_name(first_name, last_name) FROM users;
UPDATE table SET col = 函数名(参数);

七、查看与删除

-- 查看所有自定义函数（当前库）
SHOW FUNCTION STATUS WHERE Db = '数据库名';
 
-- 查看函数创建语句
SHOW CREATE FUNCTION 函数名;
 
-- 删除函数
DROP FUNCTION IF EXISTS 函数名;

修改函数： MySQL 不支持 ALTER FUNCTION 直接修改函数体，只能修改特性（COMMENT 等）。如需变更逻辑，须 DROP 后重新 CREATE。

八、与存储过程的区别

对比项自定义函数存储过程
返回值必须返回一个值可通过 OUT 参数返回多个值，也可无返回值
调用方式嵌入 SQL 中调用CALL 存储过程名(参数);
参数模式仅 ININ、OUT、INOUT
使用场景计算、转换、封装逻辑后用于查询批量处理、事务操作、复杂业务流

九、重要注意事项

  1. 禁止在函数内修改数据
    通常自定义函数应只读（READS SQL DATA 或 NO SQL）。若函数内包含 INSERT/UPDATE/DELETE，极易导致主从复制不一致、触发器意外激活或日志记录错误。在只读从库上调用时会失败。

  2. 二进制日志安全
    声明函数为 DETERMINISTIC 且不修改数据，才能安全地用于主从复制与生成列。若声明与行为不符，复制可能出错。

  3. 性能
    函数在 SQL 中对每一行都会执行，避免在大量数据上使用复杂计算的函数。可改用查询表达式或应用层处理。

  4. 权限

    • 创建函数：需要 CREATE ROUTINE 权限。
    • 执行函数：需要 EXECUTE 权限。
    • 修改/删除：需 ALTER ROUTINE 权限及函数所属数据库的 ALTER/DROP 权限。

  5. 函数名区分大小写
    取决于操作系统和数据库配置，建议统一使用小写加下划线。

十、用户自定义函数扩展（C语言 UDF）

MySQL 还支持使用 C/C++ 编写动态加载的 用户定义函数（UDF），通过 CREATE FUNCTION ... SONAME '库文件名' 加载。这种方式功能更强大但安全风险高，不适合常规业务，通常使用存储函数即可。本总结仅针对存储函数。

合理使用自定义函数可以大幅简化 SQL 逻辑，但要谨记“只读、确定性、轻量”三原则，确保系统稳定。

Graph View