MySQL存储函数

MySQL存储函数（Stored Function）是用户自定义的、封装了特定逻辑的可重用代码单元，与存储过程类似，但必须返回一个值，且可直接在SQL语句中调用。

---

一、核心特性与使用场景

1. 核心特性

• 返回值强制：必须通过RETURN语句返回单个值（标量或NULL）。
• SQL集成：可直接在SELECT、WHERE等子句中调用，类似内置函数。
• 事务安全：默认在事务中执行，支持事务控制（如DECLARE CONTINUE HANDLER）。

2. 典型使用场景

• 复杂计算封装：如税率计算、字符串格式化等。
• 业务逻辑复用：减少重复代码（如权限校验、日志记录）。
• 数据转换：将原始数据转换为业务语义明确的值（如状态码转文本）。

---

二、语法详解

1. 创建函数模板

DELIMITER //
CREATE FUNCTION function_name (
    param1 datatype [DEFAULT default_value],
    param2 datatype,
    ...
)
RETURNS return_datatype
[DETERMINISTIC | NOT DETERMINISTIC]
[SQL SECURITY { DEFINER | INVOKER }]
[COMMENT 'string']
BEGIN
    -- 函数逻辑（使用DECLARE声明局部变量）
    RETURN value;
END //
DELIMITER ;

2. 关键参数说明

• DETERMINISTIC：声明函数是否为确定性函数（相同输入总返回相同输出）。
• SQL SECURITY：定义执行权限（DEFINER使用创建者权限，INVOKER使用调用者权限）。
• COMMENT：添加函数描述（便于文档化）。

---

三、实战示例

示例1：计算圆的面积

DELIMITER //
CREATE FUNCTION CalculateCircleArea(radius DOUBLE)
RETURNS DOUBLE
DETERMINISTIC
COMMENT '计算圆的面积（π≈3.14159）'
BEGIN
    DECLARE pi DOUBLE DEFAULT 3.14159;
    RETURN pi * radius * radius;
END //
DELIMITER ;

-- 调用示例
SELECT CalculateCircleArea(5) AS Area; -- 输出 78.53975

示例2：员工薪水等级转换（业务逻辑封装）

DELIMITER //
CREATE FUNCTION GetSalaryGrade(salary DECIMAL(10,2))
RETURNS VARCHAR(20)
DETERMINISTIC
COMMENT '根据薪水返回等级（A: >10000, B: 5000-10000, C: <5000）'
BEGIN
    DECLARE grade VARCHAR(20);
    
    CASE
        WHEN salary > 10000 THEN  grade = 'A';
        WHEN salary >= 5000 THEN  grade = 'B';
        ELSE grade = 'C';
    END CASE;
    
    RETURN grade;
END //
DELIMITER ;

-- 调用示例
SELECT employee_name, GetSalaryGrade(salary) AS Grade FROM Employee;

示例3：第 N 高的薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT; 
    SET M = N-1; 
  RETURN (
      SELECT DISTINCT salary
      FROM Employee
      ORDER BY salary DESC
      LIMIT M, 1
  );
END

四、高级技巧与注意事项

1. 性能优化

• 避免游标：尽量使用集合操作替代逐行处理。
• 索引利用：确保函数内查询能命中索引（如WHERE条件字段）。
• 缓存结果：对频繁调用且结果稳定的函数，可考虑缓存机制。

2. 错误处理

• 显式错误：使用SIGNAL SQLSTATE主动抛出业务错误。
• 隐式处理：通过DECLARE ... HANDLER捕获异常并返回默认值。

3. 安全控制

• 权限分离：通过SQL SECURITY控制函数执行权限。
• 防SQL注入：对输入参数进行严格校验（如使用正则表达式）。

4. 调试技巧

• 临时输出：使用SELECT语句在函数内打印中间结果（需开启LOG_OUTPUT）。
• 逐步注释：通过注释代码块定位逻辑错误。

---

五、与存储过程的对比

特性	存储函数	存储过程
返回值	必须通过RETURN返回单个值	通过OUT参数返回值
SQL调用	可直接在SELECT中使用	需通过CALL执行
事务控制	默认在事务中	需显式控制事务
错误处理	支持SIGNAL和HANDLER	同函数

---

六、最佳实践

1. 命名规范：使用fn_前缀，如fn_calculate_tax；统一规范降低沟通成本（如 fn_ 代表函数，sp_ 代表存储过程）。
2. 注释文档：为复杂函数添加COMMENT和行内注释。
3. 单元测试：编写测试用例覆盖正常/边界/异常场景。
4. 版本控制：通过CREATE OR REPLACE实现安全更新。
5. 依赖管理：避免函数间循环依赖，保持逻辑解耦。

通过合理使用存储函数，可显著提升MySQL数据库的逻辑封装能力和代码复用性，但需权衡性能与维护成本。