MYSQL更新触发器检查所有列的变化并将值插入到其他表

Question

我已经找到这段代码了，但是我必须手动插入所有列并逐列检查

CREATE TRIGGER table_a_update BEFORE UPDATE ON table_a
FOR EACH ROW     
BEGIN
   IF (NEW.column1 <=> OLD.column1) 
   THEN
       INSERT INTO table_updates(table, object_id, user_id, column, old_value, new_value)
       VALUES ("table_a",NEW.id, NEW.user_id, "column1", OLD.column1, new.column1)
   END IF;
   IF (NEW.column2 <=> OLD.column2) 
   THEN
       INSERT INTO table_updates(table, object_id, user_id, column, old_value, new_value)
       VALUES ("table_a",NEW.id, NEW.user_id, "column2", OLD.column2, new.column2)
   END IF;
   (other columns... ifs)
END;

我想知道如何从 NEW 中获取所有与 OLD 列不同的列，然后通过像这个示例一样使用 php 将其插入到我不知道的内容中:

BEGIN
    for(columns(NEW) as $column){
        IF (NEW.$column <=> OLD.$column)
        THEN
            INSERT INTO table_updates(table, object_id, user_id,column, old_value, new_value)
            VALUES ("table_a", NEW.id, NEW.user_id, $column, $OLD[$column], $NEW[$column])
        END IF;
    }
END;

Answer 1

IIUC:

DELIMITER $$
CREATE TRIGGER before_update_table_a
BEFORE UPDATE ON table_a 
FOR EACH ROW BEGIN

    IF (NEW.col1 <> OLD.col1) THEN
        INSERT INTO table_updates (col1, col2, ... 
        VALUES (OLD.col1, NEW.col2 ...
    END IF;

    IF (NEW.col2 <> OLD.col2) THEN
        INSERT INTO table_updates (col1, col2, ... 
        VALUES (NEW.col1, OLD.col2 ...
    END IF;

    ... 

END $$
DELIMITER ;

我相信您需要在此触发器中使用 DELIMITER 关键字，如果您想阅读更多相关信息，请查看 this问题。