mysql - 当新记录插入另一个表时更新计数器字段

Question

我有两个表，当在表B上插入或删除新记录时，我想更新表A的计数器记录字段，代码是相同的。

表A字段

ID-代码计数器等..

表 B 字段

ID-代码等..

当在表 B 上插入/删除新记录时，如果它们具有相同的 CODE 字段，我想在表 A 计数器字段上对其进行计数。

我想这可以通过触发器来完成，但我需要一些帮助。

Answer 1

我们或许可以使用 MySQL 触发器来实现。

我们应该考虑选择不将计数器存储在 table_a 上。 ，而只是从 table_b 获取计数。

在实现触发器时我们还需要注意一些问题。这会产生潜在的性能影响，并且对于执行 DML 的触发器，有可能会引入锁争用。触发器不是免费的，也不是“ Elixir ”。

我们需要一个AFTER UPDATE触发器(处理更新行以更改 code 列的值的情况)，以及 AFTER INSERT和AFTER DELETE触发器。

我们需要在 table_b 上有一个合适的索引，例如

 ON table_a (code)

假设table_a counter列不为 NULL，并且 counter被初始化为我们期望的值。

作为第一次剪辑:

--删除后

DELIMITER $$

CREATE TRIGGER table_b_ad
AFTER DELETE ON table_b
FOR EACH ROW
BEGIN
    -- decrement counter on row of table_a with matching OLD code
    UPDATE table_a a
       SET a.counter = a.counter - 1
     WHERE a.code <=> OLD.code
    ;
END$$

DELIMITER ;

--插入后

DELIMITER $$

CREATE TRIGGER table_b_ai
AFTER INSERT ON table_b
FOR EACH ROW
BEGIN
    -- increment counter on row of table_a with matching NEW code
    UPDATE table_a a
       SET a.counter = a.counter + 1
     WHERE a.code <=> NEW.code
    ;
END$$

DELIMITER ;

--更新后

DELIMITER $$

CREATE TRIGGER table_b_au
AFTER UPDATE ON table_b
FOR EACH ROW
BEGIN
    IF NOT ( NEW.code <=> OLD.code ) THEN
       -- decrement counter on row of table_a that matches OLD code
       UPDATE table_a a
          SET a.counter = a.counter - 1
        WHERE a.code <=> OLD.code
       ;
       -- increment counter on row of table_a that matches NEW code
       UPDATE table_a a
          SET a.counter = a.counter + 1
        WHERE a.code <=> NEW.code
       ;
    END IF; 
END$$

DELIMITER ;

注意:

在 MySQL 触发器内，

NEW.code指分配给列 code 的"new"值通过 INSERT 或 UPDATE 语句。

OLD.code指 code 列中的“旧”值在 UPDATE 或 DELETE 语句之前。

<=> (spaceship 运算符) 是 NULL 安全比较运算符，返回 TRUE 或 FALSE。不像常规比较运算符在比较 NULL 值时返回 NULL。

x <=> y速记相当于写 ( x = y OR ( x IS NULL AND y IS NULL ) )

在UPDATE触发器中进行比较的目的是如果code的值没有改变，没有必要执行从计数中减 1 的繁琐操作 code值，然后立即将 1 添加回相同的计数。避免运行不必要的 UPDATE 语句会更有效。