技术文档

Appearance

MySQL 存储函数详解：从入门到生产可用的完整指南 ​

一、存储函数是什么：用"奶茶店配方"来理解 ​

想象你开了一家奶茶店。店里有一份标准配方："中杯 + 三分糖 + 珍珠 + 椰果 = 招牌奶茶"。顾客只需要说"来一杯招牌奶茶"，店员就知道该做什么——不用每次都重复完整配方。

MySQL 存储函数就是数据库里的"标准配方"：

  • 你把一段固定的计算逻辑（比如"根据订单金额计算积分"）预先写好，起个名字保存在数据库里；
  • 以后任何 SQL 语句里需要这个计算，直接调用函数名字就行，就像点"招牌奶茶"一样简单；
  • 它有一个铁律：必须返回一个结果——就像快递驿站的取件码，你报手机号，系统必须给你对应的取件码，不能"查完了但不告诉你"。

1. 基础定义 ​

存储函数（Stored Function）是存储在数据库服务器上的一段可复用代码，接受输入参数，经过计算后必须返回一个单一的值。

它和存储过程（Stored Procedure）长得几乎一样，但定位完全不同。用一张表说清楚：

对比项存储函数（Function）存储过程（Procedure）
返回值必须返回一个值（RETURN）不返回值，或返回结果集/多个 OUT 参数
调用方式直接在 SQL 里使用：SELECT fn(x)独立调用：CALL proc(x)
事务能力不能开启/提交事务可以控制事务（START TRANSACTION 等）
设计定位像一个"计算工具"，嵌在查询里用像一个"操作流程"，独立执行多步任务
典型场景计算积分、格式化金额、转换状态码转账、批量清洗数据、生成报表

一句话区分：函数是"我给你输入，你给我一个结果"；过程是"我给你指令，你给我做完一系列事"。

2. 什么时候用存储函数 ​

适合的场景：

  • 高频复用的计算规则：比如电商系统里"订单金额 → 用户积分"的换算，Java 代码、定时任务、数据报表都要用，写成函数只改一处；
  • SQL 里的格式化逻辑：把数据库状态码 1/2/3 翻译成"待付款/已发货/已完成"，直接在 SELECT 里调用；
  • 数据库内部的数学计算：比如根据经纬度算两点距离，放在 SQL 的 WHERE 或 SELECT 里做筛选。

不适合的场景（重要）：

  • 需要返回多行数据：函数只能返回一个值，多行结果请用存储过程或直接写查询；
  • 需要控制事务：函数里不能 COMMIT/ROLLBACK，复杂事务流程请用存储过程；
  • 计算逻辑特别复杂且频繁变化：函数改起来比改 Java 代码麻烦，调试也困难。

二、第一个存储函数：10 行代码上手 ​

1. 最简示例：计算订单积分 ​

业务规则：消费 1 元积 1 分，不足 1 元部分不计（即取整）。

sql
-- 切换结束符：因为函数体内部有分号，避免 MySQL 提前断句
DELIMITER $$

-- 创建函数：名字叫 calc_points，接收一个订单金额参数
CREATE FUNCTION calc_points(p_amount DECIMAL(10,2))
RETURNS INT                    -- 声明返回值类型：积分是整数
DETERMINISTIC                  -- 声明：同样的输入永远返回同样的结果
BEGIN
    -- DECLARE：声明局部变量，只在函数内部有效
    DECLARE v_points INT DEFAULT 0;

    -- FLOOR 取整：100.99 元 → 100 分
    SET v_points = FLOOR(p_amount);

    -- RETURN：把结果"交回"给调用方，必须执行到
    RETURN v_points;
END$$

-- 恢复默认结束符
DELIMITER ;

2. 调用函数 ​

存储函数可以直接出现在 SELECT、WHERE、甚至 UPDATE 里，就像内置函数 NOW()、SUM() 一样：

sql
-- 在 SELECT 中调用
SELECT calc_points(99.50) AS 积分;
-- 输出：99

-- 在查询中调用：给每个订单算积分
SELECT order_no, amount, calc_points(amount) AS points
FROM orders
WHERE user_id = 1001;

-- 在 UPDATE 中调用：批量更新用户积分
UPDATE user SET points = points + calc_points(199.00) WHERE id = 1001;

预期输出示例：

+----------+
| 积分     |
+----------+
|       99 |
+----------+

代码逻辑讲解：

  • p_amount DECIMAL(10,2)：输入参数，DECIMAL(10,2) 表示最多 10 位数、保留 2 位小数，适合金额；
  • RETURNS INT：告诉 MySQL 这个函数最后会交回一个整数；
  • DETERMINISTIC：这是"确定性"声明，后面详细讲，这里先记住"纯计算函数都要写它"；
  • FLOOR()：MySQL 内置数学函数，向下取整；
  • RETURN：函数的核心，执行到这一行就把值返回给调用方。

