MySQL编程进阶之：触发器与多行操作：如何在`INSERT`、`UPDATE`和`DELETE`中正确使用触发器。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位观众老爷，大家好！今天咱们来聊聊MySQL编程里的“触发器”和“多行操作”。这俩家伙，用好了能让你的数据库飞起来，用不好嘛…嘿嘿，小心数据乱成一锅粥！

一、啥是触发器？为啥要用它？

简单来说，触发器(Trigger)就像数据库里的“闹钟”。它会在特定的数据库事件发生时自动执行一些代码。这些事件包括INSERT、UPDATE和DELETE。

想象一下，你有个电商网站，用户下单的时候，你除了要往orders表里插入一条记录，还得更新products表的库存。没触发器的时候，你得在你的应用程序代码里写一堆逻辑，确保这两件事儿一起发生。万一哪天程序员小明手一抖，只更新了订单，忘了扣库存，那就麻烦大了！

有了触发器，你就可以把更新库存的逻辑写在触发器里。当orders表里插入一条新记录时，触发器自动执行，帮你扣掉对应的库存。这样，业务逻辑和数据操作就解耦了，代码更清晰，也更不容易出错。

二、触发器的基本语法

MySQL触发器的基本语法如下：

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- 触发器要执行的代码
END;

trigger_name: 触发器的名字，随便你起，但最好起个有意义的，方便以后维护。
BEFORE | AFTER: 触发器执行的时机。BEFORE表示在事件发生之前执行，AFTER表示在事件发生之后执行。
INSERT | UPDATE | DELETE: 触发器响应的事件。
table_name: 触发器监控的表。
FOR EACH ROW: 这个是必须的，表示触发器是针对每一行记录触发的。
BEGIN ... END: 触发器要执行的代码块。

三、NEW和OLD：触发器里的“秘密武器”

在触发器里，你可以使用NEW和OLD这两个“秘密武器”来访问被操作的数据。

NEW: 在INSERT和UPDATE触发器中可用，代表新插入或更新后的行。
OLD: 在UPDATE和DELETE触发器中可用，代表旧的、被更新或删除的行。

举个例子，假设我们要创建一个触发器，在products表更新price字段时，记录一下更新前后的价格。

CREATE TRIGGER price_change_log
AFTER UPDATE
ON products
FOR EACH ROW
BEGIN
    INSERT INTO price_log (product_id, old_price, new_price, change_time)
    VALUES (OLD.id, OLD.price, NEW.price, NOW());
END;

在这个触发器里，OLD.id表示更新前的商品ID，OLD.price表示更新前的价格，NEW.price表示更新后的价格。

四、触发器与多行操作：重点来了！

触发器是FOR EACH ROW的，这意味着，即使你执行的是批量操作，比如一次性插入、更新或删除多行数据，触发器也会针对每一行数据分别执行一次。

这既是优点，也是挑战。优点是你可以精细地控制每一行数据的处理逻辑。挑战是，如果你的触发器代码写得不好，可能会导致性能问题，甚至数据不一致。

4.1 INSERT触发器与多行操作

假设我们有个orders表，记录订单信息，还有一个order_items表，记录订单里的商品明细。

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATETIME
);

CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_id INT,
    quantity INT
);

现在，我们想创建一个触发器，当往order_items表里插入数据时，自动更新products表的库存。

CREATE TRIGGER update_stock_on_order
AFTER INSERT
ON order_items
FOR EACH ROW
BEGIN
    UPDATE products
    SET stock = stock - NEW.quantity
    WHERE id = NEW.product_id;
END;

这个触发器看起来没啥问题，对吧？但是，如果用户一次性下单了多个商品，也就是往order_items表里插入了多行数据，这个触发器就会执行多次UPDATE语句，每次更新一行products表的数据。

在高并发的情况下，这可能会导致性能问题，甚至出现死锁。

优化方案：

我们可以使用SUM()函数和GROUP BY子句，将多个UPDATE语句合并成一个。

首先，创建一个临时表，存储每个商品的需要扣减的数量。

CREATE TEMPORARY TABLE temp_order_items (
    product_id INT,
    quantity INT
);

然后，修改触发器代码如下：

CREATE TRIGGER update_stock_on_order_optimized
AFTER INSERT
ON order_items
FOR EACH ROW
BEGIN
    INSERT INTO temp_order_items (product_id, quantity)
    VALUES (NEW.product_id, NEW.quantity);

    UPDATE products
    SET stock = stock - (SELECT SUM(quantity) FROM temp_order_items WHERE product_id = products.id)
    WHERE id IN (SELECT product_id FROM temp_order_items);

    DELETE FROM temp_order_items; -- 清空临时表
END;

解释：

每次插入order_items记录时，将product_id和quantity插入到temp_order_items临时表。
使用一个UPDATE语句，基于temp_order_items表汇总每个product_id的总quantity，并一次性更新products表的库存。
清空temp_order_items临时表，为下次触发做准备。

注意: 临时表只在当前会话有效, 会话结束后会自动删除, 所以不用担心长期占用资源。

4.2 UPDATE触发器与多行操作

假设我们有个employees表，记录员工信息，包括薪水。

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    salary DECIMAL(10, 2)
);

现在，我们要创建一个触发器，当员工薪水发生变化时，记录一下变化前后的薪水。

CREATE TABLE salary_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    change_time DATETIME
);

CREATE TRIGGER log_salary_change
AFTER UPDATE
ON employees
FOR EACH ROW
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_history (employee_id, old_salary, new_salary, change_time)
        VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
    END IF;
END;

