Поле счетчика обновления, когда новая запись вставлена ​​в другую таблицу

Вероятно, этого можно было бы добиться с помощью триггеров MySQL.

Мы должны рассмотреть вариант, когда нет хранит счетчик на table_a, а вместо этого просто получает счетчик от table_b.

Есть также некоторые проблемы, о которых нам нужно знать при реализации триггеров. Это может повлиять на производительность, а для триггеров, выполняющих DML, существует вероятность конкуренции за блокировку. Триггеры не бесплатны и не являются «волшебной пулей».

Нам нужен триггер AFTER UPDATE (для обработки случаев, когда строка обновляется для изменения значения столбца code), а также триггеры AFTER INSERT и AFTER DELETE.

Нам понадобится подходящий индекс для table_b, например

 ON table_a (code)

Предполагая, что столбец counter table_a НЕ ПУСТО (NOT 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 инструкциями INSERT или UPDATE.

OLD.code относится к «старому» значению, которое было в столбце code перед оператором UPDATE или DELETE.

<=> (оператор космического корабля) является NULL-безопасным оператором сравнения, возвращает либо ИСТИНА, либо ЛОЖЬ. Не возвращает NULL, как это делает обычный оператор сравнения при сравнении значения NULL.

x <=> y - это сокращенный эквивалент записи ( x = y OR ( x IS NULL AND y IS NULL ) )

Цель сравнения в триггере UPDATE заключается в том, что, если значение code не изменилось, нет необходимости вычитать 1 из счетчика для этого значения code, а затем немедленно прибавлять 1 к тому же самому считать. Более эффективно просто избегать ненужных операторов UPDATE.