MySQL自定义函数

于 2025-04-16 15:19:15 发布
阅读量297 收藏 6
点赞数 4
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/yyz13862243/article/details/147231819
版权

一：MySQL函数介绍

MySQL自定义函数(Function)是一组预编译的SQL语句集合，它们被存储在数据库中并可重复使用。函数接受输入参数，执行特定操作，并且必须返回一个单一值。

函数的基本特点

- 封装性：将多个SQL操作封装为一个单元
- 重用性：能在多个SQL语句中重复调用
- 简化操作：使复杂的操作变得简单
- 返回单一值：必须且只能返回一个值
- 可在SQL表达式中使用：可以直接在SELECT语句中使用

二：函数与存储过程的区别

函数和存储过程都是MySQL中的程序化对象，但它们在用途、语法和行为上有明显区别。

 需要获取学生的平均分数：

DELIMITER $$
CREATE PROCEDURE get_student_avg_proc(IN student_id INT, OUT avg_score DECIMAL(5, 2))
BEGIN
    SELECT AVG(score)
    INTO avg_score
    FROM t_score
    WHERE sid = student_id;

    SET avg_score = IFNULL(avg_score, 0);
END $$
DELIMITER ;

-- 调用方式：
SET @avg = 0;
CALL get_student_avg_proc(1001, @avg);
SELECT @avg AS 平均分;
DELIMITER $$
CREATE FUNCTION get_student_avg(student_id INT)
    RETURNS DECIMAL(5, 2)
    READS SQL DATA
BEGIN
    DECLARE avg_score DECIMAL(5, 2);

    SELECT AVG(score)
    INTO avg_score
    FROM t_score
    WHERE sid = student_id;

    RETURN IFNULL(avg_score, 0);
END $$
DELIMITER ;

-- 调用方式：
SELECT sid, sname, get_student_avg(sid) AS 平均分
FROM t_student;

使用函数的场景：

  • 需要在SELECT语句中使用计算结果

  • 需要返回单一值的数据转换或计算

  • 查询操作，不涉及数据修改

  • 需要在WHERE或ORDER BY子句中使用

使用存储过程的场景：

  • 执行包含多个步骤的复杂业务逻辑

  • 需要返回多个结果或使用结果集

  • 需要执行数据修改操作(INSERT/UPDATE/DELETE)

  • 需要进行事务处理

  • 需要错误处理和异常捕获

三：MySQL函数创建语法

基本语法结构

DELIMITER $$
CREATE FUNCTION function_name([parameter ,...])
RETURNS return_datatype
BEGIN
    -- 声明变量
    DECLARE variable_name datatype [DEFAULT value];

    -- 函数体 - SQL语句

    -- 返回值
RETURN value;
END $$
DELIMITER ;

创建一个存储函数时，MySQL 要求你明确声明该函数的特性之一 ==如果开了binlog==：

  1. DETERMINISTIC：函数对于相同的输入总是返回相同的结果。

  2. NO SQL：函数不包含任何 SQL 语句。

  3. READS SQL DATA：函数只读取数据，但不修改数据。

  4. MODIFIES SQL DATA：函数会修改数据。

计算相差天数

DELIMITER $$
CREATE FUNCTION calculate_age(birth_date DATE)
    RETURNS INT
    DETERMINISTIC
BEGIN
    RETURN datediff(CURDATE(), birth_date);
END $$
DELIMITER ;

 根据学生ID获取性别

 

DELIMITER $$
CREATE FUNCTION get_gender_desc(student_id INT)
RETURNS VARCHAR(10)
READS SQL DATA
BEGIN
    DECLARE gender CHAR(1);
    
    -- 获取学生性别
    SELECT ssex INTO gender
    FROM t_student
    WHERE sid = student_id;
    
    -- 返回性别描述
    IF gender = '男' THEN
        RETURN '男性';
    ELSEIF gender = '女' THEN
        RETURN '女性';
    ELSE
        RETURN '未知';
    END IF;
END $$
DELIMITER ;
-- 查询单个学生性别
SELECT sid, sname, get_gender_desc(sid) AS 性别
FROM t_student
WHERE sid = 1001;

