MySQL教程 / 第 120 节

第12章：触发器与事件

自动化数据库操作的利器

12.1 触发器（Trigger）⭐⭐⭐⭐

12.1.1 什么是触发器？

定义：

在特定事件发生时自动执行的SQL语句
事件：INSERT、UPDATE、DELETE
时机：BEFORE、AFTER

语法：

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- SQL语句
END;

12.1.2 触发器示例

BEFORE INSERT触发器：

-- 自动设置创建时间
DELIMITER $$

CREATE TRIGGER tr_users_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.create_time = NOW();
END$$

DELIMITER ;

AFTER INSERT触发器：

-- 插入用户后，记录日志
DELIMITER $$

CREATE TRIGGER tr_users_after_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_logs(user_id, action, create_time)
    VALUES (NEW.id, 'INSERT', NOW());
END$$

DELIMITER ;

BEFORE UPDATE触发器：

-- 更新前记录旧值
DELIMITER $$

CREATE TRIGGER tr_users_before_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.update_time = NOW();
    INSERT INTO user_history(user_id, old_name, new_name, update_time)
    VALUES (OLD.id, OLD.name, NEW.name, NOW());
END$$

DELIMITER ;

AFTER DELETE触发器：

-- 删除用户后，删除相关订单
DELIMITER $$

CREATE TRIGGER tr_users_after_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
    DELETE FROM orders WHERE user_id = OLD.id;
END$$

DELIMITER ;

12.1.3 NEW和OLD

NEW：

INSERT：新插入的行
UPDATE：更新后的行
DELETE：不可用

OLD：

INSERT：不可用
UPDATE：更新前的行
DELETE：被删除的行

12.1.4 触发器管理

查看触发器：

-- 查看所有触发器
SHOW TRIGGERS;

-- 查看指定表的触发器
SHOW TRIGGERS WHERE `Table` = 'users';

-- 查看触发器定义
SHOW CREATE TRIGGER tr_users_before_insert;

删除触发器：

DROP TRIGGER IF EXISTS tr_users_before_insert;

12.1.5 触发器注意事项

限制：

❌ 不能在触发器中调用存储过程返回结果集
❌ 不能使用CALL语句
❌ 不能使用事务控制语句（COMMIT、ROLLBACK）

性能影响：

⚠️ 触发器会影响INSERT、UPDATE、DELETE性能
⚠️ 避免在触发器中执行复杂操作

12.2 事件（Event）⭐⭐⭐⭐

12.2.1 什么是事件？

定义：

定时任务，类似Linux的cron
在指定时间自动执行SQL语句

开启事件调度器：

-- 查看事件调度器状态
SHOW VARIABLES LIKE 'event_scheduler';

-- 开启事件调度器
SET GLOBAL event_scheduler = ON;

-- 配置文件中开启
[mysqld]
event_scheduler = ON

12.2.2 创建事件

一次性事件：

-- 在指定时间执行一次
CREATE EVENT evt_delete_old_logs
ON SCHEDULE AT '2024-12-31 23:59:59'
DO
    DELETE FROM logs WHERE create_time < DATE_SUB(NOW(), INTERVAL 1 YEAR);

周期性事件：

-- 每天凌晨1点执行
CREATE EVENT evt_daily_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 01:00:00'
DO
    DELETE FROM logs WHERE create_time < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 每小时执行
CREATE EVENT evt_hourly_task
ON SCHEDULE EVERY 1 HOUR
DO
    UPDATE statistics SET update_time = NOW();

-- 每5分钟执行
CREATE EVENT evt_5min_task
ON SCHEDULE EVERY 5 MINUTE
DO
    CALL sp_update_cache();

带结束时间的事件：

CREATE EVENT evt_temp_task
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 00:00:00'
ENDS '2024-12-31 23:59:59'
DO
    DELETE FROM temp_data WHERE create_time < DATE_SUB(NOW(), INTERVAL 1 DAY);

12.2.3 事件管理

查看事件：

-- 查看所有事件
SHOW EVENTS;

-- 查看指定数据库的事件
SHOW EVENTS FROM mydb;

-- 查看事件定义
SHOW CREATE EVENT evt_daily_cleanup;

修改事件：

-- 禁用事件
ALTER EVENT evt_daily_cleanup DISABLE;

-- 启用事件
ALTER EVENT evt_daily_cleanup ENABLE;

-- 修改事件
ALTER EVENT evt_daily_cleanup
ON SCHEDULE EVERY 2 DAY;

删除事件：

DROP EVENT IF EXISTS evt_daily_cleanup;

12.2.4 事件实战案例

案例1：定期清理日志

CREATE EVENT evt_cleanup_logs
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 02:00:00'
DO
BEGIN
    -- 删除30天前的日志
    DELETE FROM access_logs WHERE create_time < DATE_SUB(NOW(), INTERVAL 30 DAY);
    DELETE FROM error_logs WHERE create_time < DATE_SUB(NOW(), INTERVAL 30 DAY);
    
    -- 优化表
    OPTIMIZE TABLE access_logs;
    OPTIMIZE TABLE error_logs;
END;

案例2：定期备份数据

CREATE EVENT evt_backup_data
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 03:00:00'
DO
BEGIN
    -- 备份到历史表
    INSERT INTO orders_history
    SELECT * FROM orders 
    WHERE create_time < DATE_SUB(NOW(), INTERVAL 1 YEAR);
    
    -- 删除旧数据
    DELETE FROM orders 
    WHERE create_time < DATE_SUB(NOW(), INTERVAL 1 YEAR);
END;

案例3：定期更新统计数据

CREATE EVENT evt_update_statistics
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    -- 更新用户统计
    UPDATE user_statistics us
    SET 
        order_count = (SELECT COUNT(*) FROM orders WHERE user_id = us.user_id),
        total_amount = (SELECT IFNULL(SUM(amount), 0) FROM orders WHERE user_id = us.user_id),
        update_time = NOW();
END;

12.3 本章总结

本章学习内容：

✅ 触发器（BEFORE/AFTER、INSERT/UPDATE/DELETE）⭐⭐⭐⭐
✅ 事件（一次性、周期性）⭐⭐⭐⭐
✅ NEW和OLD
✅ 实战案例

重点掌握：

触发器：BEFORE/AFTER + INSERT/UPDATE/DELETE
NEW：新数据，OLD：旧数据
事件：定时任务
event_scheduler = ON

触发器时机：

BEFORE：在操作之前执行
AFTER：在操作之后执行

事件调度：

AT：一次性
EVERY：周期性

面试重点：

触发器的作用
NEW和OLD的区别
事件和cron的区别
触发器的性能影响

下一章预告： 分区表

练习题

什么是触发器？
BEFORE和AFTER触发器有什么区别？
NEW和OLD有什么区别？
如何创建一个AFTER INSERT触发器？
什么是事件？
如何开启事件调度器？
如何创建一个每天执行的事件？
触发器有哪些限制？
事件和cron有什么区别？
实战：创建一个触发器和事件

继续学习： 第13章：分区表