MySQL进阶

前言

在文章MySQL进阶中我们介绍了MySQL的基础用法，今天我们来介绍一下MySQL的其他用法。

触发器

简介

MySQL触发器（Trigger）是数据库中的一种特殊存储程序，它与特定表关联，当该表发生特定事件（如INSERT、UPDATE、DELETE）时会自动执行，无需手动调用。触发器主要用于实现数据的自动校验、日志记录、数据同步等功能，确保数据库操作的一致性和完整性。但是在实际的项目中使用触发器之前，务必权衡其利弊，优先考虑是否能在应用层实现（特别是互联网场景，几乎是禁止使用的），避免引入不必要的复杂性和性能瓶颈。

核心概念

触发器的定义包含以下关键信息：

• 关联表：触发器必须绑定到某一张具体的表，当该表发生指定事件时触发。
• 触发时机：BEFORE（事件执行前触发）或AFTER（事件执行后触发）。
• 触发事件：INSERT（插入数据时）、UPDATE（更新数据时）、DELETE（删除数据时）。
• 触发操作：触发器被触发时执行的SQL语句（可以是单条或多条语句，多条时需用BEGIN...END包裹）。

在触发器的操作中，MySQL提供了两个临时表用于访问触发事件前后的数据：

• **NEW**：仅在INSERT和UPDATE事件中可用，代表“即将插入”或“更新后”的数据。

  • 例如：NEW.id表示新插入或更新后的id字段值。
  • 在BEFORE INSERT或BEFORE UPDATE中，可以修改NEW的值（如SET NEW.create_time = NOW()）。

• **OLD**：仅在UPDATE和DELETE事件中可用，代表“更新前”或“删除前”的数据。

  • 例如：OLD.name表示更新前或删除前的name字段值。
  • OLD的值是只读的，不能修改。

创建语法

基本语法如下：

CREATE TRIGGER 触发器名称
触发时机（BEFORE/AFTER） 触发事件（INSERT/UPDATE/DELETE）
ON 关联表名 FOR EACH ROW  -- 行级触发（每影响一行就触发一次）
BEGIN
  -- 触发时执行的SQL语句（可多条）
END;

说明：

- 触发器名称: 为触发器指定一个唯一的名称（在同一个数据库中唯一）。
- FOR EACH ROW：表示触发器是“行级触发”，即每插入/更新/删除一行数据，触发器就执行一次（MySQL仅支持行级触发器）。
- 若触发操作是单条SQL，可省略BEGIN...END; 若多条，需用BEGIN...END包裹，且需注意分隔符（默认';'可能与语句冲突，可临时修改分隔符，如DELIMITER //）。

触发器类型（6种组合）

触发时间	触发事件	应用场景举例
BEFORE INSERT	插入前	校验插入数据合法性（如年龄不能为负）、自动填充默认值（如创建时间）
AFTER INSERT	插入后	记录插入日志（如新增用户后写入操作日志表）
BEFORE UPDATE	更新前	校验更新后的数据（如价格不能低于成本）、记录更新前的旧值
AFTER UPDATE	更新后	同步更新关联表数据（如订单状态变更后更新统计数据）
BEFORE DELETE	删除前	校验是否允许删除（如禁止删除有子订单的主订单）
AFTER DELETE	删除后	备份删除的数据（如删除用户前将数据存档到历史表）

触发器的管理语句

• 查看触发器

-- 查看所有触发器（需有足够权限）
SHOW TRIGGERS;

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

-- 从系统表information_schema中查询（更灵活）
SELECT * FROM information_schema.TRIGGERS 
WHERE TRIGGER_NAME = '触发器名称';

• 删除触发器

DROP TRIGGER IF EXISTS 触发器名称;

# 注意：删除关联表时，该表上的所有触发器会被自动删除。

示例

数据验证（Before Insert）

# 向user表插入数据时，阻止插入无效邮箱地址。
DELIMITER $$ -- 更改分隔符，以便在触发器体内使用分号
CREATE TRIGGER validate_email_before_insert
BEFORE INSERT ON user
FOR EACH ROW
BEGIN
    -- 简单的邮箱格式检查（实际应用需要更复杂的正则）
    IF NEW.email NOT LIKE '%@%' THEN
        SIGNAL SQLSTATE '45000' -- 使用自定义错误状态码 (45000 是通用用户定义异常)
        SET MESSAGE_TEXT = 'Invalid email format';
    END IF;
