数据库概述
MySQL安装与配置
数据库基础操作
数据类型
表结构操作
数据插入更新删除
基础查询
多表查询基础
高级查询
多表查询进阶
索引与优化
事务与锁
存储过程与函数基础
触发器与事件
存储过程与函数进阶
视图
用户权限管理
数据备份与恢复
性能优化
  1. SQL语法
  2. 触发器与事件

触发器与事件

一、触发器基础

1.1 触发器概念

1.1.1 什么是触发器

触发器：与表相关的数据库对象，在表上发生特定事件时自动执行。

东巴文理解：

触发器 = 自动感应灯
人来灯亮，人走灯灭
数据变化触发器自动执行
无需手动操作

1.1.2 触发器的优缺点

优点：

  • 自动执行，无需手动调用
  • 保证数据完整性
  • 实现复杂业务规则
  • 记录数据变更

缺点：

  • 增加数据库负担
  • 难以调试
  • 可能导致性能问题
  • 逻辑隐藏，不易维护

1.2 触发器类型

1.2.1 触发时机

触发时机 说明
BEFORE 事件发生前触发
AFTER 事件发生后触发

1.2.2 触发事件

触发事件 说明
INSERT 插入数据时触发
UPDATE 更新数据时触发
DELETE 删除数据时触发

1.3 创建触发器

1.3.1 基本语法

语法格式：

CREATE TRIGGER 触发器名
{BEFORE|AFTER} {INSERT|UPDATE|DELETE}
ON 表名
FOR EACH ROW
BEGIN
    触发器逻辑;
END;

参数说明：

  • FOR EACH ROW：行级触发器，每行触发一次

1.3.2 NEW和OLD关键字

NEW：新数据

  • INSERT：NEW表示新插入的数据
  • UPDATE：NEW表示更新后的数据
  • DELETE：不可用

OLD：旧数据

  • INSERT：不可用
  • UPDATE：OLD表示更新前的数据
  • DELETE：OLD表示删除的数据

1.4 BEFORE触发器

1.4.1 数据验证

示例：

-- 创建员工表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    salary DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建BEFORE INSERT触发器
DELIMITER //

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    -- 验证薪资不能为负数
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '薪资不能为负数';
    END IF;
    
    -- 自动设置创建时间
    SET NEW.created_at = NOW();
END //

DELIMITER ;

-- 测试
INSERT INTO employees (name, salary) VALUES ('张三', 10000);  -- 成功
INSERT INTO employees (name, salary) VALUES ('李四', -1000);  -- 失败

1.4.2 数据自动计算

示例：

-- 创建订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    quantity INT,
    price DECIMAL(10, 2),
    total DECIMAL(10, 2)
);

-- 创建BEFORE INSERT触发器
DELIMITER //

CREATE TRIGGER before_order_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    -- 自动计算总价
    SET NEW.total = NEW.quantity * NEW.price;
END //

DELIMITER ;

-- 测试
INSERT INTO orders (quantity, price) VALUES (10, 100.00);
SELECT * FROM orders;  -- total自动计算为1000.00

1.5 AFTER触发器

1.5.1 日志记录

示例：

-- 创建日志表
CREATE TABLE employee_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    action VARCHAR(20),
    employee_id INT,
    old_name VARCHAR(50),
    new_name VARCHAR(50),
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建AFTER UPDATE触发器
DELIMITER //

CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_logs (action, employee_id, old_name, new_name, old_salary, new_salary)
    VALUES ('UPDATE', OLD.id, OLD.name, NEW.name, OLD.salary, NEW.salary);
END //

DELIMITER ;

-- 测试
UPDATE employees SET salary = 12000 WHERE id = 1;
SELECT * FROM employee_logs;

1.5.2 级联操作

示例：

-- 创建部门表
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50),
    employee_count INT DEFAULT 0
);

-- 创建员工表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    dept_id INT
);

-- 创建AFTER INSERT触发器
DELIMITER //

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    UPDATE departments 
    SET employee_count = employee_count + 1
    WHERE id = NEW.dept_id;
END //

DELIMITER ;

-- 创建AFTER DELETE触发器
DELIMITER //

CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    UPDATE departments 
    SET employee_count = employee_count - 1
    WHERE id = OLD.dept_id;
END //

DELIMITER ;

