使用 MySQL 触发器实现自动化

简介

在这个实验中，你将学习如何使用触发器在 MySQL 中自动化任务。本实验重点在于创建一个触发器，该触发器会将 products 表上的插入操作记录到单独的 product_logs 表中。

本实验将引导你连接到 MySQL 服务器，创建 mydatabase 数据库，并定义 products 和 product_logs 表。然后，你将创建一个名为 products_after_insert 的触发器，每当向 products 表中添加新产品时，该触发器会自动向 product_logs 表中插入一条记录。后续步骤包括测试触发器、更新其逻辑，最后删除该触发器。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_database("Database Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_table("Table Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/drop_table("Table Removal") mysql/BasicKeywordsandStatementsGroup -.-> mysql/alter_table("Table Modification") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("Data Retrieval") mysql/BasicKeywordsandStatementsGroup -.-> mysql/insert("Data Insertion") mysql/BasicKeywordsandStatementsGroup -.-> mysql/delete("Data Deletion") subgraph Lab Skills mysql/create_database -.-> lab-550919{{"使用 MySQL 触发器实现自动化"}} mysql/create_table -.-> lab-550919{{"使用 MySQL 触发器实现自动化"}} mysql/drop_table -.-> lab-550919{{"使用 MySQL 触发器实现自动化"}} mysql/alter_table -.-> lab-550919{{"使用 MySQL 触发器实现自动化"}} mysql/select -.-> lab-550919{{"使用 MySQL 触发器实现自动化"}} mysql/insert -.-> lab-550919{{"使用 MySQL 触发器实现自动化"}} mysql/delete -.-> lab-550919{{"使用 MySQL 触发器实现自动化"}} end

创建触发器以记录插入操作

在这一步中，你将学习如何在 MySQL 中创建一个触发器，该触发器会将插入操作记录到一个单独的表中。触发器是特殊的存储程序，会针对表上的某些事件（如 INSERT、UPDATE 或 DELETE）自动执行。这对于审计更改、实施业务规则或基于数据修改执行其他操作非常有用。

首先，让我们连接到 MySQL 服务器。打开一个终端并执行以下命令：

mysql -u root -p

系统会提示你输入 root 密码。输入密码后按回车键。

接下来，如果 mydatabase 数据库尚不存在，则创建该数据库：

CREATE DATABASE IF NOT EXISTS mydatabase;
USE mydatabase;

现在，让我们创建一个名为 products 的表：

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

我们还需要一个表来记录插入操作。让我们创建一个名为 product_logs 的表：

CREATE TABLE product_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    product_name VARCHAR(255),
    product_price DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

现在，让我们创建触发器。该触发器将在 products 表的每次插入操作之后执行。触发器会将插入产品的详细信息记录到 product_logs 表中。

CREATE TRIGGER products_after_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_logs (product_id, product_name, product_price)
    VALUES (NEW.id, NEW.name, NEW.price);
END;

让我们详细分析一下触发器的定义：

CREATE TRIGGER products_after_insert：此语句创建一个名为 products_after_insert 的触发器。
AFTER INSERT ON products：这指定了触发器将在 products 表的每次插入操作之后执行。
FOR EACH ROW：这表明触发器将针对插入到 products 表中的每一行执行。
BEGIN ... END：此代码块包含触发器激活时将执行的 SQL 语句。
NEW.id、NEW.name、NEW.price：这些引用的是新插入行的 id、name 和 price 列的值。

现在，让我们向 products 表中插入一些数据来测试触发器：

INSERT INTO products (name, price) VALUES ('Laptop', 1200.00);
INSERT INTO products (name, price) VALUES ('Mouse', 25.00);

最后，让我们查看 product_logs 表，看看触发器是否已插入日志记录：

SELECT * FROM product_logs;

你应该会在 product_logs 表中看到两条记录，对应我们在 products 表上执行的两次插入操作。

+--------+------------+--------------+---------------+---------------------+
| log_id | product_id | product_name | product_price | created_at          |
+--------+------------+--------------+---------------+---------------------+
|      1 |          1 | Laptop       |       1200.00 | 2024-10-27 12:00:00 |
|      2 |          2 | Mouse        |         25.00 | 2024-10-27 12:00:00 |
+--------+------------+--------------+---------------+---------------------+
2 rows in set (0.00 sec)

插入数据以测试触发器

在这一步中，你将向 products 表中插入数据，这将触发你在上一步中创建的 products_after_insert 触发器。这将使你能够验证触发器是否正常工作，以及它是否将预期的数据插入到 product_logs 表中。

首先，确保你已连接到 MySQL 服务器并使用 mydatabase 数据库。如果你还未连接，请打开一个终端并执行以下命令：

mysql -u root -p

出现提示时输入密码。

USE mydatabase;

现在，让我们向 products 表中插入一些数据。我们将插入三种不同名称和价格的新产品：

INSERT INTO products (name, price) VALUES ('Keyboard', 75.00);
INSERT INTO products (name, price) VALUES ('Monitor', 300.00);
INSERT INTO products (name, price) VALUES ('Headphones', 100.00);

这些 INSERT 语句将向 products 表中添加三行新数据。由于我们创建了触发器，每次插入操作都应该会在 product_logs 表中添加一行新记录。

为了验证触发器是否正常工作，让我们查询 product_logs 表，检查是否已插入新记录：

SELECT * FROM product_logs;

你应该会在 product_logs 表中看到与新插入产品对应的记录。输出应该类似于以下内容（log_id 和 created_at 的值会有所不同）：

+--------+------------+--------------+---------------+---------------------+
| log_id | product_id | product_name | product_price | created_at          |
+--------+------------+--------------+---------------+---------------------+
|      1 |          1 | Laptop       |       1200.00 | 2024-10-27 12:00:00 |
|      2 |          2 | Mouse        |         25.00 | 2024-10-27 12:00:00 |
|      3 |          3 | Keyboard     |         75.00 | 2024-10-27 12:05:00 |
|      4 |          4 | Monitor      |        300.00 | 2024-10-27 12:05:00 |
|      5 |          5 | Headphones   |        100.00 | 2024-10-27 12:05:00 |
+--------+------------+--------------+---------------+---------------------+
5 rows in set (0.00 sec)

如果你在 product_logs 表中看到了新记录，这意味着触发器正常工作。product_id 应该与 products 表中对应产品的 id 相匹配，product_name 和 product_price 也应该与你插入的值相匹配。

更新触发器逻辑

在这一步中，你将修改现有的触发器 products_after_insert，以便在 product_logs 表中包含额外的信息。具体来说，你将把当前时间戳添加到 product_logs 表中一个名为 operation_time 的新列中。

首先，你需要向 product_logs 表添加 operation_time 列。连接到 MySQL 服务器并使用 mydatabase 数据库：

mysql -u root -p

出现提示时输入密码。

USE mydatabase;

现在，向 product_logs 表添加 operation_time 列：

ALTER TABLE product_logs ADD COLUMN operation_time TIMESTAMP;

接下来，在使用更新后的逻辑重新创建触发器之前，你需要删除现有的 products_after_insert 触发器：

DROP TRIGGER IF EXISTS products_after_insert;

现在，使用更新后的逻辑重新创建触发器，以包含 operation_time。我们将在触发器执行时将 operation_time 设置为当前时间戳：

CREATE TRIGGER products_after_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_logs (product_id, product_name, product_price, operation_time)
    VALUES (NEW.id, NEW.name, NEW.price, CURRENT_TIMESTAMP);
END;

在这个更新后的触发器中：

我们在 INSERT 语句中，将 operation_time 添加到了 product_logs 表的列列表中。
我们将 operation_time 的值设置为 CURRENT_TIMESTAMP，这将在触发器执行时插入当前的日期和时间。

现在，让我们向 products 表中插入一些新数据，以测试更新后的触发器：

INSERT INTO products (name, price) VALUES ('Printer', 200.00);

最后，让我们查看 product_logs 表，看看触发器是否已插入包含 operation_time 的日志记录：

SELECT * FROM product_logs;

你应该会在 product_logs 表中看到一条新记录，其中 operation_time 列填充了当前时间戳。输出应该类似于以下内容（log_id、created_at 和 operation_time 的值会有所不同）：

+--------+------------+--------------+---------------+---------------------+---------------------+
| log_id | product_id | product_name | product_price | created_at          | operation_time      |
+--------+------------+--------------+---------------+---------------------+---------------------+
|      1 |          1 | Laptop       |       1200.00 | 2024-10-27 12:00:00 | NULL                |
|      2 |          2 | Mouse        |         25.00 | 2024-10-27 12:00:00 | NULL                |
|      3 |          3 | Keyboard     |         75.00 | 2024-10-27 12:05:00 | NULL                |
|      4 |          4 | Monitor      |        300.00 | 2024-10-27 12:05:00 | NULL                |
|      5 |          5 | Headphones   |        100.00 | 2024-10-27 12:05:00 | NULL                |
|      6 |          6 | Printer      |        200.00 | 2024-10-27 12:10:00 | 2024-10-27 12:10:00 |
+--------+------------+--------------+---------------+---------------------+---------------------+
6 rows in set (0.00 sec)

请注意，新条目的 operation_time 已填充，而旧条目的 operation_time 为 NULL，因为这些旧条目是在添加列和更新触发器之前插入的。

删除触发器

在这一步中，你将删除在前面步骤中创建并更新的触发器 products_after_insert。删除触发器会将其从数据库中移除，因此在关联事件发生时，它将不再执行。

首先，确保你已连接到 MySQL 服务器并使用 mydatabase 数据库。如果你还未连接，请打开一个终端并执行以下命令：

mysql -u root -p

出现提示时输入密码。

USE mydatabase;

要删除触发器，请使用以下 SQL 命令：

DROP TRIGGER IF EXISTS products_after_insert;

DROP TRIGGER 语句会从数据库中移除指定的触发器。IF EXISTS 子句确保如果触发器不存在，该命令不会返回错误。

要验证触发器是否已被删除，你可以使用 SHOW TRIGGERS 命令：

SHOW TRIGGERS LIKE 'products_after_insert';

如果触发器已成功删除，SHOW TRIGGERS 命令将不会返回任何结果。

让我们再向 products 表中插入一条记录，以确认触发器确实已被删除，并且不再记录新的插入操作：

INSERT INTO products (name, price) VALUES ('Webcam', 50.00);

现在，让我们查看 product_logs 表。如果触发器已成功删除，新插入的记录应该不会被记录。

SELECT * FROM product_logs;

你应该不会在 product_logs 表中看到 'Webcam' 的新记录。这证实了触发器已成功删除。

总结

在这个实验中，你学习了如何创建一个 MySQL 触发器，该触发器能自动将插入操作记录到一个单独的表中。这包括连接到 MySQL 服务器，创建一个数据库以及两个表：products 和 product_logs。products 表用于存储产品信息，而 product_logs 表则记录每条插入产品的详细信息。

这一步的核心是定义一个名为 products_after_insert 的触发器，它会在 products 表每次插入操作之后执行。该触发器会向 product_logs 表中插入一条新记录，使用 NEW 关键字捕获新插入产品的 id、name 和 price。