存储函数

导学

存储函数（Stored Function）与存储过程类似，但它必须有返回值，并且可以像内置函数一样嵌套在 SQL 语句中使用。本节学习如何创建和调用存储函数。

定义

存储函数：预编译并存储在数据库中的函数，接受参数（只能是 IN 模式），必须返回一个值。可以在 SQL 表达式的任何位置调用。

核心语法

DELIMITER //

CREATE FUNCTION 函数名(参数 类型)
RETURNS 返回类型
[特性声明]
BEGIN
    -- 逻辑
    RETURN 值;
END //

DELIMITER ;

完整示例一：计算加分后成绩的函数

当前数据状态

首先建立示例表并插入数据：

CREATE DATABASE IF NOT EXISTS school;
USE school;

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
);

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

当前 employees 表数据：

emp_id	emp_name	dept	score
1	大翔	技术部	100
2	白歌	技术部	NULL

操作语句

创建计算加分后成绩的存储函数：

DELIMITER //

CREATE FUNCTION ScoreAfterBonus(score DECIMAL(5,2), bonus DECIMAL(5,2))
RETURNS DECIMAL(5,2)
DETERMINISTIC
BEGIN
    RETURN score + bonus;
END //

DELIMITER ;

-- 在 SQL 中像内置函数一样使用
SELECT emp_name, score, ScoreAfterBonus(score, 10) AS new_score FROM employees;

操作后的结果

emp_name	score	new_score
大翔	100	110
白歌	NULL	NULL

结果解读

ScoreAfterBonus(score, 10) 像内置函数一样嵌套在 SELECT 语句中，对每一行数据计算加分后的成绩。DETERMINISTIC 声明表示给定相同输入，函数总是返回相同输出。

完整示例二：格式化名字的函数

当前数据状态

基于 employees 表：

emp_id	emp_name	dept	score
1	大翔	技术部	100
2	白歌	技术部	NULL

操作语句

创建名字脱敏函数：

DELIMITER //

CREATE FUNCTION NameMask(name VARCHAR(20))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    IF name IS NULL OR LENGTH(name) < 2 THEN
        RETURN name;
    END IF;
    RETURN CONCAT(LEFT(name, 1), '*');
END //

DELIMITER ;

-- 使用函数查询
SELECT emp_name, NameMask(emp_name) AS masked_name FROM employees;

操作后的结果

emp_name	masked_name
大翔	大*
白歌	白*

结果解读

NameMask 函数对名字进行脱敏处理，保留第一个字，后面用 * 替代
当 name 为 NULL 或长度不足2位时，直接原样返回，不做处理
存储函数可以嵌套在 SELECT 列表、WHERE 子句、ORDER BY 等任何 SQL 表达式位置

完整示例三：在 WHERE 子句中使用存储函数

当前数据状态

基于 employees 表：

emp_id	emp_name	dept	score
1	大翔	技术部	100
2	白歌	技术部	NULL

操作语句

使用 ScoreAfterBonus 函数在 WHERE 子句中筛选：

-- 查询加分后成绩大于等于 110 的员工
SELECT emp_name, score, ScoreAfterBonus(score, 10) AS new_score
FROM employees
WHERE ScoreAfterBonus(score, 10) >= 110;

操作后的结果

emp_name	score	new_score
大翔	100	110

结果解读

存储函数可以嵌套在 WHERE 条件中，实现复杂的筛选逻辑。这使得 SQL 查询更加灵活和可复用。注意在 WHERE 中使用函数可能导致索引失效，大数据量时需谨慎。

完整示例四：删除存储函数

操作语句

-- 安全删除：如果存在则删除
DROP FUNCTION IF EXISTS ScoreAfterBonus;

-- 删除后再调用会报错
-- SELECT ScoreAfterBonus(100, 10);  -- ERROR 1305 (42000): FUNCTION school.ScoreAfterBonus does not exist

结果解读

DROP FUNCTION IF EXISTS 安全删除函数。删除后该函数名不能再被调用，否则报 FUNCTION does not exist 错误。

函数特性声明

特性	含义
`DETERMINISTIC`	给定相同输入，总是返回相同输出（推荐声明）
`NOT DETERMINISTIC`	给定相同输入，可能返回不同输出（如依赖当前时间）
`READS SQL DATA`	函数读取数据
`NO SQL`	函数不含 SQL
`MODIFIES SQL DATA`	函数修改数据（不推荐在函数中修改数据）

⚠️ 注意：MySQL 5.7 默认启用 log_bin_trust_function_creators = OFF，创建函数需要 SUPER 权限，或设置该参数为 ON。因为函数可能在复制中造成不确定性。

解决权限限制的示例：

-- 需要 SUPER 权限执行
SET GLOBAL log_bin_trust_function_creators = 1;

完整示例五：NOT DETERMINISTIC 函数

当前数据状态

基于 employees 表：

emp_id	emp_name	dept	score
1	大翔	技术部	100
2	白歌	技术部	NULL

操作语句

创建一个依赖当前时间的非确定性函数：

DELIMITER //

CREATE FUNCTION GetCurrentYear()
RETURNS INT
NOT DETERMINISTIC
BEGIN
    RETURN YEAR(NOW());
END //

DELIMITER ;

-- 使用
SELECT emp_name, GetCurrentYear() AS current_year FROM employees;

操作后的结果

返回（结果随执行时间变化）：

emp_name	current_year
大翔	2026
白歌	2026

结果解读

NOT DETERMINISTIC 声明表示函数输出可能随时间或其他外部因素变化。NOW() 每次执行返回不同的时间戳，因此相同输入可能产生不同输出。未声明时 MySQL 默认 NOT DETERMINISTIC，可能影响复制和查询缓存。

存储函数 vs 存储过程

特性	存储函数	存储过程
返回值	必须有 1 个	可以没有或有多个（结果集）
调用方式	嵌套在 SQL 中	`CALL` 调用
参数模式	只能 `IN`	`IN`、`OUT`、`INOUT`
使用场景	计算、转换	复杂业务流程
返回结果集	不可以	可以

常见误区

误区	正解
函数可以修改数据	技术上可以，但极不推荐。函数应只负责计算和返回。
函数不需要声明 `DETERMINISTIC`	建议声明。未声明时 MySQL 默认 `NOT DETERMINISTIC`，可能影响复制。
函数和存储过程完全等价	调用方式和使用场景不同，不可完全互换。

面试考点

Q：存储函数和存储过程的区别？

函数必须有返回值，只能有 IN 参数，可以嵌套在 SQL 表达式中；过程通过 CALL 调用，可以有 IN/OUT/INOUT 参数，可以返回多个结果集，不能嵌套在 SQL 中。

Q：为什么函数中不建议修改数据？

函数的设计目的是计算和返回值。在函数中修改数据会导致副作用，使代码难以理解和维护，也可能在复制环境中造成问题。

Q：DETERMINISTIC 和 NOT DETERMINISTIC 的区别？

DETERMINISTIC 表示相同输入总是产生相同输出（如字符串拼接）；NOT DETERMINISTIC 表示输出可能变化（如依赖 NOW()）。声明 DETERMINISTIC 有助于优化器缓存结果。

小结

存储函数必须有返回值，可以嵌套在 SQL 中使用
参数只能是 IN 模式
建议声明 DETERMINISTIC 特性
函数应只做计算，不应修改数据

下一章引子：存储过程和函数中需要使用变量来临时存储数据，MySQL 提供了丰富的变量机制。