比较2个表，并跟踪更改的列和其他置为null的列。
- 使用LEFT JOIN和CASE语句将2个表连接在一起，并基于每列的值判断它们是否相等：
SELECT table1.column1, CASE WHEN table1.column2 <> table2.column2 THEN CONCAT('changed from ', table2.column2, ' to ', table1.column2) WHEN table2.column2 IS NULL THEN CONCAT(table1.column2, ' set to NULL') ELSE NULL END AS column2, CASE WHEN table1.column3 <> table2.column3 THEN CONCAT('changed from ', table2.column3, ' to ', table1.column3) WHEN table2.column3 IS NULL THEN CONCAT(table1.column3, ' set to NULL') ELSE NULL END AS column3, ... FROM table1 LEFT JOIN table2 ON table1.primaryKey = table2.primaryKey;
- 使用MySQL的TRIGGER操作，当一个表更新后自动将更改的列和其他被设置为NULL的列插入到另一个表中。例如：
CREATE TRIGGER table1_after_update AFTER UPDATE ON table1
FOR EACH ROW
INSERT INTO table1_changes (primaryKey, columnName, oldValue, newValue)
VALUES (NEW.primaryKey, 'column1', OLD.column1, NEW.column1),
(NEW.primaryKey, 'column2', IFNULL(OLD.column2, 'NULL'), IFNULL(NEW.column2, 'NULL')),
(NEW.primaryKey, 'column3', IFNULL(OLD.column3, 'NULL'), IFNULL(NEW.column3, 'NULL')),
... ;