-- 在完整查询中使用
SELECT sid, sname, sage, get_gender_desc(sid) AS 性别
FROM t_student
ORDER BY sid;

判断成绩是否及格

DELIMITER $$
CREATE FUNCTION is_pass(score DECIMAL(5, 2))
    RETURNS VARCHAR(10)
    deterministic
BEGIN
    IF score >= 60 THEN
        RETURN '及格';
    ELSE
        RETURN '不及格';
    END IF;
END $$
DELIMITER ;
-- 查看所有成绩是否及格
SELECT sid, cid, score, is_pass(score) AS 是否及格
FROM t_score;

-- 结合WHERE条件使用
SELECT 
    s.sid, 
    st.sname, 
    s.cid, 
    c.cname, 
    s.score, 
    is_pass(s.score) AS 是否及格
FROM t_score s
JOIN t_student st ON s.sid = st.sid
JOIN t_course c ON s.cid = c.cid
WHERE is_pass(s.score) = '不及格';

计算学生年龄

DELIMITER $$
CREATE FUNCTION calculate_student_age(student_id INT)
    RETURNS INT
    READS SQL DATA
BEGIN
    DECLARE birth_date DATE;

    -- 获取学生出生日期
    SELECT sage
    INTO birth_date
    FROM t_student
    WHERE sid = student_id;

    -- 计算年龄
    RETURN TIMESTAMPDIFF(YEAR, birth_date, CURDATE());
END $$
DELIMITER ;
-- 查询所有学生的年龄
SELECT 
    sid, 
    sname, 
    sage AS 出生日期, 
    calculate_student_age(sid) AS 年龄
FROM t_student
ORDER BY 年龄 DESC;

获取教师姓名

DELIMITER $$
CREATE FUNCTION get_teacher_name(teacher_id INT)
    RETURNS VARCHAR(50)
    READS SQL DATA
BEGIN
    DECLARE teacher_name VARCHAR(50);

    SELECT tname
    INTO teacher_name
    FROM t_teacher
    WHERE tid = teacher_id;

    RETURN IFNULL(teacher_name, '未知教师');
END $$
DELIMITER ;
-- 查询课程对应的教师
SELECT 
    cid, 
    cname, 
    tid AS 教师ID, 
    get_teacher_name(tid) AS 教师姓名
FROM t_course;

计算学生的平均成绩

DELIMITER $$
CREATE FUNCTION get_student_avg_score(student_id INT)
    RETURNS DECIMAL(5, 2)
    READS SQL DATA
BEGIN
    DECLARE avg_score DECIMAL(5, 2);

    SELECT AVG(score)
    INTO avg_score
    FROM t_score
    WHERE sid = student_id;

    RETURN IFNULL(avg_score, 0);
END $$
DELIMITER ;
-- 查询指定学生的平均分
SELECT 
    sid, 
    sname, 
    get_student_avg_score(sid) AS 平均分
FROM t_student
WHERE sid = 1001;

-- 按平均分对所有学生进行排名
SELECT 
    sid, 
    sname, 
    get_student_avg_score(sid) AS 平均分
FROM t_student
ORDER BY 平均分 DESC;

统计学生选修的课程数量

DELIMITER $$
CREATE FUNCTION count_student_courses(student_id INT)
    RETURNS INT
    READS SQL DATA
BEGIN
    DECLARE course_count INT;

    SELECT COUNT(*)
    INTO course_count
    FROM t_score
    WHERE sid = student_id;
-- 查询所有学生选修的课程数量
SELECT 
    sid, 
    sname, 
    count_student_courses(sid) AS 选修课程数
FROM t_student
ORDER BY 选修课程数 DESC;

-- 筛选选修课程数大于2的学生
SELECT 
    sid, 
    sname, 
    count_student_courses(sid) AS 选修课程数
FROM t_student
WHERE count_student_courses(sid) > 2;

获取课程的最高分

DELIMITER $$
CREATE FUNCTION get_course_max_score(course_id INT)
    RETURNS DECIMAL(5, 2)
    READS SQL DATA
BEGIN
    DECLARE max_score DECIMAL(5, 2);

    SELECT MAX(score)
    INTO max_score
    FROM t_score
    WHERE cid = course_id;

    RETURN IFNULL(max_score, 0);
