生产级的 MySQL双 EVENT 归档任务

Event 简单归档

-- SET GLOBAL event_scheduler = ON;

CREATE EVENT IF NOT EXISTS archive_table1
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
CREATE TEMPORARY TABLE tmp_archive_ids (
id BIGINT PRIMARY KEY
) ENGINE=MEMORY;

INSERT INTO tmp_archive_ids (id)
SELECT id
FROM game_node_settlement
WHERE createtime < '2026-01-28'
ORDER BY id
LIMIT 10000
FOR UPDATE;

INSERT INTO game_node_settlement_bak
SELECT t.*
FROM game_node_settlement t
JOIN tmp_archive_ids tmp ON t.id = tmp.id;

DELETE t
FROM game_node_settlement t
JOIN tmp_archive_ids tmp ON t.id = tmp.id;

DROP TEMPORARY TABLE tmp_archive_ids;
END

-- -- 1. 调度器
-- SHOW VARIABLES LIKE 'event_scheduler';
-- -- 2. 状态
-- SHOW EVENTS;
-- -- 3. 定义
-- SHOW CREATE EVENT archive_table1;
-- -- 4. 执行记录
-- SELECT EVENT_NAME, LAST_EXECUTED FROM information_schema.EVENTS;
-- -- 5. 时间
-- SELECT NOW();

===================================
生产级双EVENT归档

archive_table1_worker 真正执行归档（有数据才跑）
archive_table1_watcher 定期检查是否有新数据，决定是否“唤醒”归档

table1
- id BIGINT PRIMARY KEY
- createtime DATETIME
- ...

table1_bak
- 结构与 table1 完全一致

CREATE TABLE archive_run_log (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
event_name VARCHAR(64),
archived_rows INT,
action VARCHAR(32),
run_time DATETIME
);

CREATE EVENT archive_table1_worker
ON SCHEDULE EVERY 10 MINUTE
STARTS NOW()
ON COMPLETION PRESERVE
ENABLE
DO
BEGIN
DECLARE v_rows INT DEFAULT 0;

-- 互斥锁，防止并发
IF GET_LOCK('archive_table1_lock', 0) = 0 THEN
LEAVE BEGIN;
END IF;

CREATE TEMPORARY TABLE tmp_archive_ids (
id BIGINT PRIMARY KEY
) ENGINE=MEMORY;

-- 锁定一批可归档数据
INSERT INTO tmp_archive_ids (id)
SELECT id
FROM table1
WHERE createtime < DATE_SUB(NOW(), INTERVAL 2 DAY)
ORDER BY id
LIMIT 10000
FOR UPDATE;

-- 归档
INSERT INTO table1_bak
SELECT t.*
FROM table1 t
JOIN tmp_archive_ids tmp ON t.id = tmp.id;

SET v_rows = ROW_COUNT();

-- 删除源数据
DELETE t
FROM table1 t
JOIN tmp_archive_ids tmp ON t.id = tmp.id;

DROP TEMPORARY TABLE tmp_archive_ids;

-- 记录日志
INSERT INTO archive_run_log(event_name, archived_rows, action, run_time)
VALUES ('archive_table1_worker', v_rows, 'ARCHIVE', NOW());

-- 核心逻辑：没数据 → 自停
IF v_rows = 0 THEN
ALTER EVENT archive_table1_worker DISABLE;

INSERT INTO archive_run_log(event_name, archived_rows, action, run_time)
VALUES ('archive_table1_worker', 0, 'AUTO_DISABLE', NOW());
END IF;

DO RELEASE_LOCK('archive_table1_lock');
END;

CREATE EVENT archive_table1_watcher
ON SCHEDULE EVERY 30 MINUTE
STARTS NOW()
ON COMPLETION PRESERVE
ENABLE
DO
BEGIN
DECLARE v_cnt INT DEFAULT 0;

-- 检查是否有新可归档数据
SELECT COUNT(*)
INTO v_cnt
FROM table1
WHERE createtime < DATE_SUB(NOW(), INTERVAL 2 DAY)
LIMIT 1;

-- 如果有数据且归档器是停的 → 唤醒
IF v_cnt > 0 THEN
ALTER EVENT archive_table1_worker ENABLE;

INSERT INTO archive_run_log(event_name, archived_rows, action, run_time)
VALUES ('archive_table1_watcher', v_cnt, 'AUTO_ENABLE', NOW());
END IF;
END;

CREATE INDEX idx_table1_createtime_id ON table1 (createtime, id);
ALTER TABLE table1_bak ADD PRIMARY KEY (id);