开发者空间里玩转MySQL之存储过程与触发器-优快云博客

1 概述

1.1 案例介绍

存储过程和触发器作为MySQL的重要特性，能够实现业务逻辑封装、数据一致性保障和自动化处理，广泛应用于订单处理、数据审计、报表生成等复杂业务场景，是开发者必须掌握的高阶数据库技术。

1.2 适用对象

个人开发者
高校学生

1.3 案例时间

本案例总时长预计40分钟。

1.4 案例流程

{{{width="60%" height="auto"}}}

说明：

领取空间开发桌面；
在空间开发桌面终端进入MySQL；
进行数据库的开发者空间进行MySQL之存储过程与触发器操作；

1.5 资源总览

本案例预计花费总计0元。

资源名称	规格	单价（元）	时长（分钟）
开发者空间 - 空间开发桌面	X86\| 2 vCPUs 4GB \| Ubuntu 22.04 Server 定制版	0	40

2 存储过程和存储函数

存储过程和存储函数是MySQL中强大的工具，可用于封装复杂的业务逻辑，提高代码复用性和执行效率。存储过程适合处理复杂的业务逻辑，而存储函数适合返回单个值的计算或转换。

2.1 存储过程

2.1.1 存储过程概述

存储过程（Stored Procedure）是一组预编译的SQL语句集合，存储在数据库中，可以通过调用来执行。存储过程可以接受输入参数、输出参数，并包含复杂的业务逻辑。它通常用于封装重复性操作，提高代码复用性和执行效率。

适用场景：

需要重复执行的复杂业务逻辑
需要封装业务逻辑以提高代码复用性和安全性
减少网络传输量，提高执行效率（特别是在客户端与服务器交互频繁的场景）

2.1.2 创建存储过程

语法示例：

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE [IF NOT EXISTS] 存储过程名 (
        [IN | OUT | INOUT] 参数名1 数据类型 [(长度)],
        [IN | OUT | INOUT] 参数名2 数据类型 [(长度)],
        ...
    )
    [characteristic ...]
    BEGIN
        -- 存储过程体
        -- 可包含SQL语句、变量声明、流程控制等
    END

语法元素说明：

1、DEFINER（可选）：

用于指定存储过程的创建者/定义者
默认值为当前执行创建语句的用户 (CURRENT_USER)
格式为 'user_name'@'host_name'（需用引号括起来）
需要 SUPER 权限才能设置非当前用户的 DEFINER
影响存储过程的执行权限（与 SQL SECURITY 特性相关）

2、IF NOT EXISTS（可选）：

仅当同名的存储过程不存在时才创建、避免因重复创建导致的错误
与OR REPLACE不同，不会替换现有过程仅在存储过程不存在时创建

3、参数模式:

IN 参数（输入参数）：默认参数模式，调用时必须提供值，在过程内部是只读的相当于按值传递
OUT 参数（输出参数）：用于从存储过程返回值，调用时初始值为 NULL，在过程内部可修改调用后可以获取值
INOUT 参数（输入输出参数）：结合IN和OUT的特性，调用时必须提供初始值，过程可以修改值，调用后可以获取修改后的值

4、characteristic (可选特性):

-- 可选特性
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }

COMMENT：为存储过程添加注释最大长度 64 个字符
LANGUAGE SQL：声明存储过程语言，目前仅支持 SQL通常可以省略
DETERMINISTIC/NOT DETERMINISTIC：DETERMINISTIC相同输入总是产生相同输出（如数学函数），NOT DETERMINISTIC（默认）可能产生不同结果（如包含 NOW() 或 RAND()），影响查询优化和复制
SQL 数据访问特性：

特性	说明
CONTAINS SQL（默认）	包含SQL但不读写数据
NO SQL	不包含SQL语句
READS SQL DATA	只读取数据
MODIFIES SQL DATA	会修改数据

SQL SECURITY：DEFINER（默认）以定义者权限执行，INVOKER以调用者权限执行，影响存储过程的权限检查

语句示例：

-- 添加新学生账户的存储过程
DELIMITER //

