MySQL——自定义函数

一、什么是MySQL自定义函数？

MySQL自定义函数（User-Defined Function, UDF） 是一段存储在数据库中的可重用代码块，用于封装特定逻辑的计算或数据转换。它接受输入参数，执行操作，并必须返回一个单一值。通过将复杂逻辑抽象为函数，开发者可以在SQL语句中直接调用，显著提升代码的简洁性和复用性。

核心特性：

封装性：将多步操作合并为一个逻辑单元。
可重用性：在多个查询中重复调用，避免代码冗余。
SQL集成：可在SELECT、WHERE、ORDER BY等子句中直接使用。

二、函数与存储过程的区别：如何选择？

特性	函数（Function）	存储过程（Procedure）
返回值	必须返回单一值	可返回多个结果集或通过OUT参数返回值
调用方式	嵌入SQL语句（如`SELECT func()`）	使用`CALL proc()`独立调用
数据修改	通常只读，不建议修改数据	支持INSERT/UPDATE/DELETE等操作
事务支持	不支持	支持事务控制（COMMIT/ROLLBACK）
参数类型	仅输入参数（IN）	支持IN、OUT、INOUT参数
典型应用场景	数据转换、计算、条件判断	复杂业务逻辑、批量数据处理、事务操作

示例场景对比：

函数适用：计算学生平均分，并在SELECT结果中直接显示。
```
SELECT sid, get_student_avg(sid) AS avg_score FROM students;
```
存储过程适用：批量删除指定学生的所有课程记录，并记录操作日志。
```
CALL delete_student_courses(1001, @result);
```

三、创建函数：语法与实战示例

1. 基础语法

DELIMITER $$
CREATE FUNCTION 函数名(参数 数据类型) 
RETURNS 返回类型
[特性声明]
BEGIN-- 逻辑实现RETURN 结果;
END $$
DELIMITER ;

特性声明（必选其一）：

DETERMINISTIC：相同输入始终返回相同结果（如数学计算）。
READS SQL DATA：仅读取数据，不修改（如查询类函数）。
MODIFIES SQL DATA：会修改数据（不推荐在函数中使用）。

2. 实战示例

示例1：成绩等级判定

DELIMITER $$
CREATE FUNCTION get_grade(score DECIMAL(5,2))
RETURNS VARCHAR(10)
DETERMINISTIC
BEGINRETURN CASEWHEN score >= 90 THEN 'A'WHEN score >= 80 THEN 'B'WHEN score >= 60 THEN 'C'ELSE 'F'END;
END $$
DELIMITER ;-- 调用示例
SELECT sid, score, get_grade(score) AS grade FROM exam_results;

示例2：计算学生年龄

DELIMITER $$
CREATE FUNCTION calculate_age(birth_date DATE)
RETURNS INT
READS SQL DATA
BEGINRETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE());
END $$
DELIMITER ;-- 调用示例
SELECT name, calculate_age(birthday) AS age FROM students;

四、函数管理：查看与维护

1. 查看函数

-- 列出所有函数
SHOW FUNCTION STATUS WHERE Db = DATABASE();-- 查看函数定义
SHOW CREATE FUNCTION calculate_age;

2. 删除函数

DROP FUNCTION IF EXISTS get_grade;

五、高级应用场景

场景1：动态条件过滤

-- 创建函数：检查是否选修某课程
DELIMITER $$
CREATE FUNCTION has_course(student_id INT, course_name VARCHAR(50))
RETURNS BOOLEAN
READS SQL DATA
BEGINDECLARE exists_flag INT;SELECT COUNT(*) INTO exists_flag FROM courses WHERE sid = student_id AND cname = course_name;RETURN exists_flag > 0;
END $$
DELIMITER ;-- 查询选修"数据库"课程的学生
SELECT * FROM students WHERE has_course(sid, '数据库');

场景2：数据格式化

-- 创建函数：格式化手机号
DELIMITER $$
CREATE FUNCTION format_phone(phone VARCHAR(15))
RETURNS VARCHAR(15)
DETERMINISTIC
BEGINRETURN CONCAT(SUBSTR(phone,1,3), '-', SUBSTR(phone,4,4), '-', SUBSTR(phone,8));
END $$
DELIMITER ;-- 调用示例
SELECT name, format_phone(phone) AS formatted_phone FROM users;