迁移数据到历史表

lishihuan大约 4 分钟

迁移数据到历史表

场景描述，目前由于有张表数据量过大，导致已经影响正常使用，现在考虑创建历史表，原表只保留10天的数据，通过数据库的事件 每一个小时执行一次，通过查询历史表中id最大值，查询出时时表中新增的数据，执行插入【保证历史表的数据是完整的】
同时删除时时表10天之前的数据
原表：OD_ROUND_RECORD
历史表：OD_ROUND_RECORD_HIS
目前总共2中方案
方案1：历史表记录10天前，实时表记录10天内【调度是1天执行一次】
方案2：历史表记录全部数据，通过频繁的定时器时时的去插入，实时表保留10天【调度需要1小时执行一次】
弊端：
当前没有一个表记录完整的数据，历史表是10天前，实时表10天内，如果涉及到相关查询则无法查询出全部【只能将目前最优了】
如果让历史表记录完整数据，就存在无法保证操作实时表删除、修改这些场景下的数据同步

-- 1. 备份一下原表【防止意外】

create table OD_ROUND_RECORD_20250416 as select * from OD_ROUND_RECORD;

-- 2. 创建历史表并且迁移数据到历史表中【只迁移10天外的数据】

-- 预创建备份表
create table OD_ROUND_RECORD_HIS LIKE  OD_ROUND_RECORD;

-- 抛弃
create table OD_ROUND_RECORD_HIS as select * from OD_ROUND_RECORD WHERE 1!=1; -- DUTY_DATE < CURDATE() - INTERVAL 10 DAY
ALTER TABLE OD_ROUND_RECORD_HIS MODIFY COLUMN id BIGINT NOT NULL PRIMARY KEY; -- 设置主键，否则id会存在重复无法察觉

修改历史表的 DUTY_DATE 日期类型

ALTER TABLE OD_ROUND_RECORD_HIS  MODIFY COLUMN DUTY_DATE DATE;

创建索引解决查询慢

-- ALTER TABLE OD_ROUND_RECORD_HIS ADD INDEX idx_OD_ROUND_RECORD_HIS (DUTY_DATE, SHIFT_RECORD_ID,USER_ID, DEPT_TYPE,ORDER_DEPT_ID);


CREATE INDEX idx_RECORD_HIS_duty_shift_dept_order_user ON `OD_ROUND_RECORD_HIS` ( `DUTY_DATE`, `SHIFT_RECORD_ID`, `DEPT_TYPE`, `ORDER_DEPT_ID`, `USER_NAME` );

6.创建历史记录表

CREATE TABLE error_log (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 自增主键
    error_code CHAR(5),              -- 错误代码（可扩展为具体的错误码）
    error_time DATETIME,                -- 错误发生时间
    error_message TEXT                  -- 错误详细信息
);

添加存储过程

DELIMITER $$

CREATE PROCEDURE sync_and_clean_od_round_record()
BEGIN
    -- 声明变量
    DECLARE cutoff_time DATETIME;          -- 数据分界时间（10天前）
    DECLARE batch_size INT DEFAULT 1000;   -- 每批次处理的记录数
    DECLARE min_id, max_id BIGINT;         -- 批次范围的最小和最大 ID
    DECLARE err_flag INT DEFAULT 0;        -- 错误标记
    DECLARE errcode CHAR(5);               -- 错误代码
    DECLARE errmsg TEXT;                   -- 错误信息

    -- 异常处理（捕获所有异常类型）
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
    BEGIN
        SET err_flag = 1;  -- 标记错误
        GET DIAGNOSTICS CONDITION 1
            errcode = RETURNED_SQLSTATE,
            errmsg = MESSAGE_TEXT;
        ROLLBACK;  -- 回滚事务

        -- 插入错误日志
        INSERT INTO error_log (error_code, error_time, error_message)
        VALUES (errcode, NOW(), CONCAT('Error: ', IFNULL(errmsg, 'UNKNOWN')));
    END;

    -- 自定义异常处理
    DECLARE EXIT HANDLER FOR SQLSTATE '45000'
    BEGIN
        -- 记录自定义错误日志
        INSERT INTO error_log (error_code, error_time, error_message)
        VALUES ('45000', NOW(), 'Custom validation error');
        RESIGNAL;  -- 重新抛出异常
    END;

    -- 初始化参数
    SET cutoff_time = CURDATE() - INTERVAL 10 DAY;

    -- 获取处理范围（10天前的数据）
    SELECT MIN(id), MAX(id)
    INTO min_id, max_id
    FROM OD_ROUND_RECORD
    WHERE DUTY_DATE < cutoff_time;

    -- 分批次处理数据
    WHILE min_id <= max_id AND err_flag = 0 DO
        START TRANSACTION;

        -- 插入历史表（忽略主键冲突）
        INSERT IGNORE INTO OD_ROUND_RECORD_HIS (
            ID, DUTY_DATE, SHIFT_RECORD_ID, SHIFT_RECORD_NAME, USER_ID, USER_NAME, ASSET_ORG_NAME, TRANSMISSION_CENTER,
            ORDER_DEPT_ID, ORDER_DEPT_NAME, DEPT_TYPE, XS_TYPE, ROLE_NAME, ALARM_DEVICE, RESULT, ROUND_TIME,
            ALARM_DEVICE_CODE, DEVICE_FACTORY, REMARKS, DEL_FLAG, CREATE_BY, CREATE_DATE, UPDATE_BY, UPDATE_DATE
        )
        SELECT
            ID, DUTY_DATE, SHIFT_RECORD_ID, SHIFT_RECORD_NAME, USER_ID, USER_NAME, ASSET_ORG_NAME, TRANSMISSION_CENTER,
            ORDER_DEPT_ID, ORDER_DEPT_NAME, DEPT_TYPE, XS_TYPE, ROLE_NAME, ALARM_DEVICE, RESULT, ROUND_TIME,
            ALARM_DEVICE_CODE, DEVICE_FACTORY, REMARKS, DEL_FLAG, CREATE_BY, CREATE_DATE, UPDATE_BY, UPDATE_DATE
        FROM OD_ROUND_RECORD
        WHERE id BETWEEN min_id AND min_id + batch_size 
				AND DUTY_DATE < cutoff_time;

        -- 删除原表数据
        DELETE FROM OD_ROUND_RECORD
        WHERE id BETWEEN min_id AND min_id + batch_size
          AND DUTY_DATE < cutoff_time;

        -- 提交或回滚事务
        IF err_flag = 0 THEN
            COMMIT;  -- 提交事务
            SET min_id = min_id + batch_size;  -- 更新批次范围
        ELSE
            ROLLBACK;  -- 回滚事务
        END IF;
    END WHILE;
