MySQL自定义函数

MySQL 自定义函数详解：单一返回值的 SQL 逻辑封装

MySQL 自定义函数（Function）是一组预先编译好的 SQL 语句集合，与存储过程类似，但只能返回一个值，主要用于数据计算或转换。本文详细讲解自定义函数的创建、使用及注意事项，帮助你灵活封装复用逻辑。

自定义函数的核心特性

• 单一返回值：函数必须有且仅有一个返回值（通过 RETURN 语句），而存储过程可返回多个值。
• 嵌入查询：函数可作为表达式的一部分嵌入 SELECT、WHERE 等子句中，存储过程需用 CALL 单独调用。
• 适用场景：适合封装简单的计算逻辑（如数据转换、聚合计算），复杂业务逻辑更适合用存储过程。

自定义函数的基本语法

创建函数

DELIMITER 自定义结束符  -- 如 $，避免与函数体内的分号冲突
CREATE FUNCTION 函数名(参数列表) 
RETURNS 返回值类型  -- 声明返回值的数据类型（如 INT、VARCHAR）
DETERMINISTIC  -- 可选，用于二进制日志安全（见后文说明）
BEGIN
  -- 函数体（SQL语句集合，必须包含 RETURN 语句）
  RETURN 返回值;
END 自定义结束符
DELIMITER ;  -- 恢复默认结束符

参数列表格式：

仅包含 “参数名 + 数据类型”（无参数模式，默认均为输入参数），如 (id INT, name VARCHAR(50))。

调用函数

函数可直接嵌入 SQL 语句中，作为表达式的一部分：

SELECT 函数名(参数列表);  -- 单独调用，返回结果
-- 或嵌入查询
SELECT id, 函数名(字段名) AS 计算结果 FROM 表名;

查看函数

-- 查看函数的创建语句
SHOW CREATE FUNCTION 函数名;

-- 查看当前数据库中所有函数
SELECT name FROM mysql.proc WHERE db = '数据库名' AND type = 'FUNCTION';

删除函数

DROP FUNCTION [IF EXISTS] 函数名;  -- 一次删除一个函数

自定义函数示例

示例 1：简单计算函数

创建一个计算两个数之和的函数：

DELIMITER $
CREATE FUNCTION addTwoNum(a INT, b INT) 
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN a + b;  -- 直接返回计算结果
END $
DELIMITER ;

-- 调用
SELECT addTwoNum(3, 5);  -- 结果：8

示例 2：查询转换函数

创建一个根据学生 ID 返回其所属班级名称的函数：

DELIMITER $
CREATE FUNCTION getClassName(stu_id INT) 
RETURNS VARCHAR(50)  -- 返回字符串类型
DETERMINISTIC
BEGIN
  DECLARE class_name VARCHAR(50);  -- 声明局部变量存储结果
  -- 查询班级名称并赋值给变量
  SELECT c.name INTO class_name
  FROM student s
  JOIN class c ON s.class_id = c.id
  WHERE s.id = stu_id;
  RETURN class_name;  -- 返回结果
END $
DELIMITER ;

-- 调用
SELECT getClassName(1);  -- 假设ID=1的学生属于“高三(1)班”，返回该名称
-- 嵌入查询
SELECT id, name, getClassName(id) AS 班级 FROM student;

示例 3：条件判断函数

创建一个根据分数返回等级的函数（90 分以上为 A，60-89 为 B，否则为 C）：

DELIMITER $
CREATE FUNCTION getScoreLevel(score INT) 
RETURNS CHAR(1)
DETERMINISTIC
BEGIN
  DECLARE level CHAR(1);
  IF score >= 90 THEN
    SET level = 'A';
  ELSEIF score >= 60 THEN
    SET level = 'B';
  ELSE
    SET level = 'C';
  END IF;
  RETURN level;
END $
DELIMITER ;

-- 调用
SELECT getScoreLevel(85);  -- 结果：B
SELECT id, score, getScoreLevel(score) AS 等级 FROM exam;

常见问题与解决

1. 创建函数时的二进制日志错误

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

原因：
MySQL 开启二进制日志时，默认要求函数声明确定性（避免主从同步不一致）。

解决方法：

• 方法 1：创建函数时添加 DETERMINISTIC 或 READS SQL DATA 等关键字（如示例中所示）。

• 方法 2：全局设置允许创建非确定性函数（需SUPER权限）：

  SET GLOBAL log_bin_trust_function_creators = 1;

函数与存储过程的区别

特性	自定义函数（FUNCTION）	存储过程（PROCEDURE）
返回值	必须有且仅有一个返回值	可返回 0 个或多个值（通过 OUT 参数）
调用方式	嵌入 SQL 语句（如 SELECT 中）	用 CALL 语句独立调用
事务支持	不支持事务控制（如 COMMIT）	支持事务控制
适用场景	简单计算、数据转换	复杂业务逻辑（多步操作、事务）

使用注意事项

1. 返回值必须存在：函数体中必须包含 RETURN 语句，且确保在所有代码路径中都能执行（如条件判断的每个分支都有 RETURN）。
2. 局部变量声明：局部变量需用 DECLARE 声明，且必须放在函数体的开头（BEGIN 之后）。
3. 权限控制：创建函数需 CREATE ROUTINE 权限，调用需 EXECUTE 权限。
4. 性能影响：避免在函数中编写复杂逻辑或查询（如多表关联、大量数据扫描），否则可能导致查询效率低下。
5. 确定性声明：为保证主从同步安全，建议根据函数特性添加关键字：
   • DETERMINISTIC：函数结果唯一（相同输入必返回相同输出）。
   • READS SQL DATA：函数仅读取数据，不修改。
   • NO SQL：函数不涉及 SQL 操作（如纯计算）