3. 查看和删除函数 ​

sql
-- 查看当前库有哪些函数
SHOW FUNCTION STATUS WHERE Db = '你的数据库名';

-- 查看函数具体定义
SHOW CREATE FUNCTION calc_points;

-- 删除函数
DROP FUNCTION IF EXISTS calc_points;

踩坑提示：MySQL 的函数没有"修改"语法，想改逻辑只能 DROP 后重新 CREATE。

三、DETERMINISTIC 参数：容易被忽略的关键开关 ​

1. 什么是确定性（Deterministic） ​

这个词听起来很学术，用一句话解释：同样的输入，是否永远得到同样的输出？

  • calc_points(99.50) 无论算多少次，结果都是 99 → 确定性（DETERMINISTIC）
  • NOW() 每次调用结果都不一样 → 不确定性（NOT DETERMINISTIC）
  • RAND() 每次生成随机数 → 不确定性（NOT DETERMINISTIC）

2. 为什么必须声明 ​

MySQL 开启二进制日志（binlog，用于主从复制和数据备份）时，默认要求函数必须明确声明自己的确定性。否则创建函数会报错：

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration...

三种声明的含义：

声明含义适用场景
DETERMINISTIC确定性：输入相同，输出必然相同纯计算、格式化、查固定表
NOT DETERMINISTIC不确定性：输入相同，输出可能不同函数里调用了 NOW()、RAND()
READS SQL DATA会读取数据表，但不修改函数里写了 SELECT 查询
NO SQL完全不碰 SQL纯数学计算

重要规则：如果你的函数里只是数学计算，一定要写 DETERMINISTIC。这不仅是为了过 1418 错误，还会带来两个实际好处：

  1. 主从复制安全：从库重放 binlog 时，确定性函数能保证和主库算出一样的结果；
  2. 查询缓存命中：MySQL 可以缓存确定性函数的结果，同样的输入直接返回缓存值。

3. 什么时候用 NOT DETERMINISTIC ​

当函数依赖外部环境时，必须如实声明：

sql
DELIMITER $$

-- 获取当前服务器时间的格式化字符串（每次调用结果不同）
CREATE FUNCTION get_current_time_str()
RETURNS VARCHAR(20)
NOT DETERMINISTIC          -- 如实声明：结果不确定
NO SQL
BEGIN
    RETURN DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
END$$

DELIMITER ;

踩坑提示：如果函数里用了 NOW() 却写了 DETERMINISTIC，主从复制时从库会算出错误的时间，数据就悄悄对不上了。

四、实战案例：三种数据类型的完整示例 ​

案例 1：数值计算 —— 电商订单阶梯积分 ​

业务规则：订单金额 ≤ 100 元，1 元 = 1 分；100~500 元部分，1 元 = 2 分；500 元以上部分，1 元 = 3 分。

sql
DELIMITER $$

CREATE FUNCTION calc_points_step(p_amount DECIMAL(10,2))
RETURNS INT
DETERMINISTIC
NO SQL
BEGIN
    DECLARE v_points INT DEFAULT 0;

    IF p_amount <= 100 THEN
        -- 全部按 1:1 计算
        SET v_points = FLOOR(p_amount);
    ELSEIF p_amount <= 500 THEN
        -- 前 100 元按 1:1，超出部分按 1:2
        SET v_points = 100 + FLOOR((p_amount - 100) * 2);
    ELSE
        -- 前 100 元 1:1，100~500 元 1:2，超出 500 元 1:3
        SET v_points = 100 + 800 + FLOOR((p_amount - 500) * 3);
    END IF;

    RETURN v_points;
END$$

DELIMITER ;

测试与预期输出：

sql
SELECT calc_points_step(50) AS test1;    -- 50 元 → 50 分
SELECT calc_points_step(200) AS test2;   -- 100 + 100×2 = 300 分
SELECT calc_points_step(600) AS test3;   -- 100 + 800 + 100×3 = 1200 分

案例 2：字符串处理 —— 脱敏用户手机号 ​

业务规则：13812345678 → 138****5678，保护用户隐私。

sql
DELIMITER $$