END $$

DELIMITER ;

-- 启用事件调度器 SET GLOBAL event_scheduler = ON;

SHOW VARIABLES LIKE 'event_scheduler';

如果返回的值是 OFF，可以使用以下命令启用事件调度器【临时】：

需要通过数据库配置文件中添加才能长久
C:\ProgramData\MySQL\MySQL Server 8.1
添加 event_scheduler=ON

SET GLOBAL event_scheduler = ON;

定时任务配置

-- 每日凌晨执行（数据保留策略可靠时）
CREATE EVENT E_OD_ROUND_RECORD_DAILY_CLEAN
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP(CURRENT_DATE, '01:30:00')  -- 凌晨01:30执行
DO
BEGIN
    CALL sync_and_clean_od_round_record();
END;

放弃【每个小时执行】

CREATE EVENT E_OD_ROUND_RECORD_HOURLY_SYNC
ON SCHEDULE EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP
DO
BEGIN
    CALL sync_and_clean_od_round_record();
END;

手动执行

CALL sync_and_clean_od_round_record();

测试

-- 测试前先备份
create table OD_ROUND_RECORD_0416 as select * from OD_ROUND_RECORD;

-- 测试失败，还原初始表
RENAME TABLE OD_ROUND_RECORD_0416 TO OD_ROUND_RECORD;
-- 还原的新表没有主键自增长
ALTER TABLE OD_ROUND_RECORD MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY;
-- 重新备份原表
create table OD_ROUND_RECORD_0416 as select * from OD_ROUND_RECORD;
-- 开始测试，整表迁移
create table OD_ROUND_RECORD_HIS LIKE  OD_ROUND_RECORD; 
 -- 手动执行存储过程
CALL sync_and_clean_od_round_record();
-- 查询 执行报错日志
SELECT * FROM error_log; 
 
-- 查询是否成功
SELECT * FROM OD_ROUND_RECORD_HIS order by id desc LIMIT 100   -- id=2141579
select count(1) from OD_ROUND_RECORD where DATE(DUTY_DATE)=DATE('2025-04-11');
select count(1) from OD_ROUND_RECORD_HIS where DATE(DUTY_DATE)=DATE('2025-04-11');

-- 新增数据 插入10天前的数据--  2025/3/29
-- 手动执行存储过程
CALL sync_and_clean_od_round_record();
-- 查询 执行报错日志
SELECT * FROM error_log; 

-- 模拟插入失败场景
-- 取 历史表中的已经迁移过来的数据，插入到实时表中
select * from OD_ROUND_RECORD_HIS where DATE(DUTY_DATE)=DATE('2025-03-29');

其他

-- 给备份的表添加主键id自增长
ALTER TABLE OD_ROUND_RECORD MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY;
-- 如果失败  【临时禁用严格模式】
SET SESSION sql_mode = '';


-- 修改表名称
RENAME TABLE OD_ROUND_RECORD_0416 TO OD_ROUND_RECORD;

-- 备份
create table OD_ROUND_RECORD_0416 as select * from OD_ROUND_RECORD;

迁移数据到历史表

# 迁移数据到历史表

# 测试

# 其他

迁移数据到历史表

测试

其他