如何每日将MySQL表chat复制至chat_archive并清空原表且不影响业务？

这是个非常典型的生产环境归档需求——既要每日把历史数据迁移到归档表，又不能影响实时聊天消息的写入。我分享两个经过验证的方案，你可以根据自己的表规模和并发情况选择：

方案一：基于时间戳/自增ID的批量事务归档（适合中小表） #

如果你的chat表有created_at（消息创建时间）或者自增主键id，可以利用范围查询来最小化锁的影响，同时用事务保证数据一致性：

操作步骤 #

1. 先确保chat_archive表结构和chat完全一致（包括索引、约束），可以用这条语句初始化：

CREATE TABLE chat_archive LIKE chat;

2. 编写归档脚本（可以用MySQL事件调度器自动执行，或者放到定时任务里）：

START TRANSACTION;

-- 第一步：获取截止到昨日23:59:59的所有消息的最大ID（假设用created_at判断归档范围）
SELECT MAX(id) INTO @max_archive_id 
FROM chat 
WHERE created_at <= DATE_SUB(CURDATE(), INTERVAL 1 DAY);

-- 如果有需要归档的数据
IF @max_archive_id IS NOT NULL THEN
    -- 把历史数据插入归档表
    INSERT INTO chat_archive 
    SELECT * FROM chat WHERE id <= @max_archive_id;
    
    -- 删除原表中的历史数据
    DELETE FROM chat WHERE id <= @max_archive_id;
END IF;

COMMIT;

为什么这个方案不影响业务？ #

• 用范围查询id <= @max_archive_id只会锁定需要归档的行，不会锁全表，新写入的消息ID肯定大于这个值，所以不会被阻塞。
• 事务包裹保证了插入和删除操作的原子性，不会出现数据只插入归档表但没删除原表的情况。

方案二：无锁式重命名表归档（适合大表/高并发场景） #

如果你的chat表数据量很大，方案一的批量插入/删除可能还是会占用较多资源，推荐用原子重命名表的方式，几乎零阻塞业务：

操作步骤 #

1. 提前创建一个和chat结构完全一致的空表（可以提前在低峰期创建，或者在脚本里动态创建）：

CREATE TABLE chat_new LIKE chat;

2. 执行原子重命名操作（这一步是瞬间完成的，业务写入几乎无感知）：

-- 把正在被写入的chat表重命名为临时表，同时把空表chat_new改名为chat供业务继续写入
RENAME TABLE chat TO chat_temp, chat_new TO chat;

3. 后台异步把临时表的数据插入归档表（这一步不影响主表的任何操作）：

-- 如果数据量极大，可以分批次插入，避免一次性占用过多IO
WHILE (SELECT COUNT(*) FROM chat_temp) > 0 DO
    INSERT INTO chat_archive SELECT * FROM chat_temp LIMIT 10000;
    DELETE FROM chat_temp LIMIT 10000;
END WHILE;

4. 验证数据一致性后，删除临时表：

DROP TABLE chat_temp;

核心优势 #

• RENAME TABLE是MySQL的原子操作，执行时间毫秒级，业务写入不会被中断。
• 后续的归档操作都在临时表上进行，完全不影响新的chat表的正常使用。

关键注意事项 #

• 执行时机：建议把归档任务安排在业务低峰期（比如凌晨2-4点），进一步降低对系统资源的影响。
• 数据校验：每次归档后，可以对比chat_archive新增的行数和原表删除的行数，确保数据没有丢失。
• 自动执行：可以用MySQL的事件调度器自动每日执行，开启方式和示例如下：

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

-- 创建每日凌晨2点执行的归档事件
CREATE EVENT daily_chat_archive
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 02:00:00'
DO
BEGIN
    -- 这里放方案二的完整脚本
    CREATE TABLE chat_new LIKE chat;
    RENAME TABLE chat TO chat_temp, chat_new TO chat;
    
    WHILE (SELECT COUNT(*) FROM chat_temp) > 0 DO
        INSERT INTO chat_archive SELECT * FROM chat_temp LIMIT 10000;
        DELETE FROM chat_temp LIMIT 10000;
    END WHILE;
    
    DROP TABLE chat_temp;
END;

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