END $$
DELIMITER ;
-- 查询所有课程的最高分
SELECT 
    c.cid, 
    c.cname, 
    get_course_max_score(c.cid) AS 最高分
FROM t_course c
ORDER BY 最高分 DESC;

学生成绩等级评定函数

DELIMITER $$
CREATE FUNCTION get_score_grade(score DECIMAL(5, 2))
    RETURNS CHAR(1)
    DETERMINISTIC
BEGIN
    DECLARE grade CHAR(1);

    CASE
        WHEN score >= 90 THEN SET grade = 'A';
        WHEN score >= 80 THEN SET grade = 'B';
        WHEN score >= 70 THEN SET grade = 'C';
        WHEN score >= 60 THEN SET grade = 'D';
        ELSE SET grade = 'F';
        END CASE;

    RETURN grade;
END $$
DELIMITER ;
-- 为所有成绩评定等级
SELECT 
    s.sid, 
    st.sname, 
    c.cname,
    s.score, 
    get_score_grade(s.score) AS 等级
FROM t_score s
JOIN t_student st ON s.sid = st.sid
JOIN t_course c ON s.cid = c.cid
ORDER BY s.sid, s.cid;

-- 统计各等级人数
SELECT 
    get_score_grade(score) AS 等级, 
    COUNT(*) AS 人数
FROM t_score
GROUP BY 等级
ORDER BY 等级;

检查学生是否通过所有考试

DELIMITER $$
CREATE FUNCTION has_failed_courses(student_id INT)
RETURNS VARCHAR(10)
READS SQL DATA
BEGIN
    DECLARE fail_count INT;
    
    SELECT COUNT(*) INTO fail_count
    FROM t_score
    WHERE sid = student_id AND score < 60;
    
    IF fail_count > 0 THEN
        RETURN '有不及格';
    ELSE
        RETURN '全部及格';
    END IF;
END $$
DELIMITER ;
-- 查询所有学生的及格情况
SELECT 
    s.sid, 
    s.sname, 
    has_failed_courses(s.sid) AS 及格情况
FROM t_student s
ORDER BY s.sid;

-- 筛选出有不及格科目的学生
SELECT 
    s.sid, 
    s.sname, 
    has_failed_courses(s.sid) AS 及格情况
FROM t_student s
WHERE has_failed_courses(s.sid) = '有不及格';

四：函数管理

（1）：查看函数

-- 查看当前数据库中所有函数
SHOW FUNCTION STATUS WHERE Db = DATABASE();

-- 查看特定函数的创建语句
SHOW CREATE FUNCTION function_name;

（2）：删除函数

DROP FUNCTION IF EXISTS function_name;

（3）：函数的局限性

  1. 无法执行事务：

    • 函数内不能包含事务控制语句(START TRANSACTION, COMMIT, ROLLBACK)

    • 复杂的事务处理应使用存储过程

  2. 不建议在函数中修改数据：

    • MySQL允许在函数中修改数据，但不推荐这样做

    • 修改数据的操作应放在存储过程中

  3. 不能返回结果集：

    • 函数只能返回单一值，不能返回多行结果

    • 需要返回结果集的操作应使用存储过程

