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权限）：
1
SET GLOBAL log_bin_trust_function_creators = 1;

函数与存储过程的区别

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

使用注意事项

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