CREATE FUNCTION mask_phone(p_phone VARCHAR(20))
RETURNS VARCHAR(20)
DETERMINISTIC
NO SQL
BEGIN
    -- 参数校验：空值或长度不对直接返回原值
    IF p_phone IS NULL OR LENGTH(p_phone) < 7 THEN
        RETURN p_phone;
    END IF;

    -- CONCAT 拼接：前3位 + **** + 后4位
    RETURN CONCAT(
        LEFT(p_phone, 3),           -- 取左边3位：138
        '****',                      -- 中间打码
        RIGHT(p_phone, 4)            -- 取右边4位：5678
    );
END$$

DELIMITER ;

测试与预期输出：

sql
SELECT mask_phone('13812345678') AS 脱敏结果;
-- 输出：138****5678

SELECT mask_phone(NULL) AS 空值测试;
-- 输出：NULL

案例 3：日期时间处理 —— 判断订单是否超时未支付 ​

业务规则：订单创建后 30 分钟未支付视为超时。

sql
DELIMITER $$

CREATE FUNCTION is_order_timeout(
    p_create_time DATETIME,      -- 订单创建时间
    p_pay_time DATETIME          -- 支付时间（未支付为 NULL）
)
RETURNS TINYINT                   -- 返回 1=已超时，0=未超时
DETERMINISTIC
NO SQL
BEGIN
    -- 已支付的订单永远不算超时
    IF p_pay_time IS NOT NULL THEN
        RETURN 0;
    END IF;

    -- TIMESTAMPDIFF 计算两个时间差多少分钟
    IF TIMESTAMPDIFF(MINUTE, p_create_time, NOW()) > 30 THEN
        RETURN 1;                 -- 超过30分钟未支付，超时
    ELSE
        RETURN 0;
    END IF;
END$$

DELIMITER ;

使用示例：

sql
-- 筛选出所有超时未支付的订单
SELECT order_no, create_time
FROM orders
WHERE status = '待支付' AND is_order_timeout(create_time, pay_time) = 1;

踩坑提示：这个函数用了 NOW()，严格来说应该写 NOT DETERMINISTIC。但因为这个函数只用于业务查询、不用于主从复制一致性场景，且 DETERMINISTIC 能获得缓存收益，实际项目中很多团队会"睁一只眼闭一只眼"。如果涉及主从复制数据一致性，务必如实写 NOT DETERMINISTIC。

五、存储函数与事务的关系：别在函数里"玩火" ​

1. 函数里不能控制事务 ​

存储函数不能执行 START TRANSACTION、COMMIT、ROLLBACK。试图写会报错：

sql
-- ❌ 错误：函数里不能开事务
CREATE FUNCTION bad_func() RETURNS INT
BEGIN
    START TRANSACTION;   -- 报错！函数不允许控制事务
    RETURN 1;
END;

2. 函数与主事务的联动逻辑 ​

虽然函数不能控制事务，但它会参与调用方所在的事务：

sql
START TRANSACTION;

-- 这个 UPDATE 在事务里，它调用的函数也在同一个事务上下文里
UPDATE user SET points = points + calc_points(100) WHERE id = 1;

-- 如果这里 ROLLBACK，上面的 UPDATE 会回滚，但函数本身没有副作用
ROLLBACK;

关键认知：函数应该是"无副作用的纯计算"——它只负责算数，不负责改数据。如果函数里偷偷写了 UPDATE/INSERT，会导致两个问题：

  1. 这些修改不受主事务保护（因为函数不能开启子事务），主事务回滚时它们可能已落库；
  2. 开启 binlog 时，修改数据的函数必须声明 MODIFIES SQL DATA，否则从库重放会失败。

最佳实践：函数里只写 SELECT（查询）和计算逻辑，绝不写 INSERT/UPDATE/DELETE。

六、权限配置：谁能创建、谁能调用 ​

1. 创建函数需要的权限 ​

普通业务账号通常没有创建函数的权限，需要 DBA 授权：

sql
-- 给账号授权：允许在 test_db 库上创建函数
GRANT CREATE ROUTINE ON test_db.* TO 'dev_user'@'%';

-- 同时需要 ALTER ROUTINE 才能修改（删除重建）
GRANT ALTER ROUTINE ON test_db.* TO 'dev_user'@'%';

2. 调用函数的权限 ​

sql
-- 只给执行权限，不给表权限（安全隔离）
GRANT EXECUTE ON FUNCTION test_db.calc_points TO 'app_user'@'%';

-- 回收权限
REVOKE EXECUTE ON FUNCTION test_db.calc_points FROM 'app_user'@'%';

安全价值：应用账号只拿到 EXECUTE 权限，看不到底层表结构，也无法直接操作数据——它只能调用你允许的函数，这是比"给表权限"更细粒度的管控。

七、性能优化与缓存策略 ​

1. 存储函数 vs 应用层函数的性能对比 ​

以"计算 100 万行订单的积分"为例：