这个触发器看起来也没啥问题，对吧？但是，如果我们需要批量更新员工薪水，比如给所有员工涨薪10%，这个触发器就会执行多次INSERT语句，每次插入一行salary_history表的数据。

优化方案：

对于UPDATE触发器，优化方案与INSERT类似，也可以使用临时表来批量处理。不过，由于UPDATE触发器有OLD和NEW两个值，处理起来更复杂一些。

首先，创建临时表：

CREATE TEMPORARY TABLE temp_salary_changes (
    employee_id INT,
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2)
);

然后，修改触发器代码：

CREATE TRIGGER log_salary_change_optimized
AFTER UPDATE
ON employees
FOR EACH ROW
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO temp_salary_changes (employee_id, old_salary, new_salary)
        VALUES (OLD.id, OLD.salary, NEW.salary);
    END IF;

    -- 注意: 这里不能直接使用 temp_salary_changes 表进行查询,
    -- 因为触发器是针对每一行执行的, temp_salary_changes 会不断增加.
    -- 需要在外部执行一次批量插入.
END;

这个触发器只是将需要记录的薪水变化信息插入到临时表temp_salary_changes中。关键在于，不能在触发器内部直接使用临时表进行批量插入salary_history表的操作。

正确的做法是，在触发器执行完毕后，在外部执行一次批量插入操作：

INSERT INTO salary_history (employee_id, old_salary, new_salary, change_time)
SELECT employee_id, old_salary, new_salary, NOW()
FROM temp_salary_changes;

DROP TEMPORARY TABLE IF EXISTS temp_salary_changes;

解释：

触发器将所有发生薪水变化的员工信息（employee_id, old_salary, new_salary）插入到临时表temp_salary_changes中。
触发器执行完毕后，在应用程序代码中执行一条INSERT ... SELECT语句，从temp_salary_changes表中读取数据，批量插入到salary_history表中。
删除临时表temp_salary_changes。

关键点： UPDATE触发器的优化，通常需要配合应用程序代码，将批量操作放在触发器外部执行。触发器负责收集需要修改的数据，应用程序负责最终的批量更新/插入。

4.3 DELETE触发器与多行操作

假设我们有个users表，记录用户信息，还有一个user_logs表，记录用户操作日志。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255),
    email VARCHAR(255)
);

CREATE TABLE user_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(255),
    log_time DATETIME
);

现在，我们要创建一个触发器，当用户被删除时，记录一条删除日志。

CREATE TRIGGER log_user_deletion
AFTER DELETE
ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_logs (user_id, action, log_time)
    VALUES (OLD.id, 'User deleted', NOW());
END;

和前面的情况类似，如果我们需要批量删除用户，这个触发器就会执行多次INSERT语句。

优化方案：

DELETE触发器的优化思路与UPDATE类似，也是借助临时表，并在触发器外部执行批量插入操作。

首先，创建临时表：

CREATE TEMPORARY TABLE temp_deleted_users (
    user_id INT
);

然后，修改触发器代码：

CREATE TRIGGER log_user_deletion_optimized
AFTER DELETE
ON users
FOR EACH ROW
BEGIN
    INSERT INTO temp_deleted_users (user_id)
    VALUES (OLD.id);
END;

这个触发器只是将被删除的user_id插入到临时表temp_deleted_users中。

同样，不能在触发器内部直接使用临时表进行批量插入user_logs表的操作。

正确的做法是，在触发器执行完毕后，在外部执行一次批量插入操作：

INSERT INTO user_logs (user_id, action, log_time)
SELECT user_id, 'User deleted', NOW()
FROM temp_deleted_users;

DROP TEMPORARY TABLE IF EXISTS temp_deleted_users;

解释：

触发器将被删除的用户的ID插入到临时表temp_deleted_users。
触发器执行完毕后，在应用程序代码中执行一条INSERT ... SELECT语句，从temp_deleted_users表中读取数据，批量插入到user_logs表中。
删除临时表temp_deleted_users。

关键点： DELETE触发器的优化，也需要配合应用程序代码，将批量操作放在触发器外部执行。触发器负责收集被删除用户的ID，应用程序负责最终的批量插入日志。

五、总结

触发器是数据库里的“闹钟”，在特定事件发生时自动执行代码。
NEW和OLD是触发器里的“秘密武器”，可以访问被操作的数据。
触发器是FOR EACH ROW的，多行操作会触发多次触发器执行。
对于多行操作，可以使用临时表和GROUP BY子句优化INSERT触发器。
对于多行操作，UPDATE和DELETE触发器的优化，需要配合应用程序代码，将批量操作放在触发器外部执行。

六、一些额外的注意事项

小心循环触发： 触发器可能会触发其他触发器，如果设计不当，可能会导致循环触发，让数据库陷入死循环。
控制触发器的复杂性： 触发器里的代码越复杂，越容易出错，也越影响性能。尽量让触发器只做一些简单的事情，复杂的逻辑放到应用程序里。
测试，测试，再测试： 触发器很容易被忽略，所以在上线之前一定要进行充分的测试，确保它能正常工作。
避免在触发器中使用事务控制语句（COMMIT, ROLLBACK）：触发器本身就处于一个事务上下文中，再使用事务控制语句可能会导致问题。
考虑使用存储过程：如果触发器的逻辑比较复杂，可以考虑把它封装成一个存储过程，然后在触发器里调用存储过程。

好了，今天的讲座就到这里。希望大家能够掌握触发器和多行操作的正确使用方法，让你的数据库飞起来！如果还有什么疑问，欢迎在评论区留言，咱们一起讨论！

发表回复 取消回复

发表回复取消回复