发布于 由admin

MySQL编程进阶之：`FOR EACH ROW`的深层理解：触发器如何对每一行数据进行操作。

各位观众老爷们，晚上好！我是老码农，今儿咱们聊聊MySQL触发器里一个相当重要的东东：FOR EACH ROW。

触发器：数据库的卫士

在正式进入FOR EACH ROW的深水区之前，咱们先简单回顾一下触发器。你可以把触发器想象成数据库的“卫士”，它会在特定的数据库事件（比如INSERT、UPDATE、DELETE）发生时自动执行一段预先定义好的代码。这就像是你设置了一个闹钟，当时间到了，闹钟就会自动响起来。

触发器有几个重要的特点：

  • 自动执行: 不需要你手动去调用，数据库会替你操心。
  • 基于事件: 只有当特定的数据库事件发生时才会触发。
  • 预先定义: 触发器执行的代码需要事先写好并保存到数据库中。

FOR EACH ROW：逐行扫描的利器

现在，咱们来重点说说FOR EACH ROW。在MySQL触发器中，FOR EACH ROW是一个关键性的子句，它决定了触发器内的代码是对每一行受影响的数据进行操作，还是只执行一次。

简单来说，如果你的触发器用到了FOR EACH ROW，那么它就是一个行级触发器。这意味着，每当有一行数据被INSERT、UPDATE或DELETE时，触发器内的代码就会执行一次。

如果没有FOR EACH ROW，那就是语句级触发器。语句级触发器只在整个SQL语句执行前后执行一次，不管这条语句影响了多少行数据。MySQL 5.1.6之前只支持语句级触发器，之后才引入了行级触发器。

举个栗子：

假设咱们有一个名为products的表，记录了产品的库存信息：

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255) NOT NULL,
    stock_quantity INT NOT NULL
);

INSERT INTO products (product_name, stock_quantity) VALUES
('鼠标', 100),
('键盘', 50),
('显示器', 20);

现在，我们想创建一个触发器，每当products表中的stock_quantity被更新时，就记录一条日志到另一个名为product_logs的表里。product_logs表结构如下：

