14.5 函数的创建与使用

在 MySQL 中，除了存储过程，还有另一种可编程对象——存储函数（Stored Function）。与存储过程不同，存储函数必须返回一个单一的值，并且可以像内置函数（如 NOW()、CONCAT()）一样在 SQL 语句中直接使用。本节将详细介绍存储函数的创建、使用及其与存储过程的区别。

一、什么是存储函数？

存储函数 是一组预编译的 SQL 语句，它接受参数，执行计算并返回一个结果值。函数的主要特点包括：

• 必须返回一个值（使用 RETURN 语句）。

• 可以在 SQL 表达式中直接调用，例如在 SELECT、WHERE、SET 等子句中。

• 通常用于封装常用的计算逻辑或数据转换规则。

存储函数 vs. 存储过程

二、创建函数的语法

DELIMITER //

CREATE FUNCTION 函数名(参数列表)
RETURNS 返回类型
[DETERMINISTIC | NOT DETERMINISTIC]
[CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA]
[SQL SECURITY DEFINER | INVOKER]
[COMMENT '描述信息']
BEGIN
    -- 函数体
    RETURN 值;
END //

DELIMITER ;

语法说明

• 参数列表：与存储过程类似，但只能使用 IN 参数（默认就是 IN，无需指定）。每个参数包括参数名和数据类型。

• RETURNS 类型：声明函数返回值的类型，可以是任何有效的 MySQL 数据类型。

• DETERMINISTIC / NOT DETERMINISTIC：表示函数是否为确定性的。确定性函数在给定相同参数时总是返回相同结果（如 ABS(-5)）。这对于复制和优化很重要。如果函数包含 NOW()、RAND() 等不确定因素，应声明为 NOT DETERMINISTIC，否则可能导致主从数据不一致。

• CONTAINS SQL / NO SQL / READS SQL DATA / MODIFIES SQL DATA：描述函数中 SQL 语句的类型，用于优化和安全。通常可以省略，MySQL 会自动推断。

• SQL SECURITY：指定函数的执行权限。DEFINER 表示以定义者的权限执行；INVOKER 表示以调用者的权限执行。

• COMMENT：可选的描述信息。

• 函数体：由 BEGIN ... END 包裹，可以包含局部变量、流程控制等，最后必须有一个 RETURN 语句返回结果。

基础示例

示例1：创建一个简单的函数，返回员工的年薪（假设工资是月薪）

DELIMITER //

CREATE FUNCTION GetAnnualSalary(monthly_salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE annual DECIMAL(10,2);
    SET annual = monthly_salary * 12;
    RETURN annual;
END //

DELIMITER ;

调用：

SELECT name, salary, GetAnnualSalary(salary) AS annual_salary
FROM employees;

示例2：根据员工ID获取其部门名称

假设我们有一个 employees 表和一个 departments 表：

DELIMITER //

CREATE FUNCTION GetDeptName(emp_id INT)
RETURNS VARCHAR(50)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE dept_name VARCHAR(50);
    
    SELECT d.name INTO dept_name
    FROM employees e
    JOIN departments d ON e.dept_id = d.id
    WHERE e.id = emp_id;
    
    RETURN dept_name;
END //

DELIMITER ;

调用：

SELECT id, name, GetDeptName(id) AS department
FROM employees;

注意：此函数读取了数据库，因此我们声明了 READS SQL DATA。

示例3：格式化日期函数

DELIMITER //

CREATE FUNCTION FormatDate(input_date DATE)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    RETURN DATE_FORMAT(input_date, '%Y年%m月%d日');
END //

DELIMITER ;

调用：

SELECT name, FormatDate(hire_date) AS hire_date_cn FROM employees;

三、函数中的流程控制

函数体同样支持 IF、CASE、循环等流程控制。例如，计算某个数值的等级：

DELIMITER //

CREATE FUNCTION GetScoreLevel(score INT)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE level VARCHAR(20);
    
    IF score >= 90 THEN
        SET level = '优秀';
    ELSEIF score >= 60 THEN
        SET level = '及格';
    ELSE
        SET level = '不及格';
    END IF;
    
    RETURN level;
END //

DELIMITER ;

四、注意事项与限制

1. 函数必须返回一个值：每个执行路径都必须有 RETURN 语句，否则会报错。

2. 不能使用 OUT/INOUT 参数：函数只有输入参数，不支持输出参数。如果需要返回多个值，可以考虑存储过程。

3. 函数中不能使用可能导致不确定结果的语句：如 INSERT、UPDATE、DELETE 等修改数据的语句？实际上，函数可以包含修改数据的语句，但必须声明 MODIFIES SQL DATA，且不能在某些上下文中调用（例如在查询中调用修改数据的函数会引发错误）。通常不建议在函数中修改数据，因为函数主要用于计算和查询。

4. 确定性声明：如果函数包含 RAND()、NOW()、UUID() 等非确定性函数，必须声明为 NOT DETERMINISTIC，否则可能导致复制环境中的数据不一致。

5. 递归限制：MySQL 支持递归函数调用，但默认最大递归深度为 0（即不允许递归），需要设置 max_sp_recursion_depth 参数才能启用。

6. 权限：创建函数需要 CREATE ROUTINE 权限，执行需要 EXECUTE 权限。

7. 与内置函数冲突：避免函数名与 MySQL 内置函数同名，否则调用时需要加上数据库名前缀或使用特殊语法。

五、查看和删除函数

查看已有函数

SHOW FUNCTION STATUS WHERE db = '数据库名';
SHOW CREATE FUNCTION 函数名;

删除函数

DROP FUNCTION [IF EXISTS] 函数名;

六、综合示例：使用函数生成员工邮箱

假设公司邮箱规则为：姓名的拼音（小写） + @company.com。我们可以创建一个函数来自动生成邮箱。

DELIMITER //

CREATE FUNCTION GenerateEmail(full_name VARCHAR(50))
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    DECLARE email_prefix VARCHAR(50);
    -- 简单示例：将姓名转换为拼音（实际可能需要拼音转换库，这里简化处理）
    -- 假设姓名已经是拼音形式，如 'zhang san'
    SET email_prefix = LOWER(REPLACE(full_name, ' ', '.'));
    RETURN CONCAT(email_prefix, '@company.com');
END //

DELIMITER ;

使用：

SELECT name, GenerateEmail(name) AS email FROM employees;

小结

• 存储函数 是一种返回单一值的数据库对象，可在 SQL 语句中直接使用。

• 创建函数使用 CREATE FUNCTION 语法，必须指定返回值类型和 RETURN 语句。

• 函数参数默认为 IN，不能使用 OUT 或 INOUT。

• 函数体内可以使用流程控制、变量和 SQL 查询（需声明相应特性）。

• 合理声明 DETERMINISTIC 特性对于复制和优化至关重要。

• 函数主要用于封装计算逻辑，提高代码复用性和查询可读性。