CREATE PROCEDURE add_student_account(
    IN p_username VARCHAR(50),
    IN p_real_name VARCHAR(50),
    IN p_password VARCHAR(255),
    IN p_age INT,
    IN p_gender CHAR(1),
    IN p_department VARCHAR(100),
    IN p_class VARCHAR(20),
    IN p_enrollment_date DATE,
    OUT p_result VARCHAR(200)
)
BEGIN
    DECLARE v_user_id VARCHAR(50);
    DECLARE v_student_id VARCHAR(50);
    DECLARE v_role_id TINYINT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result = '创建学生账户失败，请检查输入数据';
    END;

    -- 获取学生角色ID
    SELECT role_id INTO v_role_id FROM `role` WHERE role_name = 'student' LIMIT 1;

    -- 生成用户ID和学号（这里简单使用UUID，实际可根据业务规则调整）
    SET v_user_id = UUID();
    SET v_student_id = CONCAT('STU', DATE_FORMAT(NOW(), '%Y%m'), LPAD(FLOOR(RAND() * 10000), 4, '0'));

    -- 开始事务
    START TRANSACTION;

    -- 插入用户表
    INSERT INTO `user` (user_id, username, real_name, password, role_id)
    VALUES (v_user_id, p_username, p_real_name, p_password, v_role_id);

    -- 插入学生表
    INSERT INTO `student` (student_id, user_id, age, gender, department, class, enrollment_date)
    VALUES (v_student_id, v_user_id, p_age, p_gender, p_department, p_class, p_enrollment_date);

    -- 提交事务
    COMMIT;

    SET p_result = CONCAT('学生账户创建成功，学号: ', v_student_id);
END //

DELIMITER ;

操作结果：

语句示例：

-- 学生选课的存储过程
DELIMITER //

CREATE PROCEDURE enroll_course(
    IN p_student_id VARCHAR(50),
    IN p_course_id VARCHAR(50),
    OUT p_result VARCHAR(200)
)
BEGIN
    DECLARE v_course_capacity INT;
    DECLARE v_current_enrollment INT;
    DECLARE v_semester VARCHAR(20);
    DECLARE v_enrollment_count INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result = '选课失败，请稍后再试';
    END;

    -- 检查课程容量
    SELECT course_capacity, current_enrollment, semester 
    INTO v_course_capacity, v_current_enrollment, v_semester
    FROM course 
    WHERE course_id = p_course_id;

    -- 检查是否已选过该课程
    SELECT COUNT(*) INTO v_enrollment_count
    FROM enrollment
    WHERE student_id = p_student_id AND course_id = p_course_id AND semester = v_semester;

    IF v_enrollment_count > 0 THEN
        SET p_result = '您已经选过该课程，不能重复选择';
    ELSEIF v_current_enrollment >= v_course_capacity THEN
        SET p_result = '该课程已满，无法选课';
    ELSE
        -- 开始事务
        START TRANSACTION;

        -- 插入选课记录
        INSERT INTO enrollment (student_id, course_id, semester)
        VALUES (p_student_id, p_course_id, v_semester);

        -- 更新课程当前选课人数
        UPDATE course 
        SET current_enrollment = current_enrollment + 1
        WHERE course_id = p_course_id;

        -- 提交事务
        COMMIT;

        SET p_result = '选课成功';
    END IF;
END //

DELIMITER ;

操作结果：

注：

DELIMITER语句：

DELIMITER // DELIMITER ;是 MySQL 中用于更改语句分隔符的命令，它们在存储过程、函数、触发器等数据库对象的创建中起着关键作用

基本作用：

临时更改语句分隔符：默认情况下，MySQL使用分号 ; 作为语句结束的分隔符，但在创建存储过程等包含多个语句的数据库对象时，我们需要在过程体内使用分号，这会导致 MySQL 客户端在遇到第一个分号时就认为语句结束了
解决方案：使用 DELIMITER // 将分隔符临时改为 //（可以是其他符号），这样存储过程中的分号就不会被误认为是整个语句的结束，创建完成后用 DELIMITER ; 恢复默认分隔符

