MySQL历史表搭建及后端数据更新同步历史表实现方案咨询

一、Java后端统一拦截方案 #

1. 基于AOP切面的业务方法拦截 #

这种方式通过切面统一拦截需要记录历史的业务操作，步骤如下：

• 定义自定义注解（如@RecordHistory），标记需要生成历史记录的Service或Mapper方法，注解可携带表名等元信息
• 编写AOP切面类，拦截带有该注解的方法，通过环绕通知获取操作前后的数据：
  • 执行方法前，针对更新/删除操作提前查询旧数据
  • 执行方法后，获取新增/更新操作的新数据
  • 将表名、操作类型、新旧数据、操作人、时间等封装为历史记录实体，写入历史表
• 示例代码片段：

@Aspect
@Component
public class DataHistoryAspect {
    @Autowired
    private HistoryRecordDao historyRecordDao;

    @Around("@annotation(recordHistory)")
    public Object recordHistory(ProceedingJoinPoint joinPoint, RecordHistory recordHistory) throws Throwable {
        Object[] args = joinPoint.getArgs();
        Object oldData = null;
        // 针对更新/删除操作，提前查询旧数据
        if (isUpdateOrDeleteMethod(joinPoint.getSignature().getName())) {
            oldData = queryOldData(recordHistory.tableName(), args);
        }
        // 执行原业务方法
        Object result = joinPoint.proceed();
        // 构造并插入历史记录
        HistoryRecord record = new HistoryRecord();
        record.setTableName(recordHistory.tableName());
        record.setOperType(getOperType(joinPoint.getSignature().getName()));
        record.setOldData(oldData != null ? JSON.toJSONString(oldData) : null);
        record.setNewData(getNewData(args));
        record.setOperator(getCurrentLoginUser());
        record.setOperTime(new Date());
        historyRecordDao.insert(record);
        return result;
    }

    // 辅助方法：判断操作类型、查询旧数据等
    private boolean isUpdateOrDeleteMethod(String methodName) {
        return methodName.startsWith("update") || methodName.startsWith("delete");
    }
}

• 优点：集中管理历史记录逻辑，支持业务规则过滤（如仅记录特定字段变更），能直接获取操作人信息
• 缺点：需确保所有数据变更操作都经过被注解标记的方法，遗漏则会丢失历史记录

2. 基于ORM框架的底层拦截（以MyBatis为例） #

通过拦截MyBatis的SQL执行过程，实现无侵入式的历史记录：

• 自定义MyBatis拦截器，拦截Executor的update方法（对应新增、更新、删除操作）
• 从MappedStatement中解析SQL对应的表名、操作类型，从参数中提取变更数据
• 封装历史记录并写入历史表
• 示例代码片段：

@Intercepts({
        @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})
})
public class DataHistoryInterceptor implements Interceptor {
    @Autowired
    private HistoryRecordDao historyRecordDao;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement ms = (MappedStatement) invocation.getArgs()[0];
        SqlCommandType commandType = ms.getSqlCommandType();
        String tableName = extractTableNameFromMs(ms);
        Object param = invocation.getArgs()[1];

        // 构造历史记录
        HistoryRecord record = new HistoryRecord();
        record.setTableName(tableName);
        record.setOperType(OperType.valueOf(commandType.name()));
        if (commandType == SqlCommandType.INSERT) {
            record.setNewData(JSON.toJSONString(param));
        } else if (commandType == SqlCommandType.UPDATE) {
            // 需自行实现从参数中提取新旧数据，或通过SQL查询旧数据
            record.setOldData(getOldData(tableName, param));
            record.setNewData(JSON.toJSONString(param));
        } else if (commandType == SqlCommandType.DELETE) {
            record.setOldData(JSON.toJSONString(param));
        }
        record.setOperator(getCurrentLoginUser());
        historyRecordDao.insert(record);

        return invocation.proceed();
    }

    // 从MappedStatement中提取表名的辅助方法
    private String extractTableNameFromMs(MappedStatement ms) {
        // 可通过解析SQL语句或读取Mapper接口的注解获取表名
        String sql = ms.getSqlSource().getBoundSql(new Object()).getSql();
        return extractTableNameFromSql(sql);
    }
}

• 优点：无需修改业务代码，只要是MyBatis执行的变更操作都会被拦截，覆盖率高
• 缺点：解析SQL和参数的逻辑较复杂，动态SQL场景下提取表名和数据难度大