-- 测试
INSERT INTO departments (dept_name) VALUES ('技术部');
INSERT INTO employees (name, dept_id) VALUES ('张三', 1);
SELECT * FROM departments;  -- employee_count = 1

二、触发器管理

2.1 查看触发器

2.1.1 查看触发器列表

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

-- 查看指定数据库的触发器
SHOW TRIGGERS FROM database_name;

-- 查看指定表的触发器
SHOW TRIGGERS LIKE 'employees';

2.1.2 查看触发器定义

-- 查看触发器详细信息
SELECT * FROM information_schema.TRIGGERS 
WHERE TRIGGER_NAME = 'before_employee_insert';

2.2 删除触发器

语法格式：

DROP TRIGGER [IF EXISTS] [数据库名.]触发器名;

示例：

-- 删除触发器
DROP TRIGGER IF EXISTS before_employee_insert;

2.3 修改触发器

MySQL不支持直接修改触发器，需要先删除再创建：

-- 删除旧触发器
DROP TRIGGER IF EXISTS before_employee_insert;

-- 创建新触发器
DELIMITER //

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    -- 新的逻辑
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '薪资不能为负数';
    END IF;
END //

DELIMITER ;

三、触发器应用场景

3.1 数据验证

3.1.1 验证数据完整性

示例：

-- 创建商品表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    stock INT,
    price DECIMAL(10, 2)
);

-- 创建BEFORE INSERT触发器
DELIMITER //

CREATE TRIGGER before_product_insert
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    -- 验证库存不能为负数
    IF NEW.stock < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '库存不能为负数';
    END IF;
    
    -- 验证价格必须大于0
    IF NEW.price <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '价格必须大于0';
    END IF;
END //

DELIMITER ;

3.2 数据同步

3.2.1 同步数据到其他表

示例：

-- 创建主表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100)
);

-- 创建备份表
CREATE TABLE users_backup (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    name VARCHAR(50),
    email VARCHAR(100),
    action VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建AFTER INSERT触发器
DELIMITER //

CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO users_backup (user_id, name, email, action)
    VALUES (NEW.id, NEW.name, NEW.email, 'INSERT');
END //

DELIMITER ;

-- 创建AFTER UPDATE触发器
DELIMITER //

CREATE TRIGGER after_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO users_backup (user_id, name, email, action)
    VALUES (NEW.id, NEW.name, NEW.email, 'UPDATE');
END //

DELIMITER ;

-- 创建AFTER DELETE触发器
DELIMITER //

CREATE TRIGGER after_user_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO users_backup (user_id, name, email, action)
    VALUES (OLD.id, OLD.name, OLD.email, 'DELETE');
END //

DELIMITER ;

3.3 审计日志

3.3.1 记录数据变更

示例：

-- 创建审计日志表
CREATE TABLE audit_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    table_name VARCHAR(50),
    action VARCHAR(20),
    record_id INT,
    old_data TEXT,
    new_data TEXT,
    user VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建通用审计触发器
DELIMITER //

CREATE TRIGGER after_employee_audit
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, action, record_id, old_data, new_data, user)
    VALUES (
        'employees',
        'UPDATE',
        OLD.id,
        CONCAT('name:', OLD.name, ',salary:', OLD.salary),
        CONCAT('name:', NEW.name, ',salary:', NEW.salary),
        USER()
    );
END //

DELIMITER ;

四、事件调度器

4.1 事件调度器概念

4.1.1 什么是事件调度器

事件调度器：MySQL的定时任务执行器，可以定期执行SQL语句。

东巴文理解：

事件调度器 = 闹钟
设置闹钟，定时提醒
事件调度器定时执行任务
无需人工干预

4.1.2 查看事件调度器状态

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

-- 查看事件列表
SHOW EVENTS;

4.2 开启事件调度器

4.2.1 临时开启

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

-- 关闭事件调度器
SET GLOBAL event_scheduler = OFF;

4.2.2 永久开启

修改配置文件：

[mysqld]
event_scheduler = ON

4.3 创建事件

4.3.1 基本语法

语法格式：

CREATE EVENT [IF NOT EXISTS] 事件名
ON SCHEDULE 时间计划
[ON COMPLETION [NOT] PRESERVE]
[ENABLE|DISABLE|DISABLE ON SLAVE]
[COMMENT '注释']
DO
BEGIN
    SQL语句;
END;

