MySQL触发器终极指南：从入门到精通

引言

在处理关系型数据库和结构化查询语言（SQL）时，大多数数据操作是通过显式执行的查询操作来实现的，例如SELECT、INSERT或UPDATE。

然而，SQL数据库还可以通过触发器（Triggers）自动执行预定义的操作，每当发生特定事件时。例如，您可以使用触发器保留所有DELETE语句的审计日志，或者在每次更新或追加行到表时自动更新聚合统计摘要。

在本教程中，您将使用不同的SQL触发器来自动执行在行插入、更新或删除时进行的操作。

先决条件

要按照本指南操作，您需要一台运行基于SQL的关系数据库管理系统（RDBMS）的计算机。本指南中的指令和示例是在以下环境下验证的：

• 一台运行 Ubuntu 20.04 的服务器，拥有具备管理权限的非root用户，并配置了UFW防火墙，具体请参考我们的 Ubuntu 20.04 服务器初始设置指南。
• 在服务器上安装并配置了MySQL，具体请参考 如何在 Ubuntu 20.04 上安装 MySQL。本指南已通过使用在第3步中创建的非root MySQL用户进行验证。
• 对执行 SELECT、INSERT、UPDATE 和 DELETE 查询以操作数据库有基本的了解，具体请参考我们的 如何在 SQL 中从表中选择行、如何在 SQL 中插入数据、如何在 SQL 中更新数据 和 如何在 SQL 中删除数据 指南。
• 对使用嵌套查询有基本的了解，具体请参考我们的 如何在 SQL 中使用嵌套查询 指南。
• 对使用聚合数学函数有基本的了解，具体请参考我们的 如何在 SQL 中使用数学表达式和聚合函数 指南。

连接到MySQL并设置一个样本数据库

在本节中，您将连接到一个MySQL服务器并创建一个示例数据库，以便您可以在接下来的几节中跟随示例进行操作。

对于本指南，您将使用一个假想的收藏品集合。您将存储关于当前拥有的收藏品的详细信息，随时保持它们的总价值可用，并确保删除一个收藏品始终留下痕迹。

如果您的SQL数据库系统在远程服务器上运行，请从您的本地机器上的SSH登录到服务器。

ssh sammy@your_server_ip

然后打开MySQL服务器提示符，将“sammy”替换为您的MySQL用户帐户的名称。

mysql -u sammy -p

创建一个名为collectibles的数据库。

CREATE DATABASE collectibles;

如果数据库创建成功，您将收到如下输出：

Query OK, 1 row affected (0.01 sec)

要选择collectibles数据库，请运行以下USE语句：

USE collectibles;

您将会得到以下的输出：

Database changed

在选择数据库之后，您可以在其中创建示例表。表collectibles将包含有关数据库中收藏品的简化数据。它将包含以下列：

• name: 此列存储每个收藏品的名称，使用varchar数据类型，最大长度为50个字符。
• value: 此列存储收藏品的市场价值，使用decimal数据类型，小数点前最多5位，小数点后2位。

用以下命令创建示例表：

CREATE TABLE collectibles (
name varchar(50),
value decimal(5, 2)
);

如果下面的输出打印出来，那么表已经被创建了。

这是文章《如何在MySQL中使用触发器》的第2部分（共9部分）。

Query OK, 0 rows affected (0.00 sec)

下一个表将命名为collectibles_stats，它将用于跟踪收藏品集合中所有收藏品的累计价值。该表将包含一行数据，具有以下列：

• count: 此列存储已拥有收藏品的数量，使用INT数据类型表示。
• value: 此列存储所有收藏品的累计价值，使用DECIMAL数据类型，小数点前最多5位，小数点后2位。

使用以下命令创建示例表：

CREATE TABLE collectibles_stats (
  count INT,
  value DECIMAL(5, 2)
);

如果出现以下输出，则表示表已成功创建。

Query OK, 0 rows affected (0.00 sec)

第三个也是最后一个表将命名为collectibles_archive，它将跟踪所有从收藏中移除的藏品，以确保它们永远不会丢失。它将保存与collectibles表类似的数据，但增加了移除日期。它使用以下列：

