MySQL自定义函数

最新推荐文章于 2025-07-04 18:16:34 发布
原创 最新推荐文章于 2025-07-04 18:16:34 发布 · 614 阅读 · 14 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库

MySQL函数介绍

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

函数的基本特点

  • 封装性：将多个SQL操作封装为一个单元

  • 重用性：能在多个SQL语句中重复调用

  • 简化操作：使复杂的操作变得简单

  • 返回单一值：必须且只能返回一个值

  • 可在SQL表达式中使用：可以直接在SELECT语句中使用

函数与存储过程的区别

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

特性函数(Function)存储过程(Procedure)
返回值必须返回一个单一值可以返回多个值，也可以不返回值
调用方式可以在SQL语句中直接调用使用CALL语句单独调用
使用场景计算和数据转换执行复杂的业务逻辑和事务
参数类型只支持输入参数(IN)支持输入(IN)、输出(OUT)和输入输出(INOUT)参数
事务操作通常不在函数中进行事务操作经常包含事务处理
数据修改一般不建议在函数中修改数据可以执行数据修改操作

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

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) = '有不及格';

函数管理

查看函数

-- 查看当前数据库中所有函数
SHOW FUNCTION STATUS WHERE Db = DATABASE();
​
-- 查看特定函数的创建语句
SHOW CREATE FUNCTION function_name;

删除函数

DROP FUNCTION IF EXISTS function_name;

函数的局限性

  1. 无法执行事务：

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

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

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

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

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

  3. 不能返回结果集：

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

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

饼干ovo
关注
MySQL 自定义函数
遇见1995
08-12 780
自定义函数 用户自定义函数（user-defined function,UDF）是一种对MySQL扩展的途径，其用法与内置函数相同。   语句结构 CREATE FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL} routine_body 函数体（routine_body) 由合法的SQL语句构成 可以是简...
MySQL 自定义函数CREATE FUNCTION示例
12-15
mysql> mysql> delimiter $$ mysql> mysql> CREATE FUNCTION myFunction -> (in_string VARCHAR(255), -> in_find_str VARCHAR(20), -> in_repl_str VARCHAR(20)) -> -> RETURNS VARCHAR(255) -> BEGIN -> DECLARE l_new_string VARCHAR(255); -> DECLARE l_find_pos INT; -> -> SET l_find_pos=INSTR(in_string,in_find_st
mysql之自定义函数
dianchougao5936的博客
04-18 497
本文内容： 什么是函数 函数的创建 函数的调用 函数的查看 函数的修改 函数的删除 首发日期：2018-04-18 什么是函数： 函数存储着一系列sql语句，调用函数就是一次性执行这些语句。所以函数可以降低语句重复。【但注意的是函数注重返回值，不注重执行过程，所以一些语句无法执行。所以函数并不是单纯的sql语句集合。】 mysql...
MySQL函数大全（收藏）
最新发布
qq_39980997的博客
07-04 1041
MySQL常用函数摘要 本文总结了MySQL中常用的数值和字符串处理函数。数值处理函数包括基本运算(ABS、MOD、POW)、四舍五入(ROUND、CEIL)、三角函数(SIN、COS)及进制转换(BIN、HEX)等。字符串函数包含连接(CONCAT、CONCAT_WS)、截取(SUBSTRING、LEFT)、转换(UPPER、LOWER)、查找(LOCATE)和格式化(FORMAT)等功能。这些函数适用于所有MySQL版本，为数据计算和文本处理提供了便捷的操作方式。函数使用需注意参数类型和返回值格式
mysql-自定义函数
bug生成师
09-25 333
自定义函数 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 655
自定义函数 用户自定义函数（user-defined function,UDF）是一种对MySQL扩展的途径，其用法与内置函数相同。 函数可以返回任意类型的值，同样可以接收任意类型的值。接收和返回两者没有必然关系。 2个必要条件 1.参数 2.返回值 创建自定义函数 CREATE FUNCTION 函数名（参数名1 类型1，参数名2 类型2，…） RETURNS 返回类型 {STRI
MySQL自定义函数简单用法示例
12-15
MySQL自定义函数是数据库系统提供的一种扩展功能，允许用户根据需求创建自己的函数，以实现特定的数据处理或转换。本文将通过实例介绍如何在MySQL中创建和使用自定义函数，包括基本用法、分支结构和循环结构。 首先...
MYSQL自定义函数判断是否正整数的实例代码
09-09
本文将详细介绍如何创建一个MySQL自定义函数，用于判断输入的值是否为非负整数，即正整数或零。 首先，我们来理解自定义函数的基本概念。在MySQL中，自定义函数允许用户根据需求创建自己的函数，这些函数可以在SQL...
MySQL自定义函数和存储过程示例详解
01-19
本文主要给大家介绍的是关于MySQL自定义函数和存储过程的相关内容，分享出来供大家参考学习，下面话不多说了，来一起看看详细的介绍吧 1、前置条件 MySQL数据库中存在表user_info，其结构和数据如下： mysql> desc...
MySQL自定义函数用法详解-复合结构自定义变量/流程控制
weixin_30790841的博客
05-07 1968
自定义函数 (user-defined function UDF)就是用一个象ABS() 或 CONCAT()这样的固有（内建）函数一样作用的新函数去扩展MySQL。 所以UDF是对MySQL功能的一个扩展 创建和删除自定义函数语法: 创建UDF: 　　CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parame...
Mysql 复习笔记- 基础篇12 [函数定义概述]
qq_45205390的博客
11-23 1660
MySQL不仅内置了一些常用函数，同时也支持自定义函数，来满足开发者的需求 函数定义 MySQL的函数定义语法如下： CREATE [DEFINER = { user | CURRENT_USER }] FUNCTION functionName ( varName varType [, ... ] ) RETURNS returnVarType [characteristic ...] routine_body
MySQL自定义函数（CREATE FUNCTION）
fishermanMr
11-26 1387
介绍 在使用 MySQL 的过程中，MySQL 自带的函数可能完成不了我们的业务需求，这时候就需要自定义函数。 自定义函数是一种与存储过程十分相似的过程式数据库对象。它与存储过程一样，都是由 SQL 语句和过程式语句组成的代码片段，并且可以被应用程序和其他 SQL 语句调用。 自定义函数与存储过程之间存在几点区别： 自定义函数不能拥有输出参数，这是因为自定义函数自身就是输出参数；而存储过程可以拥...
mysql 自定义函数
猎人在吃肉
09-23 1415
下面的自定义函数 中， 注意delimiter 的使用， 使用了  group_concat()、find_in_set()  函数， delimiter $$ use `test`$$ drop function if exists `queryOnePartnerIdById`$$ create definer=`root`@`localhost` function `queryO
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值