END$$
DELIMITER ; -- 恢复分隔符

我们来测试一下效果：

首先运行创建触发器语句，然后我们先看看触发器是否正确创建

可以看到触发器已经成功创建，接着我们来尝试插入不符合标准的数据

可以看到触发器已经成功拦截了不符合标准的数据插入

删除数据时备份到历史表

# 删除user表数据时，自动将删除的记录备份到user_history表
-- 创建历史表（结构与user一致，多一个删除时间字段）
CREATE TABLE user_history (
  id INT,
  name VARCHAR(50),
  create_time DATETIME,
  delete_time DATETIME
);

DELIMITER //
CREATE TRIGGER trigger_user_delete
AFTER DELETE ON user FOR EACH ROW
BEGIN
  -- 将删除前的数据（OLD）插入历史表，并记录删除时间
  INSERT INTO user_history (id, name, create_time, delete_time)
  VALUES (OLD.id, OLD.name, OLD.create_time, NOW());
END //
DELIMITER ;

测试

-- 测试：在users表中删除一条记录
DELETE FROM users WHERE id = 1;

-- 结果：历史表中会新增一条备份记录
SELECT * FROM user_history;

常见用途

1. 数据验证与完整性约束增强：

• 检查数据的有效性（例如，BEFORE INSERT 检查邮箱格式）。
• 强制执行业务规则（例如，BEFORE UPDATE 确保订单金额不能减少）。
• 实现复杂的参照完整性（例如，BEFORE DELETE 检查主表记录是否被子表引用，模拟外键的 ON DELETE RESTRICT）。

2. 审计（Audit Trails）：

• AFTER INSERT/UPDATE/DELETE 时，将更改记录（包括谁、何时、什么数据、旧值、新值等）自动写入到一个专门的审计表中。这是最常见的用途之一。

3. 数据同步：

• 当主表数据变更时，自动更新相关的汇总表、统计表或缓存表（例如，AFTER INSERT ON orders 时更新 customer_totals 表中的总订单金额）。

4. 计算与派生列：

• 在 BEFORE INSERT/UPDATE 时，基于其他列的值计算并设置某个字段（例如，根据 unit_price 和 quantity 自动计算并设置 total_price）。

5. 复杂默认值：

• 当简单的 DEFAULT 约束无法满足需求时（例如，需要根据其他表或复杂逻辑生成默认值），可以在 BEFORE INSERT 触发器中设置 NEW.column 的值。

6. 防止误操作：

• 使用 BEFORE DELETE 触发器实现“软删除”（将 status 字段标记为 ‘deleted’ 而不是物理删除行）或阻止删除特定重要记录（通过 SIGNAL SQLSTATE 抛出错误）。

注意事项

• 性能影响：触发器会在表操作时自动执行，过多或复杂的触发器可能降低SQL执行效率（尤其是批量插入/更新/删除时）。
• 避免递归触发：触发器内部的操作可能再次触发其他触发器（如A表触发器修改B表，B表触发器又修改A表），可能导致无限递归，需谨慎设计。
• 权限限制：创建触发器需要TRIGGER权限；删除触发器需要TRIGGER权限或表的DROP权限。
• 事务一致性：触发器的操作与触发事件在同一事务中，若触发器执行失败，触发事件会回滚（如INSERT触发的触发器报错，插入操作会失败）。
• 不支持的操作：触发器中不能使用START TRANSACTION、COMMIT、ROLLBACK等事务控制语句，也不能返回结果集。
• 隐蔽性：触发器在后台默默运行。如果开发人员不知道某个表上存在触发器，调试因触发器逻辑引起的问题可能会比较困难。良好的文档和命名规范很重要。
• 一个表上的多个触发器： 可以为同一个表上的同一个事件（如 BEFORE UPDATE）定义多个触发器。但它们的执行顺序不确定（在 MySQL 5.7 及更早版本中，按创建时间顺序执行；在 MySQL 8.0+ 中，按 trigger_order 子句指定顺序，默认按创建顺序）。应避免依赖顺序，或者使用 trigger_order（8.0+）明确指定。

存储过程

简介