二、MySQL触发器方案（简化版） #

针对手动编写触发器繁琐的问题，可以通过存储过程批量生成触发器：

1. 定义通用历史表 #

先创建一个统一存储所有表历史记录的表：

CREATE TABLE data_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    table_name VARCHAR(100) NOT NULL COMMENT '操作的表名',
    oper_type ENUM('INSERT','UPDATE','DELETE') NOT NULL COMMENT '操作类型',
    old_data JSON COMMENT '变更前数据',
    new_data JSON COMMENT '变更后数据',
    operator VARCHAR(50) COMMENT '操作人（需后端传入或从上下文获取）',
    oper_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数据变更历史表';

2. 批量生成触发器的存储过程 #

编写存储过程遍历指定库下的所有业务表，自动生成对应触发器：

DELIMITER //
CREATE PROCEDURE generate_all_history_triggers(IN db_name VARCHAR(100))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(100);
    -- 游标遍历所有业务表（排除历史表本身）
    DECLARE tbl_cursor CURSOR FOR 
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = db_name 
          AND table_type = 'BASE TABLE' 
          AND table_name != 'data_history';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN tbl_cursor;
    trigger_loop: LOOP
        FETCH tbl_cursor INTO tbl_name;
        IF done THEN
            LEAVE trigger_loop;
        END IF;

        -- 生成INSERT触发器
        SET @insert_sql = CONCAT(
            'CREATE TRIGGER trg_', tbl_name, '_ins AFTER INSERT ON ', tbl_name,
            ' FOR EACH ROW BEGIN ',
            'INSERT INTO data_history(table_name, oper_type, new_data) VALUES(',
            '\'', tbl_name, '\', \'INSERT\', JSON_OBJECT(',
            (SELECT GROUP_CONCAT('\'', column_name, '\', NEW.', column_name) 
             FROM information_schema.columns 
             WHERE table_schema = db_name AND table_name = tbl_name),
            ')); END;'
        );
        PREPARE stmt FROM @insert_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- 生成UPDATE触发器
        SET @update_sql = CONCAT(
            'CREATE TRIGGER trg_', tbl_name, '_upd AFTER UPDATE ON ', tbl_name,
            ' FOR EACH ROW BEGIN ',
            'INSERT INTO data_history(table_name, oper_type, old_data, new_data) VALUES(',
            '\'', tbl_name, '\', \'UPDATE\', ',
            'JSON_OBJECT(', (SELECT GROUP_CONCAT('\'', column_name, '\', OLD.', column_name) FROM information_schema.columns WHERE table_schema = db_name AND table_name = tbl_name), '), ',
            'JSON_OBJECT(', (SELECT GROUP_CONCAT('\'', column_name, '\', NEW.', column_name) FROM information_schema.columns WHERE table_schema = db_name AND table_name = tbl_name), ')); END;'
        );
        PREPARE stmt FROM @update_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- 生成DELETE触发器
        SET @delete_sql = CONCAT(
            'CREATE TRIGGER trg_', tbl_name, '_del AFTER DELETE ON ', tbl_name,
            ' FOR EACH ROW BEGIN ',
            'INSERT INTO data_history(table_name, oper_type, old_data) VALUES(',
            '\'', tbl_name, '\', \'DELETE\', JSON_OBJECT(',
            (SELECT GROUP_CONCAT('\'', column_name, '\', OLD.', column_name) FROM information_schema.columns WHERE table_schema = db_name AND table_name = tbl_name),
            ')); END;'
        );
        PREPARE stmt FROM @delete_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE tbl_cursor;
END //
DELIMITER ;

执行存储过程即可批量生成触发器：

CALL generate_all_history_triggers('your_database_name');

触发器方案的优缺点 #

• 优点：完全无需修改后端代码，数据库层面保证所有变更都被记录，实现简单
• 缺点：无法直接获取操作人（需后端将操作人写入业务表字段，触发器读取），业务规则扩展受限，大量触发器可能影响数据库性能

方案选择建议 #

• 若需要复杂业务规则过滤、获取操作人信息，优先选择Java后端的AOP或ORM拦截器方案
• 若业务逻辑简单，希望无侵入后端代码，可采用触发器+批量生成脚本的方案

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