方案耗时网络传输适用场景
Java 代码逐行计算~12s100 万行逻辑复杂、需要外部 API
SQL 调用存储函数批量算~1.5s0 行纯数据计算、逻辑稳定
应用层先缓存规则再批量算~2s100 万行规则经常变、需热更新

结论：纯数据计算且逻辑稳定的场景，存储函数性能碾压应用层（省去百万次网络往返）；但逻辑复杂或需频繁改规则时，应用层更灵活。

2. 大流量场景下的缓存策略 ​

如果函数被高并发调用（比如每次列表查询都调 calc_points），考虑两级缓存：

第一级：MySQL 内部缓存

  • 确保函数写了 DETERMINISTIC，MySQL 会缓存相同输入的结果；
  • 在 SELECT 列表中调用函数时，如果 WHERE 条件没变，MySQL 可能复用上次计算。

第二级：应用层缓存

  • 把"订单金额 → 积分"的映射结果缓存到 Redis，设置短 TTL；
  • 适合"金额重复率高"的场景（如商品都是标准价格 99/199/299）。

第三级：结果预计算

  • 如果订单创建后金额不变，在写入订单时就把积分算好存到 points 字段，查询时直接读字段，完全绕开函数调用。

八、常见坑点避坑指南 ​

坑 1：函数名和内置函数冲突 ​

sql
-- ❌ 危险：创建了和内置函数同名的函数
CREATE FUNCTION now() ...   -- 会覆盖内置的 NOW()！

对策：自定义函数统一加前缀，如 fn_calc_points、fn_mask_phone。

坑 2：忘记写 RETURN 或路径没走到 RETURN ​

sql
-- ❌ 错误：某些分支没有 RETURN，会报错
CREATE FUNCTION bad_logic(p INT) RETURNS INT
BEGIN
    IF p > 0 THEN
        RETURN 1;
    END IF;
    -- p <= 0 时没有 RETURN！
END;

对策：确保所有代码路径都走到 RETURN，通常把 RETURN 放在函数最后一行。

坑 3：参数类型不匹配导致隐式转换 ​

sql
-- 函数定义：p_amount DECIMAL(10,2)
SELECT calc_points('一百元');   -- ❌ 字符串传入，MySQL 隐式转换可能出错或警告

对策：调用前确保类型匹配，应用层做好参数校验。

坑 4：在函数里查询正在修改的表 ​

sql
-- ❌ 危险：函数里 SELECT 了表，而调用方正在 UPDATE 这张表
CREATE FUNCTION get_user_level(p_user_id BIGINT) RETURNS INT
BEGIN
    DECLARE v_total DECIMAL(10,2);
    SELECT SUM(amount) INTO v_total FROM orders WHERE user_id = p_user_id;
    RETURN ...;
END;

-- 调用：
UPDATE orders SET status = '已完成'
WHERE user_id = 1001 AND get_user_level(1001) > 100;   -- 可能死锁或读到中间态

对策：函数里查询的表，和调用方 UPDATE 的表，避免是同一张表。

坑 5：认为"存储函数会降低可维护性" ​

这是常见误区。客观地说：

  • 确实存在的成本：函数在数据库里，不像 Java 代码有 Git 版本管理、单元测试、代码审查；
  • 被夸大的部分：对于稳定、通用、纯计算的逻辑（如积分规则、脱敏规则），存储函数反而提升可维护性——规则只在一处定义，多系统复用。

正确的姿势：把存储函数纳入数据库变更管理（用 Flyway/Liquibase 管理函数定义），像管理表结构变更一样管理它。

九、总结：一张速查表 ​

知识点核心要点
本质存在数据库里的"配方"，必须返回一个值
与存储过程区别函数必须 RETURN 一个值，过程不返回值、可控制事务
创建语法DELIMITER 切换 + CREATE FUNCTION + RETURN
确定性声明纯计算写 DETERMINISTIC，用 NOW()/RAND() 写 NOT DETERMINISTIC
数据类型实战数值（积分计算）、字符串（脱敏）、日期（超时判断）
事务关系函数不能控制事务，但参与调用方事务；绝不写修改语句
权限EXECUTE 权限实现细粒度管控
性能纯计算场景比应用层快 5~10 倍；高并发考虑 Redis 缓存
最大坑点函数名加前缀、所有路径必须有 RETURN、避免查正在改的表

最后一句忠告：存储函数是"小而美"的工具——把它用在稳定、通用、纯计算的场景，它就是性能利器；把它当万能胶水到处乱用，它就是维护噩梦。先小范围试点，验证稳定后再推广。

网站备案号:津ICP备15001525号-2