• name: 此列存储每个已移除收藏品的名称，使用VARCHAR数据类型，最大长度为50个字符。
• value: 此列存储删除时收藏品的市场价值，使用DECIMAL数据类型，小数点前最多5位，小数点后2位。
• removed_on: 此列存储每个已归档收藏品的删除日期和时间，使用TIMESTAMP数据类型，默认值为NOW()，表示当新行插入此表时的当前日期和时间。

使用以下命令创建示例表：

CREATE TABLE collectibles_archive (
  name VARCHAR(50),
  value DECIMAL(5, 2),
  removed_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

如果打印出以下输出，说明表已创建：

Query OK, 0 rows affected (0.00 sec)

随后，通过运行以下INSERT INTO操作，将collectibles_stats表加载到空collectibles集合的初始状态。

INSERT INTO collectibles_stats SELECT COUNT(name), SUM(value) FROM collectibles;

使用INSERT INTO操作将向collectibles_stats添加一行，其中的值是使用聚合函数计算得出的，以对collectibles表中的所有行进行计数，并使用value列和SUM函数计算所有收藏品的总价值。下面的输出表明已添加了一行。

Query OK, 1 row affected (0.002 sec) Records: 1 Duplicates: 0 Warnings: 0

你可以通过在表上执行SELECT语句来验证这一点。

SELECT * FROM collectibles_stats;

由于数据库中尚无可收集的物品，因此初始物品数量为0，累计价值为NULL。

+-------+-------+
| count | value |
+-------+-------+
|     0 |  NULL |
+-------+-------+
1 row in set (0.000 sec)

有了这些，你就可以继续阅读本指南并开始在MySQL中使用触发器了。

理解触发器

如何在MySQL中使用触发器（第3部分，共9部分）

触发器是针对特定表定义的语句，当表中发生指定事件时，由数据库自动执行。触发器可用于确保每次在特定表上执行某个语句时，一些操作将始终被执行，而无需数据库用户手动干预。

每个与表关联的触发器都有一个用户定义的名称和一对条件，这些条件指示数据库引擎何时执行触发器。这些条件可以分为两个不同的类别：

• 数据库事件（Database event）： 触发器可以在对表执行INSERT、UPDATE或DELETE语句时被执行。
• 事件时间（Event time）： 此外，触发器可以在相关语句执行之前（BEFORE）或之后（AFTER）执行。

将这两个条件组合在一起，总共有六种不同的触发可能性，每次满足联合条件时都会自动执行。在执行满足条件的语句之前，触发器的顺序是BEFORE INSERT、BEFORE UPDATE和BEFORE DELETE。可以利用这些触发器在数据被插入或更新到表中之前对其进行操作和验证，或者将删除行的详情保存用于审计或归档目的。

当满足条件的语句被执行后触发的操作有AFTER INSERT、AFTER UPDATE和AFTER DELETE。这些操作可以根据语句执行后数据库的最终状态，在另一个表中更新汇总值。

为了执行诸如验证和操作输入数据或归档被删除的行等操作，数据库允许在触发器内访问数据值。对于INSERT触发器，只能使用新插入的数据。对于UPDATE触发器，可以访问原始数据和更新后的数据。最后，对于DELETE触发器，只能使用原始行数据（因为没有新数据可参考）。

触发器体内可使用的数据是通过数据库中当前的OLD记录和查询将要保存的NEW记录公开的。您可以使用OLD.column_name和NEW.column_name的语法来引用单个列。

以下示例展示了用于创建新触发器的SQL语句的一般语法：

1. CREATE TRIGGER trigger_name trigger_condition
2. ON table_name
3. FOR EACH ROW
4. trigger_actions;

让我们将语法分解成更小的部分：

• CREATE TRIGGER 是用于在数据库中创建新触发器的SQL语句名称。
• trigger_name 是触发器的用户定义名称，用于描述其作用，类似于表名和列名用于描述其含义的方式。
• ON table_name 告诉数据库该触发器应监控在table_name表上发生的事件。
• trigger_condition 是六种可能的选择之一，定义了触发器何时运行，例如 BEFORE INSERT。
• FOR EACH ROW 告诉数据库触发器应针对受触发事件影响的每一行运行。一些数据库支持除FOR EACH ROW之外的其他执行模式；然而，在MySQL中，对于导致触发器执行的语句所影响的每一行运行触发器体中的语句是唯一的选项。
• trigger_actions 是触发器的主体，定义了触发器执行时会发生什么。它通常是一个有效的SQL语句。可以使用BEGIN和END关键字将语句列表包含在一个块中，从而在触发器主体中包含多个语句以执行复杂的数据操作。然而，这超出了本教程的范围。请查阅触发器的官方文档以了解更多定义触发器所使用的语法。

在接下来的部分，您将创建在进行INSERT和UPDATE操作之前操作数据的触发器。

使用BEFORE INSERT和BEFORE UPDATE触发器来操作数据

在这一节中，您将使用触发器在执行INSERT和UPDATE语句之前处理数据。

在这个例子中，您将使用触发器来确保数据库中的所有可收集项都使用大写名称以保持一致。如果不使用触发器，您将需要记住为每个插入和更新语句使用大写的可收集项名称。如果您忘记了，数据库将保存原样的数据，可能导致数据集中的错误。

您将从插入一个名为“spaceship model”的示例收藏品开始，价值12.50美元。为了说明问题，项目名称将以小写字母书写。执行以下语句：

1. INSERT INTO collectibles VALUES (‘spaceship model‘, 12.50);

以下信息确认物品已经被添加。

Query OK, 1 row affected (0.009 sec)

通过执行SELECT查询您可以验证该行是否已插入。

1. SELECT * FROM collectibles;

以下的输出将打印到屏幕上：

这是文章《如何在MySQL中使用触发器》的第4部分（共9部分）。

+-----------------+-------+
| name            | value |
+-----------------+-------+
| spaceship model | 12.50 |
+-----------------+-------+
1 row in set (0.000 sec)

这个可收藏物品的名称以小写字母保存。

为了确保所有未来的可收藏品名称都以大写字母存储，您将创建一个BEFORE INSERT触发器。在触发语句运行之前执行的触发器，允许您在数据传递给数据库之前对其进行操作。

执行以下语句：

1. CREATE TRIGGER uppercase_before_insert BEFORE INSERT
2. ON collectibles
3. FOR EACH ROW
4. SET NEW.name = UPPER(NEW.name);

这个命令创建了一个名为uppercase_before_insert的触发器，它会在collectibles表上的所有INSERT语句执行之前被触发执行。

对于每一行插入的记录，触发器中的语句SET NEW.name = UPPER(NEW.name)将被执行。SET SQL命令将右侧的值赋给左侧。在这种情况下，NEW.name表示插入语句将保存的name列的值。通过对可收藏品的名称应用UPPER函数并将其重新赋值给列值，您将转换将保存在数据库中的值的大小写。

SET GLOBAL log_bin_trust_function_creators = 1;

GRANT TRIGGER on *.* TO 'sammy'@'localhost';
FLUSH PRIVILEGES;

MySQL会打印以下消息来确认触发器是否成功创建：

Query OK, 1 row affected (0.009 sec)

现在尝试使用小写参数向INSERT查询插入一个新的收藏品。

1. INSERT INTO collectibles VALUES (‘aircraft model‘, 10.00);

再次检查collectibles表中的结果行。

1. SELECT * FROM collectibles;

以下输出将会打印到屏幕上：

+-----------------+-------+
| name            | value |
+-----------------+-------+
| spaceship model | 12.50 |
| AIRCRAFT MODEL    | 10.00 |
+-----------------+-------+
2 rows in set (0.000 sec)

这次，新插入的“飞机模型”名称的大小写全部为大写字母，与您尝试插入的原始值不同。触发器在后台运行，在将行保存到数据库之前转换了字母的大小写。

现在，所有新的行都由触发器保护，确保名称将以大写保存。但是，仍然可以使用UPDATE语句保存不受限制的数据。为了使用相同效果保护UPDATE语句，创建另一个触发器。

1. CREATE TRIGGER uppercase_before_update BEFORE UPDATE
2. ON collectibles
3. FOR EACH ROW
4. SET NEW.name = UPPER(NEW.name);

两个触发器之间的区别在于触发条件。这次是在UPDATE之前，这意味着每次在表上执行UPDATE语句时触发器都会执行——除了以前的触发器已经覆盖的新行外，还会影响到每次更新时的现有行。

MySQL会输出一个确认信息，表示触发器已成功创建。

Query OK, 0 rows affected (0.009 sec)

为了验证新触发器的行为，请尝试更新“太空飞船模型”的价格值。

1. UPDATE collectibles SET value = 15.00 WHERE name = 'spaceship model';

WHERE 子句通过名称筛选要更新的行，并且 SET 子句将值更改为 15.00。

您将收到以下输出，确认该语句已更改了一行数据。

Query OK, 1 row affected (0.002 sec) Rows matched: 1 Changed: 1 Warnings: 0

检查收藏品表中的结果行。

1. SELECT * FROM collectibles;

以下输出将打印到屏幕上：

+—————–+——-+

| name | value |

+—————–+——-+

| SPACESHIP MODEL | 15.00 |

| AIRCRAFT MODEL | 10.00 |

+—————–+——-+

2 rows in set (0.000 sec)

现在，除了价格通过执行的语句更新为 15.00 之外，名称现在显示为“太空飞船模型”。当您运行 UPDATE 语句时，触发器被执行，影响了更新行上的值。在保存之前，名称列被转换为大写。

在本节中，您创建了两个触发器，它们在插入和更新查询之前运行，以便在将数据保存到数据库之前对其进行格式化。在下一节中，您将使用 BEFORE DELETE 触发器将被删除的行复制到一个单独的表中以进行归档。

使用“BEFORE DELETE”触发器在删除行之前执行操作

即使您不再拥有某个物品，您可能仍希望在单独的表中保留有关已删除条目的信息。在本教程的开始，您创建了一个名为 collectibles_archive 的第二个表，用于跟踪已从收藏中删除的所有收藏品。在本节中，您将使用一个触发器来归档已删除的条目，该触发器将在执行 DELETE 语句之前执行。

通过执行下面的语句检查归档表是否完全为空。

1. SELECT * FROM collectibles_archive;

以下输出将打印到屏幕上，确认 collectibles_archive 表为空。

Empty set (0.000 sec)

现在，如果您对 collectibles 表发起一条删除查询，任何一行数据都可能被无痕删除。

为了解决这个问题，您将创建一个触发器，在所有对 collectibles 表的 DELETE 查询之前执行。该触发器的目的是在删除之前将删除对象的副本保存到归档表中。

运行以下命令：

1. CREATE TRIGGER archive_before_delete BEFORE DELETE
2. ON collectibles
3. FOR EACH ROW
4. INSERT INTO collectibles_archive (name, value) VALUES (OLD.name, OLD.value);

触发器被命名为 archive_before_delete，并且在 collectibles 表上的任何 DELETE 查询之前触发。对于将要删除的每一行，将执行 INSERT 语句。然后，INSERT 语句会将一个新行插入到 collectibles_archive 表中，该行的数据值取自即将删除的 OLD 记录：OLD.name 变为 name 列，OLD.value 变为 value 列。

数据库将确认触发器的创建。

Query OK, 0 rows affected (0.009 sec)

将触发器准备好后，尝试从主要的收藏表中删除一个收藏品。

1. DELETE FROM collectibles WHERE name = 'SPACESHIP MODEL';

输出结果证实了查询成功运行。

Query OK, 1 row affected (0.004 sec)

现在，列出所有的收藏品。

1. SELECT * FROM collectibles;

以下结果将打印在屏幕上：

+—————-+——-+ | name | value | +—————-+——-+ | AIRCRAFT MODEL | 10.00 | +—————-+——-+ 1 row in set (0.000 sec)

目前只剩下飞机模型；太空船模型已被删除，不再在桌子上。不过，借助之前创建的触发器，这个删除应该会被记录在collectibles_archive表中。我们来检查一下。

执行另一个查询

1. SELECT * FROM collectibles_archive;

以下结果将打印到屏幕上：