参数说明：

  • ON SCHEDULE：时间计划
  • ON COMPLETION PRESERVE：事件执行完后保留
  • ENABLE：启用事件
  • DISABLE：禁用事件

4.3.2 时间计划

一次性事件：

CREATE EVENT one_time_event
ON SCHEDULE AT TIMESTAMP '2024-12-31 23:59:59'
DO
    INSERT INTO logs (message) VALUES ('新年快乐');

周期性事件：

-- 每天执行
CREATE EVENT daily_event
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
    DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 每小时执行
CREATE EVENT hourly_event
ON SCHEDULE EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP
DO
    UPDATE statistics SET value = value + 1;

-- 每分钟执行
CREATE EVENT minute_event
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
DO
    INSERT INTO heartbeat (time) VALUES (NOW());

4.4 事件示例

4.4.1 定期清理日志

-- 创建日志表
CREATE TABLE logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建事件：每天凌晨2点清理30天前的日志
DELIMITER //

CREATE EVENT clean_logs_event
ON SCHEDULE EVERY 1 DAY
STARTS CONCAT(CURDATE() + INTERVAL 1 DAY, ' 02:00:00')
ON COMPLETION PRESERVE ENABLE
COMMENT '每天凌晨2点清理30天前的日志'
DO
BEGIN
    DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
END //

DELIMITER ;

4.4.2 定期统计数据

-- 创建统计表
CREATE TABLE daily_statistics (
    id INT PRIMARY KEY AUTO_INCREMENT,
    stat_date DATE,
    user_count INT,
    order_count INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建事件：每天凌晨1点统计前一天数据
DELIMITER //

CREATE EVENT daily_statistics_event
ON SCHEDULE EVERY 1 DAY
STARTS CONCAT(CURDATE() + INTERVAL 1 DAY, ' 01:00:00')
ON COMPLETION PRESERVE ENABLE
COMMENT '每天凌晨1点统计前一天数据'
DO
BEGIN
    INSERT INTO daily_statistics (stat_date, user_count, order_count)
    SELECT 
        CURDATE() - INTERVAL 1 DAY,
        (SELECT COUNT(*) FROM users WHERE DATE(created_at) = CURDATE() - INTERVAL 1 DAY),
        (SELECT COUNT(*) FROM orders WHERE DATE(created_at) = CURDATE() - INTERVAL 1 DAY);
END //

DELIMITER ;

4.4.3 定期备份数据

-- 创建事件：每周日凌晨3点备份数据
DELIMITER //

CREATE EVENT weekly_backup_event
ON SCHEDULE EVERY 1 WEEK
STARTS CONCAT(CURDATE() + INTERVAL (7 - WEEKDAY(CURDATE())) DAY, ' 03:00:00')
ON COMPLETION PRESERVE ENABLE
COMMENT '每周日凌晨3点备份数据'
DO
BEGIN
    -- 创建备份表
    SET @backup_table = CONCAT('users_backup_', DATE_FORMAT(NOW(), '%Y%m%d'));
    SET @sql = CONCAT('CREATE TABLE ', @backup_table, ' AS SELECT * FROM users');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

五、事件管理

5.1 查看事件

5.1.1 查看事件列表

-- 查看所有事件
SHOW EVENTS;

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

5.1.2 查看事件定义

-- 查看事件详细信息
SELECT * FROM information_schema.EVENTS 
WHERE EVENT_NAME = 'clean_logs_event';

5.2 修改事件

语法格式：

ALTER EVENT 事件名
[ON SCHEDULE 时间计划]
[ON COMPLETION [NOT] PRESERVE]
[ENABLE|DISABLE|DISABLE ON SLAVE]
[COMMENT '注释']
[DO SQL语句];

示例：

-- 禁用事件
ALTER EVENT clean_logs_event DISABLE;

-- 启用事件
ALTER EVENT clean_logs_event ENABLE;

-- 修改事件执行时间
ALTER EVENT clean_logs_event
ON SCHEDULE EVERY 1 DAY
STARTS CONCAT(CURDATE() + INTERVAL 1 DAY, ' 03:00:00');

5.3 删除事件

语法格式：

DROP EVENT [IF EXISTS] [数据库名.]事件名;

示例：

-- 删除事件
DROP EVENT IF EXISTS clean_logs_event;

六、触发器与事件最佳实践

6.1 触发器最佳实践

6.1.1 避免递归触发

问题：

-- 触发器A触发触发器B，触发器B又触发触发器A
-- 形成无限循环

解决方案：

-- 使用条件判断避免递归
DELIMITER //

CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    -- 只有薪资变化时才记录日志
    IF OLD.salary != NEW.salary THEN
        INSERT INTO salary_logs (employee_id, old_salary, new_salary)
        VALUES (OLD.id, OLD.salary, NEW.salary);
    END IF;
END //

DELIMITER ;

6.1.2 避免复杂逻辑

不推荐：

-- 触发器中包含复杂业务逻辑
DELIMITER //

CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    -- 复杂的业务逻辑
    -- ...
    -- ...
    -- ...
END //

DELIMITER ;

推荐：

-- 触发器只做简单操作
DELIMITER //

CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    -- 简单的日志记录
    INSERT INTO order_logs (order_id, action, created_at)
    VALUES (NEW.id, 'INSERT', NOW());
END //

DELIMITER ;

6.2 事件最佳实践

6.2.1 合理设置执行时间

不推荐：

-- 业务高峰期执行
CREATE EVENT busy_time_event
ON SCHEDULE EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP
DO
    -- 大量数据处理
    DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 DAY);

