如何从MySQL表行生成JSON_OBJECT 适配触发器非动态SQL使用场景

问题原因 #

你写的SQL无法正常执行的核心原因是：静态SQL语句中，列名、表名这类标识符是在查询解析阶段就确定的，你从information_schema取到的COLUMN_NAME是字符串值，无法在后续执行阶段被当作动态列名解析取值，所以内层子查询永远返回的是COLUMN_NAME的字符串本身，不是对应列的实际值。

由于MySQL触发器不支持执行动态SQL，运行时动态拼接列名的方案完全不可用，下面分场景给你可落地的通用解决方案：

方案1：MySQL 8.0.19及以上版本（最优解） #

该版本开始原生支持直接将行记录传入JSON_OBJECT生成整行JSON，不需要手动指定任何列名，直接在触发器中写即可：

-- 生成NEW行（触发器触发时插入/更新后的行）所有字段的键值对JSON
SET row_json = JSON_OBJECT(NEW.*);
-- 如果需要取更新前的行，用JSON_OBJECT(OLD.*)即可

该方案完全符合触发器语法限制，没有额外兼容问题，只要版本达标优先使用。

方案2：MySQL 5.7版本（预生成触发器代码，免手写字段） #

如果版本不支持上面的语法，你可以通过information_schema批量生成触发器的完整代码，不需要手动逐个写字段：

步骤1：调整会话参数（避免字段过多时内容截断） #

执行生成逻辑前先运行：

SET SESSION group_concat_max_len = 102400;

步骤2：生成单表触发器代码 #

将下面SQL中的tbl_name替换为你的目标表名，执行后就能直接拿到可用的触发器代码：

SELECT CONCAT(
'CREATE TRIGGER trg_', TABLE_NAME, '_after_insert AFTER INSERT ON ', TABLE_NAME, ' FOR EACH ROW
BEGIN
    DECLARE row_json JSON;
    -- 自动拼接所有字段生成JSON
    SET row_json = JSON_OBJECT(',
    GROUP_CONCAT(QUOTE(COLUMN_NAME), ', NEW.', COLUMN_NAME ORDER BY ORDINAL_POSITION),
');
    -- 这里替换为你自己的业务逻辑，比如将JSON写入日志表
    INSERT INTO data_log (table_name, record_id, content) VALUES (', QUOTE(TABLE_NAME), ', NEW.id, row_json);
END;'
) AS trigger_sql
FROM information_schema.columns
WHERE TABLE_NAME = 'tbl_name'
  AND TABLE_SCHEMA = DATABASE()
GROUP BY TABLE_NAME;

批量生成多表触发器 #

如果需要一次生成多张表的触发器，只要将WHERE条件中的TABLE_NAME = 'tbl_name'修改为TABLE_NAME IN ('表1', '表2', '表3')即可，一次执行就能拿到所有表的触发器代码。

内容的提问来源于stack exchange，提问作者Lars