实际使用中的注意事项：

错误处理：分层处理错误，精准捕获SQLSTATE，记录技术日志
安全审计：关键操作记录日志表，验证数据归属关系
性能优化：必备索引，大数据分页处理，控制事务粒度
参数规范：输入消毒处理，防御性NULL检查
兼容性：避免版本敏感语法，注明最低支持版本

2.1.3 存储过程体

存储过程体是存储过程的核心部分，包含具体的SQL语句和逻辑控制结构（如IF、CASE、LOOP等）。存储过程体可以包含变量声明、条件判断、循环等。

如2.1.2中的存储过程中的过程体：

示例：

CREATE PROCEDURE add_student_account(
    IN p_username VARCHAR(50),
    IN p_real_name VARCHAR(50),
    IN p_password VARCHAR(255),
    IN p_age INT,
    IN p_gender CHAR(1),
    IN p_department VARCHAR(100),
    IN p_class VARCHAR(20),
    IN p_enrollment_date DATE,
    OUT p_result VARCHAR(200)
)
BEGIN
    DECLARE v_user_id VARCHAR(50);
    DECLARE v_student_id VARCHAR(50);
    DECLARE v_role_id TINYINT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result = '创建学生账户失败，请检查输入数据';
    END;

    -- 获取学生角色ID
    SELECT role_id INTO v_role_id FROM `role` WHERE role_name = 'student' LIMIT 1;

    -- 生成用户ID和学号（这里简单使用UUID，实际可根据业务规则调整）
    SET v_user_id = UUID();
    SET v_student_id = CONCAT('STU', DATE_FORMAT(NOW(), '%Y%m'), LPAD(FLOOR(RAND() * 10000), 4, '0'));

    -- 开始事务
    START TRANSACTION;

    -- 插入用户表
    INSERT INTO `user` (user_id, username, real_name, password, role_id)
    VALUES (v_user_id, p_username, p_real_name, p_password, v_role_id);

    -- 插入学生表
    INSERT INTO `student` (student_id, user_id, age, gender, department, class, enrollment_date)
    VALUES (v_student_id, v_user_id, p_age, p_gender, p_department, p_class, p_enrollment_date);

    -- 提交事务
    COMMIT;

    SET p_result = CONCAT('学生账户创建成功，学号: ', v_student_id);
END //

实际使用中的注意事项：

确保逻辑清晰，避免过度嵌套
使用DECLARE声明变量时，注意变量的作用域

2.1.4 存储过程的调用

语法示例：

-- 无输出参数的存储过程调用
CALL 存储过程名称(输入参数1, 输入参数2, ...);

-- 有输出参数的存储过程调用
CALL 存储过程名称(输入参数1, 输入参数2, ..., @输出参数);
SELECT @输出参数 AS 结果描述;

适用场景：

执行存储过程以完成特定任务

语句示例：

-- 调用1：添加计算机学院学生张三
CALL add_student_account(
    'liantaotao', 
    '廉涛涛', 
    'i^8IlcJD', 
    20, 
    'F', 
    '计算机科学与技术学院', 
    '计算机科学与技术1班', 
    '2021-09-01', 
    @add_result
);
SELECT @add_result AS '添加结果';

-- 调用2：添加文学院学生李四
CALL add_student_account(
    'outaoming', 
    '欧涛明', 
    'HF5av1#Z', 
    19, 
    'M', 
    '生物工程学院', 
    '分子生物技术与基因工程专业1班', 
    '2022-09-01', 
    @add_result
);
SELECT @add_result AS '添加结果';

操作结果：

语句示例：

-- 调用1：学生2330390108选课5012
CALL enroll_course(
    '2330390108', 
    '5012', 
    @enroll_result
);
SELECT @enroll_result AS '选课结果';

-- 调用2：学生2330390108选课2027
CALL enroll_course(
    '2330390108', 
    '2027', 
    @enroll_result
);
SELECT @enroll_result AS '选课结果';

操作结果：

实际使用中的注意事项：

确保传递的参数类型和数量与存储过程定义一致
使用@符号定义会话变量来接收输出参数的值

