mysql - 在更新后在 MySQL 触发器中构建 JSON

Question

如果能获得一些帮助来在 MySQL 5.6 中实现触发器来记录更新的信息，那就太好了。

首先，这是我的数据表和日志表:

CREATE TABLE t1
(id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
 data1 VARCHAR(255) NOT NULL,
 data2 DECIMAL(5,2) NOT NULL
);

CREATE TABLE t1_log
(action VARCHAR(10) NOT NULL,
 timestamp TIMESTAMP NOT NULL,
 id INTEGER NOT NULL,
 data1 VARCHAR(255) NOT NULL,
 data2 DECIMAL(5,2) NOT NULL,
 data1_old VARCHAR(255),
 data2_old DECIMAL(5,2)
);

这是我的触发器:

CREATE TRIGGER after_update_t1 AFTER UPDATE ON t1
 FOR EACH ROW
 BEGIN
   INSERT INTO t1_log (action,timestamp,id,data1,data2,data1_old,data2_old)
   VALUES ('update',NOW(),NEW.id,NEW.data1,NEW.data2,OLD.data1,OLD.data2);
 END;

这可以正常工作，没有任何问题。但这个解决方案的缺点是我需要为每个数据表一个日志表。所以我考虑了一种可能性:只有一个带有 BLOB 字段的日志表，将更新的字段组合成一个 JSON 字符串并将其存储在这个 BLOB 中。但如何为此定义触发器？

在伪代码中，它看起来像这样:

BEGIN
  SET jsonString = "{"
  FOR EACH field in NEW
     jsonString += field.name + ":" + field.value + ","
  END FOR
  FOR EACH field in OLD
     jsonString += field.name + ":" + field.value + ","
  END FOR
  jsonString = TRIM(jsonString, ",") + "}"
  INSERT INTO log (action,timestamp,jsondata)
  VALUES ('update',NOW(),jsonString)
END

Answer 1

如果是我并且有此要求，我只会使用现有的审核日志插件之一。例如:https://www.percona.com/doc/percona-server/LATEST/management/audit_log_plugin.html

如果您无法使用审核日志插件，或者您只想记录自己的 JSON，至少避免通过字符串连接构建 JSON 值。这太容易出错了。

相反，请使用函数 JSON_OBJECT() 。这在 MySQL 5.7 及更高版本中可用。如果您使用的是早期版本的 MySQL 并且想要使用 JSON 数据，则应该升级。

INSERT INTO log SET action = 'update', timestamp = NOW(),
  jsondata = JSON_OBJECT(
    'id', NEW.id,
    'data1', NEW.data1,
    'data2', NEW.data2
  );

我也不建议尝试创建可用于任何表的单个通用触发器。无论如何，您都需要为要记录的每个表定义触发器，因此您应该使用相应表的列自定义触发器。

您可能会想使用 INFORMATION_SCHEMA.COLUMNS 来收集表的列列表，但请记住，每次对基表执行 INSERT 或 UPDATE 操作时，这都会执行查询 INFORMATION_SCHEMA.COLUMNS 的工作。这是大量的查询，并且会导致性能问题。