上面的优化方案已经使用了 JSON_TABLE() 函数将原始数据从 JSON 格式中提取出来,并插入到 log 表中。但是,如果表中有大量的字段,手动指定每一个字段是比较麻烦的,而且容易出错。
为了更好地优化这个 INSERT 语句,我们可以使用 MySQL 8.0 引入的 INFORMATION_SCHEMA.COLUMNS 表来获取目标表的所有列名,然后使用动态 SQL 语句构造 JSON 对象。这样可以避免手动指定每一个字段,提高代码的可读性和可维护性。
例如,我们可以将上面的 INSERT 语句优化为:
```
SET @sql = CONCAT(
'INSERT INTO log (table_name, action_type, old_data, new_data) ',
'SELECT ''target_table'', ''UPDATE'', old_data, new_data ',
'FROM JSON_TABLE(',
' JSON_OBJECT(',
' ''old_data'', JSON_OBJECT(',
(SELECT GROUP_CONCAT(CONCAT(' ''', column_name, ''', OLD.', column_name) SEPARATOR ',\n') FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'target_table'),
' ),',
' ''new_data'', JSON_OBJECT(',
(SELECT GROUP_CONCAT(CONCAT(' ''', column_name, ''', NEW.', column_name) SEPARATOR ',\n') FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'target_table'),
' )',
' ),',
' ''$.*'' COLUMNS(',
' old_data JSON PATH ''$.old_data'',',
' new_data JSON PATH ''$.new_data''',
' )',
') AS t;'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
```
这个语句中,我们使用 INFORMATION_SCHEMA.COLUMNS 表获取目标表的所有列名,并使用 GROUP_CONCAT() 函数将列名拼接为一个字符串。然后,使用 CONCAT() 函数构造 JSON 对象,并将其动态插入到 INSERT 语句中。最后,使用 PREPARE 和 EXECUTE 语句执行动态 SQL 语句。
这样,就可以避免手动指定每一个字段,提高代码的可读性和可维护性。