推荐：

-- 业务低峰期执行
CREATE EVENT off_peak_event
ON SCHEDULE EVERY 1 DAY
STARTS CONCAT(CURDATE() + INTERVAL 1 DAY, ' 03:00:00')
DO
    -- 大量数据处理
    DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

6.2.2 错误处理

示例：

DELIMITER //

CREATE EVENT safe_event
ON SCHEDULE EVERY 1 DAY
STARTS CONCAT(CURDATE() + INTERVAL 1 DAY, ' 02:00:00')
DO
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 记录错误日志
        INSERT INTO error_logs (error_message, created_at)
        VALUES (CONCAT('Event failed: ', SQLSTATE), NOW());
    END;
    
    -- 执行任务
    DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
END //

DELIMITER ;

七、实战案例

7.1 库存预警系统

-- 创建商品表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    stock INT,
    warning_stock INT DEFAULT 10
);

-- 创建预警日志表
CREATE TABLE stock_warnings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    product_name VARCHAR(50),
    stock INT,
    warning_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建触发器：库存低于预警值时记录
DELIMITER //

CREATE TRIGGER after_product_update
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.stock < NEW.warning_stock AND OLD.stock >= OLD.warning_stock THEN
        INSERT INTO stock_warnings (product_id, product_name, stock)
        VALUES (NEW.id, NEW.name, NEW.stock);
    END IF;
END //

DELIMITER ;

-- 创建事件：每天检查库存
DELIMITER //

CREATE EVENT check_stock_event
ON SCHEDULE EVERY 1 DAY
STARTS CONCAT(CURDATE() + INTERVAL 1 DAY, ' 08:00:00')
DO
BEGIN
    INSERT INTO stock_warnings (product_id, product_name, stock)
    SELECT id, name, stock
    FROM products
    WHERE stock < warning_stock;
END //

DELIMITER ;

7.2 数据归档系统

-- 创建订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    amount DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建订单归档表
CREATE TABLE orders_archive (
    id INT PRIMARY KEY,
    amount DECIMAL(10, 2),
    created_at TIMESTAMP,
    archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建事件：每月归档3个月前的订单
DELIMITER //

CREATE EVENT archive_orders_event
ON SCHEDULE EVERY 1 MONTH
STARTS CONCAT(CURDATE() + INTERVAL 1 MONTH, ' 02:00:00')
DO
BEGIN
    -- 插入归档表
    INSERT INTO orders_archive (id, amount, created_at)
    SELECT id, amount, created_at
    FROM orders
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 3 MONTH);
    
    -- 删除原表数据
    DELETE FROM orders
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 3 MONTH);
END //

DELIMITER ;

八、本章小结

8.1 核心要点

✅ 触发器在特定事件时自动执行 ✅ BEFORE触发器用于数据验证 ✅ AFTER触发器用于日志记录 ✅ 事件调度器用于定时任务 ✅ 合理使用触发器和事件

8.2 验证清单

完成本章学习后，请确认您能够：

  • 创建BEFORE和AFTER触发器
  • 使用NEW和OLD关键字
  • 创建定时事件
  • 管理触发器和事件
  • 避免触发器和事件的常见问题
上一章：存储过程与函数基础
下一章：存储过程与函数进阶