CREATE TABLE product_logs (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    old_quantity INT NOT NULL,
    new_quantity INT NOT NULL,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

下面是使用FOR EACH ROW的行级触发器代码：

CREATE TRIGGER products_after_update
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_logs (product_id, old_quantity, new_quantity)
    VALUES (OLD.product_id, OLD.stock_quantity, NEW.stock_quantity);
END;

代码解释：

  • CREATE TRIGGER products_after_update: 创建一个名为products_after_update的触发器。
  • AFTER UPDATE ON products: 这个触发器会在products表发生UPDATE事件之后执行。
  • FOR EACH ROW: 关键所在！表示这是一个行级触发器，触发器内的代码会对每一行被更新的数据执行一次。
  • BEGIN ... END: 定义了触发器内部要执行的代码块。
  • INSERT INTO product_logs ...: 将更新信息插入到product_logs表中。
  • OLD.product_id, OLD.stock_quantity, NEW.stock_quantity: OLD和NEW是触发器中非常重要的关键字。OLD引用了更新之前的行数据，NEW引用了更新之后的行数据。在这里，OLD.product_id表示更新之前行的product_id，OLD.stock_quantity表示更新之前行的stock_quantity，NEW.stock_quantity表示更新之后行的stock_quantity。

如果没有FOR EACH ROW会怎样？

如果去掉FOR EACH ROW，那么这个触发器就是一个语句级触发器，MySQL会报错，因为在语句级触发器中，OLD和NEW是不允许使用的。语句级触发器是在整个update语句执行完毕后执行一次， 它没有行的概念，所以无法访问OLD和NEW。

测试一下：

现在，我们来更新products表中的数据：

UPDATE products SET stock_quantity = stock_quantity - 5 WHERE product_name = '鼠标';
UPDATE products SET stock_quantity = stock_quantity + 10 WHERE product_name = '键盘';

这两条UPDATE语句分别影响了一行数据。因为我们创建的是行级触发器，所以触发器内的代码会被执行两次。

让我们查询一下product_logs表：

SELECT * FROM product_logs;

你会看到两条记录，分别对应于鼠标和键盘的更新操作。

重要提示：

  • OLD和NEW的可用性取决于触发器的类型。
    • INSERT触发器：只有NEW可用，因为插入之前没有旧数据。
    • UPDATE触发器：OLD和NEW都可用。
    • DELETE触发器：只有OLD可用，因为删除之后没有新数据。

FOR EACH ROW的应用场景

FOR EACH ROW在触发器中应用非常广泛，以下是一些常见的场景：

  • 审计日志: 记录数据的变更历史，就像我们上面演示的例子。
  • 数据验证: 在数据插入或更新之前，对数据进行验证，确保数据的有效性。
  • 自动计算: 根据其他列的值自动计算某些列的值。
  • 级联更新/删除: 当父表中的数据被更新或删除时，自动更新或删除子表中的相关数据。
  • 维护数据一致性: 确保不同表之间的数据一致性。

再来一个栗子：数据验证

假设我们想创建一个触发器，确保products表中的stock_quantity永远不会小于0。

CREATE TRIGGER products_before_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.stock_quantity < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存数量不能小于0';
    END IF;
END;

代码解释：

  • BEFORE UPDATE ON products: 这个触发器会在products表发生UPDATE事件之前执行。
  • IF NEW.stock_quantity < 0 THEN ... END IF: 判断更新后的stock_quantity是否小于0。
  • SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存数量不能小于0': 如果stock_quantity小于0，则抛出一个SQL异常，阻止更新操作。

现在，如果我们尝试将鼠标的stock_quantity更新为-10，就会收到一个错误：

UPDATE products SET stock_quantity = -10 WHERE product_name = '鼠标';
-- 错误代码：1644。 库存数量不能小于0

FOR EACH ROW的性能考量

虽然FOR EACH ROW非常强大，但在使用时也需要注意性能问题。因为行级触发器会对每一行数据执行一次代码，如果你的UPDATE、INSERT或DELETE语句影响了大量的数据，那么触发器的执行可能会消耗大量的资源，导致性能下降。

一些建议：

  • 尽量避免在触发器中执行复杂的逻辑。 如果可能，将复杂的逻辑移到应用程序层或者存储过程中执行。
  • 避免在触发器中执行大量的查询操作。 查询操作是比较耗时的，尽量减少查询次数。
  • 谨慎使用触发器。 只有在必要的时候才使用触发器，不要滥用。

OLD和NEW的更多用法

OLD和NEW不仅仅可以用来读取数据，还可以用来修改数据（仅限于BEFORE UPDATE触发器）。

栗子：自动更新商品名称

假设我们想创建一个触发器，在更新products表时，如果商品名称包含"旧"，则自动将其替换为"新"。

CREATE TRIGGER products_before_update_name
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.product_name LIKE '%旧%' THEN
        SET NEW.product_name = REPLACE(NEW.product_name, '旧', '新');
    END IF;
END;

现在，如果我们将鼠标的product_name更新为"旧鼠标"，触发器会自动将其修改为"新鼠标"。

UPDATE products SET product_name = '旧鼠标' WHERE product_id = 1;
SELECT * FROM products WHERE product_id = 1;
-- product_name 会变成 '新鼠标'

注意：

  • 你只能在BEFORE UPDATE触发器中修改NEW的值。在AFTER UPDATE触发器中，NEW是只读的。
  • 你不能修改OLD的值。

总结

FOR EACH ROW是MySQL触发器中非常重要的一个概念，它允许我们对每一行受影响的数据进行操作。掌握FOR EACH ROW的用法，可以帮助我们编写更加灵活和强大的触发器，实现各种各样的数据库自动化任务。但是，在使用FOR EACH ROW时，也要注意性能问题，避免滥用。记住，能力越大，责任越大！

总而言之，FOR EACH ROW使得触发器从简单的“语句级”操作升级到了细粒度的“行级”操作，极大地扩展了触发器的应用范围。理解并熟练运用FOR EACH ROW，是MySQL编程进阶的关键一步。

希望今天的讲解能对大家有所帮助。有问题欢迎提问，老码农随时恭候！

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注