MySQL触发器中能否动态引用NEW/OLD值实现行版本控制？

我需要通过数据库触发器实现行版本控制，现有两张表：

• addresses（id, city, street, country）
• address_versions（id, object_changes）

每次addresses表中的行发生变更时，要在address_versions表生成一条记录。我写了如下触发器：

CREATE TRIGGER `addresses_after_create` AFTER INSERT ON `addresses` FOR EACH ROW
    this_trigger:BEGIN
    DECLARE object_changes_json JSON;

    -- Prepare object_changes JSON for address_versions row creation
    SET object_changes_json = JSON_OBJECT();

    IF COALESCE(OLD.city, '') <> COALESCE(NEW.city, '') THEN
        SET object_changes_json = JSON_SET(object_changes_json, '$.city', JSON_ARRAY(OLD.city, NEW.city));
    END IF;

    IF COALESCE(OLD.street, '') <> COALESCE(NEW.street, '') THEN
        SET object_changes_json = JSON_SET(object_changes_json, '$.street', JSON_ARRAY(OLD.street, NEW.street));
    END IF;

    IF COALESCE(OLD.country, '') <> COALESCE(NEW.country, '') THEN
        SET object_changes_json = JSON_SET(object_changes_json, '$.country', JSON_ARRAY(OLD.country, NEW.country));
    END IF;

    INSERT INTO address_versions (object_changes)
    VALUES (object_changes_json);
END;

这个方案能实现需求，但每次给addresses表添加或删除列时，都得手动更新触发器，太麻烦。我尝试过用存储列名的表结合循环遍历，但无法动态访问NEW/OLD值：

...
    read_loop: LOOP
        FETCH cursor_column_name INTO column_name_string;
        IF done THEN
            LEAVE read_loop;
        END IF;
        IF COALESCE(OLD.column_name_string, '') <> COALESCE(NEW.column_name_string, '') THEN
            SET object_changes_json = JSON_SET(object_changes_json, '$.additional_info', JSON_ARRAY(OLD.column_name_string, NEW.column_name_string));
        END IF;
    END LOOP;
...

执行后报错：

=> ERROR 1054 (42S22): Unknown column 'column_name_string' in 'OLD'

想问有没有方法可以动态引用NEW/OLD值？

在MySQL中，触发器无法直接通过变量动态引用OLD/NEW的列，但可以通过存储过程+动态列遍历+JSON转换的组合实现完全动态的行版本控制，无需在表结构变更时修改触发器或存储过程。

核心思路 #

1. 利用MySQL 8.0+的CAST(OLD AS JSON)/CAST(NEW AS JSON)特性，直接将整行数据转为JSON格式，避免手动拼接列名。
2. 通过information_schema.columns动态获取目标表的列列表，遍历对比新旧JSON中的列值差异。
3. 将差异整理为指定格式的JSON，插入版本表。

具体实现 #

1. 创建通用版本生成存储过程

DELIMITER //
CREATE PROCEDURE generate_address_version(
    IN p_old_row JSON,
    IN p_new_row JSON
)
BEGIN
    DECLARE v_column_name VARCHAR(255);
    DECLARE v_old_val JSON;
    DECLARE v_new_val JSON;
    DECLARE v_changes JSON DEFAULT JSON_OBJECT();
    DECLARE @done BOOL DEFAULT FALSE;

    -- 动态获取addresses表的非主键列
    DECLARE cur_columns CURSOR FOR
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_schema = DATABASE() 
          AND table_name = 'addresses'
          AND column_name != 'id'; -- 主键无需追踪变更

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @done = TRUE;

    OPEN cur_columns;
    read_loop: LOOP
        FETCH cur_columns INTO v_column_name;
        IF @done THEN
            LEAVE read_loop;
        END IF;

        -- 从JSON中提取新旧列值
        SET v_old_val = JSON_EXTRACT(p_old_row, CONCAT('$.', v_column_name));
        SET v_new_val = JSON_EXTRACT(p_new_row, CONCAT('$.', v_column_name));

        -- 对比值（覆盖NULL与非NULL、值不等的情况）
        IF (v_old_val IS NULL XOR v_new_val IS NOT NULL) 
           OR (v_old_val IS NOT NULL AND v_old_val <> v_new_val) THEN
            SET v_changes = JSON_SET(
                v_changes, 
                CONCAT('$.', v_column_name), 
                JSON_ARRAY(v_old_val, v_new_val)
            );
        END IF;
    END LOOP;
    CLOSE cur_columns;

    -- 仅当存在变更时插入版本记录
    IF JSON_LENGTH(v_changes) > 0 THEN
        INSERT INTO address_versions (object_changes) VALUES (v_changes);
    END IF;
END //
DELIMITER ;

2. 创建动态触发器

利用行转JSON特性，触发器无需硬编码列名，表结构变更时无需修改：

-- 更新触发器
DELIMITER //
CREATE TRIGGER addresses_after_update AFTER UPDATE ON addresses FOR EACH ROW
BEGIN
    CALL generate_address_version(CAST(OLD AS JSON), CAST(NEW AS JSON));
END //
DELIMITER ;

-- 插入触发器（记录初始值，旧值为空JSON）
DELIMITER //
CREATE TRIGGER addresses_after_insert AFTER INSERT ON addresses FOR EACH ROW
BEGIN
    CALL generate_address_version(JSON_OBJECT(), CAST(NEW AS JSON));
END //
DELIMITER ;

为什么你的尝试失败 #

OLD.column_name_string这种写法是错误的，因为OLD是行对象，MySQL会把column_name_string当作列名而非变量。必须通过JSON提取或动态SQL拼接的方式间接获取列值，而上述方案通过行转JSON完美规避了这个问题。

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