烟雨书信
关注
Mysql 复习笔记- 基础篇12 [函数定义概述]
qq_45205390的博客
11-23 1621
MySQL不仅内置了一些常用函数，同时也支持自定义函数，来满足开发者的需求 函数定义 MySQL的函数定义语法如下： CREATE [DEFINER = { user | CURRENT_USER }] FUNCTION functionName ( varName varType [, ... ] ) RETURNS returnVarType [characteristic ...] routine_body
MySQL 中创建函数（用户自定义函数）
最新发布
wsh_xx的博客
02-14 544
在 MySQL 中，函数（Function）是一种存储程序，它可以接收参数并返回一个值。函数可以用于简化复杂的 SQL 查询、封装业务逻辑以及提高代码的可重用性。本文将详细介绍如何在 MySQL 中创建和使用函数。
mysql之自定义函数
dianchougao5936的博客
04-18 481
本文内容： 什么是函数 函数的创建 函数的调用 函数的查看 函数的修改 函数的删除 首发日期：2018-04-18 什么是函数： 函数存储着一系列sql语句，调用函数就是一次性执行这些语句。所以函数可以降低语句重复。【但注意的是函数注重返回值，不注重执行过程，所以一些语句无法执行。所以函数并不是单纯的sql语句集合。】 mysql...
MySQL自定义函数简单用法示例
12-15
MySQL自定义函数是数据库系统提供的一种扩展功能，允许用户根据需求创建自己的函数，以实现特定的数据处理或转换。本文将通过实例介绍如何在MySQL中创建和使用自定义函数，包括基本用法、分支结构和循环结构。 首先...
MYSQL自定义函数判断是否正整数的实例代码
09-09
本文将详细介绍如何创建一个MySQL自定义函数，用于判断输入的值是否为非负整数，即正整数或零。 首先，我们来理解自定义函数的基本概念。在MySQL中，自定义函数允许用户根据需求创建自己的函数，这些函数可以在SQL...
MySQL自定义函数和存储过程示例详解
01-19
本文主要给大家介绍的是关于MySQL自定义函数和存储过程的相关内容，分享出来供大家参考学习，下面话不多说了，来一起看看详细的介绍吧 1、前置条件 MySQL数据库中存在表user_info，其结构和数据如下： mysql> desc...
mysql function
经营IT
07-13 700
写示例的意义在于熟悉，下次需要的使用的可以快速查询，已经包含大部分语法，个人觉得可以解决80%的类似问题 DELIMITER $$ DROP FUNCTION IF EXISTS qStoreIdByUser CREATE `qStoreIdByUser`(userId INTEGER) RETURNS VARCHAR(4000) CHARSET utf8 COLLATE utf8_
MySQL的自定义函数
weixin_44775434的博客
11-23 8414
MySQL的自定义函数
Mysql_function函数介绍
热门推荐
YanQiu_Only
02-19 1万+
#函数 function         含义：一组预先编译好的SQL语句的集合，理解成批处理语句             1.提高代码的重用性             2.简化操作             3.减少了编译次数并且减少了和数据服务器的连接次数，提高了效率         区别：         存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新   ...
mysql常用函数
dubdh的博客
07-20 394
mysql常用函数0. 函数定义一、常用函数二、聚合函数(SUM/COUNT/AVG/MAX/MIN)标题三、合并(union) 0. 函数定义 1.类似Java定义方法 2.可以带参数 3.必须有返回值(一行一列) ，重点：java方法可以没有返回值，数据库函数必须要有返回值 select max(score) from sc; 4.函数可以嵌套到sql语句中 注：存储过程则不可以 5.函数可嵌套调用 一、常用函数 1.日期函数 NOW()：获取当前日期和时间 select now() D
mysql FUNCTION
atares0223的博客
12-02 554
DROP FUNCTION IF EXISTS fn_classes_teacher_num; CREATE FUNCTION fn_classes_teacher_num(id_in varchar(20)) RETURNS INTEGER READS SQL DATA  BEGIN RETURN   (SELECT num_of_teacher from classes where
16. Mysql 自定义函数
小发菜
12-30 2929
Mysql 自定义函数，介绍自定义函数的基本用法，并且配备精选的示例，让大家更熟悉并且用起来。
mysql-自定义函数
bug生成师
09-25 317
自定义函数 mysql内置函数，例如min() max() avg() count() abs() now() date() 为了满足开发中的需要，可以自定义函数，来完成某种特定的功能 语法： 自定义不带参数的函数 #自定义结束符 DELIMITER // # 定义函数 CREATE FUNCTION 自定义函数名() //返回值类型 这里必须写mysql的数据类型 RETURNS VARCHAR(30) BEGIN # 开始标志 # 函数体 RETURN CONCAT('li','si'); END /
MYSQL——自定义函数
bang3的博客
11-25 626
自定义函数 用户自定义函数（user-defined function,UDF）是一种对MySQL扩展的途径，其用法与内置函数相同。 函数可以返回任意类型的值，同样可以接收任意类型的值。接收和返回两者没有必然关系。 2个必要条件 1.参数 2.返回值 创建自定义函数 CREATE FUNCTION 函数名（参数名1 类型1，参数名2 类型2，…） RETURNS 返回类型 {STRI
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值