2.1.5 存储过程的删除

语法示例：

DROP PROCEDURE IF EXISTS [ 存储过程名 ];

适用场景：

删除不再使用的存储过程

语句示例：

DROP PROCEDURE IF EXISTS enroll_course;

操作结果：

实际使用中的注意事项：

删除前确保存储过程不再被其他程序或脚本调用
使用IF EXISTS避免删除不存在的存储过程时出现错误

2.2 存储函数

2.2.1 存储函数概述

存储函数（Stored Function）与存储过程类似，但存储函数必须返回一个值，且可以在SQL语句中直接调用。存储函数通常用于计算或转换数据。

适用场景：

需要返回单个值的计算或转换逻辑
在SQL语句中直接调用函数

2.2.2 存储函数的创建

语法示例：

CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION [IF NOT EXISTS] 函数名 (
        [IN | OUT | INOUT] 参数名1 数据类型 [(长度)],
        [IN | OUT | INOUT] 参数名2 数据类型 [(长度)],
        ...
    )
    RETURNS 返回值数据类型
    [characteristic ...]
    BEGIN
        -- 函数体
        -- 可包含SQL语句、变量声明、流程控制等
        RETURN 返回值;
    END

与存储过程的区别：

必须声明 RETURNS 子句指定返回值类型
函数体内必须包含 RETURN 语句
参数模式通常只用 IN（默认值）

characteristic 常用选项：

[NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT '注释文本'

语句示例：

-- 检查学生是否已选某门课程
DELIMITER //
CREATE FUNCTION check_student_enrollment(
    p_student_id VARCHAR(50),
    p_course_id VARCHAR(50)
) 
RETURNS BOOLEAN
DETERMINISTIC
READS SQL DATA
COMMENT '检查指定学生是否已选修指定课程（返回TRUE/FALSE）'
BEGIN
    DECLARE v_result BOOLEAN DEFAULT FALSE;

    -- 查询选课表中是否存在匹配记录
    SELECT COUNT(*) > 0 INTO v_result 
    FROM enrollment 
    WHERE student_id = p_student_id AND course_id = p_course_id;

    RETURN v_result;
END //
DELIMITER ;

-- 生成学生基本信息报告
DELIMITER //
CREATE FUNCTION generate_student_report(
    p_student_id VARCHAR(50)
)
RETURNS TEXT
DETERMINISTIC
READS SQL DATA
COMMENT '生成学生基本信息汇总报告（包含姓名、院系、班级等）'
BEGIN
    DECLARE v_report TEXT;

    -- 使用CONCAT_WS构建报告文本（自动处理NULL值）
    SELECT CONCAT_WS('\n',
        CONCAT('学生ID: ', s.student_id),
        CONCAT('姓名: ', u.real_name),
        CONCAT('年龄: ', IFNULL(s.age, '未填写')),
        CONCAT('性别: ', CASE s.gender WHEN 'M' THEN '男' WHEN 'F' THEN '女' ELSE '未知' END),
        CONCAT('院系: ', IFNULL(s.department, '未分配')),
        CONCAT('班级: ', IFNULL(s.class, '未分配')),
        CONCAT('入学日期: ', IFNULL(s.enrollment_date, '未知'))
    ) INTO v_report
    FROM student s
    JOIN user u ON s.user_id = u.user_id
    WHERE s.student_id = p_student_id;

    RETURN IFNULL(v_report, '未找到该学生信息');
END //
DELIMITER ;

操作结果：

注意事项：

所有函数都标记为 DETERMINISTIC（确定性函数），适合在MySQL 8+中使用
函数权限设置为 READS SQL DATA，仅读取不修改数据
使用 DELIMITER // 语法确保函数体中的分号被正确解析
实际使用时，请确保参数值与表中的数据格式匹配

2.2.3 调用存储函数

语句示例：

-- 检查学生是否已选课程
SELECT check_student_enrollment('2330390108', '5012') AS is_enrolled;

-- 生成学生报告
SELECT generate_student_report('2330390108') AS student_report;

操作结果：

2.2.4 删除存储函数

语法示例：

DROP FUNCTION IF EXISTS [ function_name ];

语句示例：

DROP FUNCTION IF EXISTS check_student_enrollment;

适用场景：

删除不再使用的存储函数

实际使用中的注意事项：

删除前确保函数不再被其他程序或脚本调用
使用IF EXISTS避免删除不存在的函数时出现错误

3 触发器与事件

3.1 触发器

3.1.1 触发器概述

触发器是数据库中的一种特殊存储过程，在指定表发生INSERT/UPDATE/DELETE操作时自动执行。

核心特性：

基于事件驱动（BEFORE/AFTER + 操作类型）
可以访问操作前后的数据（通过NEW/OLD伪记录）
常用于数据校验、审计日志、业务规则维护

适用场景：
数据完整性校验
自动填充字段
级联业务操作

3.1.2 创建触发器

语法示例：

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER [IF NOT EXISTS] 触发器名称
    { BEFORE | AFTER } { INSERT | UPDATE | DELETE }
    ON 表名 FOR EACH ROW
    [trigger_order]
    [characteristic ...]
    BEGIN
        -- 触发器体
        -- 可包含SQL语句、变量声明、流程控制等
        -- 通过NEW和OLD访问行数据
    END

参数说明

1、DEFINER 子句（可选）（DEFINER = { user | CURRENT_USER }）：指定触发器创建者，默认为当前用户，需要SUPER权限才能指定其他用户

2、触发时机与事件（{BEFORE | AFTER} {INSERT | UPDATE | DELETE}）：BEFORE在操作执行前触发，AFTER在操作执行后触发，可组合6种触发场景（BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE、BEFORE DELETE、AFTER DELETE）

3、表与行绑定（ON 表名 FOR EACH ROW）：必须指定关联的表名，FOR EACH ROW表示行级触发器（MySQL仅支持行级）

4、执行顺序（[FOLLOWS | PRECEDES] 其他触发器名）：控制同一表的同类触发器执行顺序（例如：FOLLOWS trig1表示在本触发器前执行trig1）

5、特性说明（可选）（[characteristic ...]）

可包含以下特性：

COMMENT '字符串'：添加注释
LANGUAGE SQL：默认值，表示用SQL编写
[NOT] DETERMINISTIC：是否确定性（通常省略）

6、NEW和OLD伪记录说明

操作类型	NEW 可用性	OLD 可用性	说明
INSERT	可用	不可用	NEW 包含要插入的新行数据
UPDATE	可用	可用	NEW 是要更新的数据，OLD 是原始数据
DELETE	不可用	可用	OLD 包含要删除的原始数据

语句示例：

-- 选课人数自动更新触发器（AFTER INSERT）
DELIMITER //
CREATE TRIGGER update_course_enrollment
AFTER INSERT ON enrollment
FOR EACH ROW
BEGIN
    /*
     * 功能：当有学生选课时自动更新课程当前人数
     * 触发时机：插入选课记录后
     * 注意：不考虑并发问题（实际生产环境需要更严谨的处理）
     */

    UPDATE course 
    SET current_enrollment = current_enrollment + 1
    WHERE course_id = NEW.course_id;
END //
DELIMITER ;

操作结果：

语句示例：

-- 学生选课容量检查（BEFORE INSERT）
DELIMITER //
CREATE TRIGGER check_course_capacity
BEFORE INSERT ON `enrollment`
FOR EACH ROW
BEGIN
    /*
     * 功能：检查课程是否已满
     * 触发时机：插入选课记录前
     * 规则：当前选课人数不能超过课程容量
     */

    DECLARE capacity INT;
    DECLARE enrolled INT;

    SELECT course_capacity, current_enrollment 
    INTO capacity, enrolled
    FROM `course` 
    WHERE course_id = NEW.course_id;

    IF enrolled >= capacity THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '课程人数已满，无法选课';
    END IF;
END //
DELIMITER ;

操作结果：

注意事项：

避免递归触发（触发器内操作触发相同触发器）
单个表同类触发器执行顺序不固定（MySQL 8+按创建顺序执行）
权限要求：需要表的TRIGGER权限，触发器内操作需要相应SQL权限
限制：不能在触发器中使用CALL调用存储过程，不能显式或隐式开始/结束事务，不支持LOAD DATA语句触发
性能影响：行级触发器对批量操作性能影响显著，复杂触发器逻辑会增加锁持有时间

3.1.3 查看触发器

查看触发器主要有以下几种方式，每种方式适用于不同的场景：

1、使用 SHOW TRIGGERS 命令

基本语法：

SHOW TRIGGERS 
[FROM database_name] 
[LIKE 'pattern' | WHERE condition];

示例：

-- 查看当前数据库所有触发器
SHOW TRIGGERS;

-- 查看特定数据库的触发器
SHOW TRIGGERS FROM university;

-- 使用LIKE模糊匹配
SHOW TRIGGERS LIKE 'check_%';

-- 使用WHERE条件筛选
SHOW TRIGGERS WHERE `Table` = 'student';

2、使用 SHOW CREATE TRIGGER 命令

基本语法：

SHOW CREATE TRIGGER [database_name.]trigger_name;

示例：

-- 查看特定触发器的完整定义
SHOW CREATE TRIGGER check_course_capacity;

3、查询 information_schema.TRIGGERS 系统表

基本语法：

SELECT * FROM information_schema.TRIGGERS
[WHERE TRIGGER_SCHEMA = 'database_name']
[AND TRIGGER_NAME LIKE 'pattern'];

常用查询示例：

-- 查看所有触发器详细信息
SELECT * FROM information_schema.TRIGGERS;

-- 查看特定数据库的触发器
SELECT * FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'university';

-- 查看特定表的触发器
SELECT * FROM information_schema.TRIGGERS
WHERE EVENT_OBJECT_TABLE = 'enrollment';

各方法对比：

方法	优点	缺点	适用场景
SHOW TRIGGERS	简单直观	信息有限	快速查看触发器列表
SHOW CREATE TRIGGER	显示完整定义	只能查单个触发器	获取创建语句
information_schema	灵活查询	语法较复杂	需要筛选或分析时

3.1.4 使用触发器

语句示例：

-- 1. 测试选课成功情况
INSERT INTO `enrollment` (
    `enrollment_id`, `student_id`, `course_id`, `semester`, `grade`, `enrollment_date`
) VALUES 
('2500009999', '2330350107', '3007', '2025 Spring', 80, '2025-04-01 15:06:11');  
-- 2. 成功，人数变为45
SELECT course_id, course_name, current_enrollment, course_capacity 
FROM course WHERE course_id = '3007';

-- 3. 测试选课失败情况（课程已满）
INSERT INTO `enrollment` (
    `enrollment_id`, `student_id`, `course_id`, `semester`, `grade`, `enrollment_date`
) VALUES 
('2500009998', '2240200202', '3007', '2025 Spring', 70, '2025-04-02 15:06:11');  -- 失败，显示错误信息

操作结果：

性能影响：

每个行操作都会触发，高频操作表慎用
复杂逻辑可能显著降低DML性能

3.1.5 删除触发器

语法示例：

DROP TRIGGER [IF EXISTS] schema_name.trigger_name;

语句示例：

-- 删除已存在的触发器（如果存在）
DROP TRIGGER IF EXISTS update_course_enrollment;
DROP TRIGGER IF EXISTS check_course_capacity;

注意事项：

删除前建议检查是否存在（使用IF EXISTS）
删除后不会影响已触发的操作

3.2 事件

3.2.1 事件概述

事件是MySQL内置的任务调度器，可定期执行SQL语句。

关键特性：

基于时间调度（一次性或周期性）
独立于客户端连接运行
需要事件调度器开启（event_scheduler=ON）

适用场景：

定期数据清理（如日志表）
夜间统计报表生成
数据库维护任务（索引重建等）

3.2.2 创建事件

语法示例：

-- 1. 创建永久表存储已满课程报告（如果不存在）
CREATE TABLE IF NOT EXISTS full_courses_reports (
    course_id VARCHAR(50) NOT NULL COMMENT '课程ID',
    course_name VARCHAR(100) NOT NULL COMMENT '课程名称',
    teacher_id VARCHAR(50) NOT NULL COMMENT '教师ID',
    teacher_name VARCHAR(50) NOT NULL COMMENT '教师姓名',
    department VARCHAR(100) COMMENT '所属院系',
    semester VARCHAR(20) COMMENT '开课学期',
    college_name VARCHAR(50) COMMENT '所属学院',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
    INDEX idx_course (course_id),
    INDEX idx_teacher (teacher_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='已满课程统计报告永久表';

-- 2. 确保事件调度器已启用
SET GLOBAL event_scheduler = ON;

-- 3. 创建事件（5分钟后执行）
DELIMITER //
CREATE EVENT `report_full_courses`
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 MINUTE
ON COMPLETION PRESERVE  -- 执行后保留事件
COMMENT '统计当前已满课程（课程余量为0）并存入永久表'
DO
BEGIN

    -- 清除历史数据
    TRUNCATE TABLE full_courses_reports;

    -- 插入统计结果到永久表
    INSERT INTO full_courses_reports (
        course_id,
        course_name,
        teacher_id,
        teacher_name,
        department,
        semester,
        college_name
    )
    SELECT 
        c.course_id,
        c.course_name,
        t.teacher_id,
        u.real_name AS teacher_name,
        t.department,
        c.semester,
        c.college_name
    FROM 
        course c
    JOIN 
        teacher t ON c.teacher_id = t.teacher_id
    JOIN 
        user u ON t.user_id = u.user_id
    WHERE 
        c.current_enrollment >= c.course_capacity  -- 已满课程
        OR c.course_capacity = 0;  -- 或者容量为0的课程
END //
DELIMITER ;

-- 4. 查看事件状态
SHOW EVENTS LIKE 'report_full_courses'\G

-- 5. 查询历史报告（示例查询）
-- SELECT * FROM full_courses_reports;

操作结果：

注意事项：

需要EVENT权限
默认不启用事件调度器（需设置SET GLOBAL event_scheduler=ON）
事件执行失败会记录到错误日志

3.2.3 修改事件

语法示例：

ALTER EVENT [IF EXISTS] event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    [DO event_body]

语句示例：

-- 1. 首先检查事件当前状态
SHOW EVENTS LIKE 'report_full_courses';

-- 2. 使用ALTER EVENT修改执行周期
ALTER EVENT `report_full_courses`
ON SCHEDULE
    EVERY 1 WEEK
    STARTS 
        -- 计算下一个周五17:30的时间
        TIMESTAMP(
            CURRENT_DATE + INTERVAL 
            (5 - IF(WEEKDAY(CURRENT_DATE) <= 5, WEEKDAY(CURRENT_DATE), WEEKDAY(CURRENT_DATE)-7)) DAY
        ) + INTERVAL 17 HOUR + INTERVAL 30 MINUTE
COMMENT '每周五17:30统计当前已满课程（课程余量为0）并存入永久表';

-- 3. 验证修改结果
SHOW EVENTS LIKE 'report_full_courses'\G

操作结果：

注意事项：

必须具有 ALTER EVENT 权限才能执行此操作
使用 IF EXISTS 可以避免事件不存在时的错误
可以组合多个修改项，如同时修改时间和事件体
修改后的事件会保持原有状态（启用/禁用）除非显式指定
在MySQL 8.0+中，事件元数据存储在数据字典中，修改会立即生效

3.2.4 删除事件

语句示例：

DROP EVENT IF EXISTS weekly_enrollment_report;

注意事项：

删除前建议禁用事件（ALTER EVENT ... DISABLE）
建议保留创建脚本

3.3 综合对比

特性	触发器	事件
触发条件	DML操作	时间调度
执行频率	每次行操作	按计划间隔
典型用途	数据校验/业务规则	定期维护/批量处理
资源消耗	增加DML开销	独立后台进程
可见性	关联到具体表	全局可见