存储过程是一组预编译好的 SQL 语句集合，被存储在数据库中，可通过名称直接调用。它类似于编程语言中的函数，能完成特定业务逻辑，支持参数传递（输入、输出、输入输出）和流程控制（条件判断、循环等）。

特点

• 一次编译多次执行，减少 SQL 解析开销，提升性能。
• 封装复杂逻辑，简化应用层代码，降低网络传输量。
• 支持参数传递，增强灵活性，可根据不同参数执行不同操作。

基本语法

-- 创建存储过程
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
  -- SQL语句及流程控制语句
END;

-- 调用存储过程
CALL 存储过程名(参数);

与触发器的对比

简单理解：存储过程就是一个函数，触发器是可以去调用这个函数的开关。

对比维度	存储过程	触发器
执行方式	需手动调用（如 CALL 语句）	自动触发（关联表发生指定事件时）
触发条件	无固定触发条件，按需调用	与特定表的 INSERT/UPDATE/DELETE 事件绑定
用途	实现复杂业务逻辑（如多表操作、批量处理）	实现数据自动校验、日志记录、同步等（依赖表事件）
参数支持	支持输入、输出、输入输出参数	无参数，依赖 NEW/OLD 临时表获取数据
调用时机	可在任意需要时调用	仅在关联表发生指定事件时执行
灵活性	更灵活，可独立于表操作执行	依赖表事件，灵活性较低，可以在触发器中，调用存储过程。

物化视图

简介

物化视图（Materialized View）是数据库中的一种特殊对象，它与普通视图（View）的最大区别在于：物化视图会实际存储查询结果数据，而普通视图仅保存查询定义（不存储数据，每次访问时动态计算结果）。

特点

• 预计算并存储结果：基于定义的查询语句（通常是复杂的多表关联或聚合查询）预先计算结果，并将数据物理存储在磁盘上，类似一张 “快照表”。
• 可定期刷新：由于源表数据可能变化，物化视图需要通过 “刷新” 操作更新存储的结果，刷新方式可配置（如定时刷新、手动刷新、源表变化时自动刷新等）。
• 提升查询性能：对于复杂查询（尤其是涉及大量数据聚合、多表关联），直接查询物化视图可避免重复计算，大幅提高响应速度。

基本语法

CREATE MATERIALIZED VIEW 物化视图名称
[BUILD IMMEDIATE | BUILD DEFERRED]  -- 数据加载时机
REFRESH [FAST | COMPLETE | FORCE]  -- 刷新方式
[ON [COMMIT | DEMAND] | START WITH 开始时间 NEXT 刷新间隔]  -- 刷新时机/频率
AS
-- 定义物化视图的查询语句（来源表的查询逻辑）
SELECT 列名1, 列名2, ... 
FROM 源表1 
[JOIN 源表2 ON 关联条件]
[WHERE 过滤条件]
[GROUP BY 分组列];

使用场景

• 用于查询频繁但数据变化不频繁的场景（如报表统计、数据分析）。
• 需加速复杂查询（如多表关联、聚合计算、带函数的查询）。
• 跨数据库或分布式环境中，用于同步特定数据集，减少远程访问开销。

注意事项

• 不同数据库对物化视图的支持不同：MySQL（8.0 及以上通过 “生成列” 或第三方工具模拟，原生支持有限）、PostgreSQL、Oracle 等支持较好。
• 刷新策略需合理配置：过于频繁的刷新会消耗资源，间隔太久则数据时效性差。
• 会增加存储成本，且刷新时可能对源表产生锁竞争，影响写入性能。

与视图的区别

特性	普通视图（View）	物化视图（Materialized View）	物理表
数据存储	不存储数据，仅保存查询逻辑	存储查询结果数据（其实这种方式你通过应用程序，定时往物理表更新数据是一样的）	存储原始业务数据
查询性能	每次访问需重新执行查询，性能低	直接读取预存数据，性能高	直接读取原始数据，性能高
数据时效性	实时反映源表变化	非实时，需刷新后才同步源表变化	实时反映数据状态（数据直接修改即更新）
存储空间	几乎不占用空间	占用与结果数据量相当的空间	占用与原始数据量相当的空间
数据修改方式	无法直接修改（需通过源表）	无法直接修改（需通过刷新）	可直接通过 INSERT/UPDATE/DELETE 修改
数据来源	依赖源表查询结果	依赖源表查询结果	由用户直接输入或系统生成