假设有一张订单操作日志表，每天新增几十万行，查询最近三天数据却要扫描上千万行。加索引只能缓解一部分压力，真正有效的手段是把超过保留期的数据移出热表。清理动作如果完全依赖人工，迟早会漏执行；如果依赖定时任务，又可能和业务高峰撞在一起。SQL触发器提供了一种思路：当新数据写入时，顺带把另一张历史表中已过期的记录删掉。这样清理动作被拆小、被分散到每次写入里，避免一次性删除带来的长事务。下面围绕这一机制展开。
一、先厘清触发器在数据生命周期中的角色
数据生命周期通常包括数据产生、高频使用、低频归档、过期删除四个阶段。不同阶段对存储介质、索引策略和清理方式要求不同。触发器不是万能清理工具，它本质上是数据库在INSERT、UPDATE、DELETE发生时自动执行的一段过程。最适合的场景是清理动作规模较小、频率可预期、并且清理目标不是正在写入的那张表。比如订单日志写入时，顺便清理通知历史表里90天之前的记录。
如果希望触发器直接清理自己所在表的过期数据，MySQL会直接限制这种操作。当你在AFTER INSERT触发器中执行DELETE FROM同一张表时，会收到Can't update table in stored function/trigger错误。SQL Server和PostgreSQL虽然限制略有不同，但同样不推荐在行级触发器中做大规模同表删除。原因很简单：每插入一行都扫描并删除本表数据，会让写入链路变得极不稳定。理解这个边界后，才能设计出真正可落地的方案。
触发器更适合做轻量级清理与状态变更。比如在写入时给数据打上过期标记，或把已归档的明细从关联表里清空。对于大表批量删除，应由事件调度器或外部任务完成。触发器可以作为第一道防线，处理少量、零散的过期数据；事件调度器则作为第二道防线，定期清理漏网数据。两者结合，生命周期管理才会稳定。
二、准备支持自动清理的日志表结构
自动清理的前提是表结构能准确判断数据是否过期。常见做法是增加expire_at字段，存储过期时间；或者增加created_at字段，保留期由业务规则计算。还可以加status字段区分有效、已过期、已归档。比如一张API调用历史表，只保留最近30天数据，可以设计为：id、api_name、request_body、created_at、expire_at、status。expire_at可以在插入时由应用或触发器自动填入。
建表SQL示例如下：
CREATE TABLE api_call_history (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
api_name VARCHAR(100) NOT NULL,
request_body TEXT,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
expire_at DATETIME NOT NULL,
status TINYINT NOT NULL DEFAULT 1 COMMENT '1有效 0已过期',
PRIMARY KEY (id),
KEY idx_expire_at (expire_at),
KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
expire_at字段最好加上索引，因为触发器和定时任务清理时都会按这个字段过滤。status字段则能支持软删除，避免误删后无法恢复。如果你的数据库支持表分区，还可以按月份或者按天做RANGE分区，清理时直接TRUNCATE或DROP过期分区，比DELETE快得多。不过分区策略对表结构有额外要求，需要在建表时就规划好。
如果清理目标是另一张归档表，归档表结构可以和主表保持一致，但通常去掉不必要的索引，只保留按时间过滤的索引。这样触发器删除归档表数据时，扫描范围更小。
三、用触发器实现过期数据自动清理
这里先说明一个容易踩的坑。MySQL触发器在FOR EACH ROW中执行的语句，不能直接修改正在被触发器使用的表。所以当你想在api_call_history插入数据时同步清理api_call_history里过期记录，是不可行的。可以在另一张业务表写入时，清理api_call_history中的过期数据。假设还有一张request_flow表记录实时请求流水，每次有新请求写入request_flow时，触发器去清理api_call_history里超过30天的记录。
MySQL触发器示例如下：
DELIMITER $$
CREATE TRIGGER trg_cleanup_api_history
AFTER INSERT ON request_flow
FOR EACH ROW
BEGIN
DELETE FROM api_call_history
WHERE expire_at < NOW()
AND status = 1
LIMIT 100;
END$$
DELIMITER ;
这个触发器每次request_flow表插入一行，就尝试删除api_call_history中最先过期的100条记录。LIMIT 100可以避免单次删除过多导致锁范围扩大，但也意味着如果过期数据很多，需要多次插入触发才能逐步清空。该触发器没有直接修改request_flow表，因此可以正常创建。需要注意的是，DELETE语句在api_call_history表上会扫描expire_at索引，性能可控。
PostgreSQL可以使用触发器函数，逻辑更清晰。例如在operation_log表插入后，清理operation_archive表中超过90天的归档数据：
CREATE OR REPLACE FUNCTION cleanup_old_archive()
RETURNS trigger AS $$
BEGIN
DELETE FROM operation_archive
WHERE expire_at < now() - interval '90 days';
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_cleanup_old_archive
AFTER INSERT ON operation_log
FOR EACH ROW EXECUTE FUNCTION cleanup_old_archive();
在PostgreSQL中，行级触发器的函数返回值对于AFTER触发器通常可以返回NULL或NEW。这里返回NEW即可，不影响插入结果。如果清理语句较慢，同样会拖慢operation_log的写入速度，所以要严格控制删除范围。建议在expire_at字段上创建索引，并且使用条件较窄的WHERE。
四、触发器清理的局限与性能优化
触发器清理最大的优点是及时、分散。但它不适合大量过期数据的集中清理。假设一个晚上积压了500万条过期数据，第二天第一次插入请求时，触发器如果按LIMIT 100删除，要执行非常多次才能真正清空；如果去掉LIMIT，单次DELETE 500万行可能锁表几十秒，反而引发线上故障。所以触发器里的删除量级一定要小，单次删除行数建议控制在几十到几百行。
另一个问题是碎片。InnoDB表频繁小规模DELETE会产生页内碎片，索引页也会变得稀疏。虽然InnoDB有后台purge机制，但长时间高频删除仍然可能导致表空间膨胀。可以通过OPTIMIZE TABLE定期整理，但在MySQL中OPTIMIZE TABLE会锁表，只能在低峰执行。PostgreSQL的VACUUM机制可以缓解死元组问题，但同样需要监控。
对于大批量数据，更合理的做法是使用定时任务。MySQL从5.1开始支持EVENT，可以创建一个每5分钟执行一次的清理事件，用游标或者循环分批删除。比如：
DELIMITER $$
CREATE EVENT evt_cleanup_api_history
ON SCHEDULE EVERY 5 MINUTE
STARTS CURRENT_TIMESTAMP
DO
BEGIN
REPEAT
DELETE FROM api_call_history
WHERE expire_at < NOW()
AND status = 1
ORDER BY expire_at
LIMIT 500;
SET @row_count = ROW_COUNT();
UNTIL @row_count = 0
END REPEAT;
END$$
DELIMITER ;
这个事件每5分钟触发一次，每次用循环分批删除，每批500行，直到没有过期数据。相比触发器，这种方式的优点是清理节奏可控，不会因为业务写入突然增大导致清理开销叠加。缺点是不够实时，如果保留策略要求非常严格，可能会有几分钟的过期数据残留。实际项目中常用折中方案：业务写入用触发器做小量实时清理，事件调度器负责兜底。
五、生产环境中的推荐落地方式
如果数据量不大，例如单表几十万行，直接用触发器清理关联表或归档表是可行的。逻辑简单，不需要额外调度组件。但当单表超过百万行，或者写入频率很高时，必须把触发器限制在极小范围，同时增加索引和监控。一个典型的落地方案是：写流量入口的表中使用AFTER INSERT触发器，清理另一张低优先级的已归档表；热表本身只保留近期数据，由低峰期存储过程统一归档和删除。这样触发器不会触碰高频热表。
存储过程示例，将热表数据迁移到归档表并删除：
DELIMITER $$
CREATE PROCEDURE sp_archive_and_purge()
BEGIN
DECLARE affected_rows INT DEFAULT 0;
INSERT INTO api_call_history_archive
(id, api_name, request_body, created_at, expire_at)
SELECT id, api_name, request_body, created_at, expire_at
FROM api_call_history
WHERE expire_at < NOW() - INTERVAL 7 DAY
LIMIT 1000;
DELETE FROM api_call_history
WHERE expire_at < NOW() - INTERVAL 7 DAY
LIMIT 1000;
END$$
DELIMITER ;
归档和删除分成两条语句，中间如果失败，可能产生重复归档。更严谨的做法是使用事务，确保归档和删除原子完成。InnoDB支持事务，将INSERT和DELETE放在START TRANSACTION和COMMIT之间即可。存储过程里还可以增加循环，反复执行直到没有更多过期数据。执行时间选在凌晨低峰，配合事件调度器就能做到每天自动归档。
监控同样重要。可以记录每次清理影响的行数、耗时和剩余过期数据量。例如建立一张cleanup_stats表，触发器或存储过程在清理后写入一条记录。这样当清理逻辑异常时，能根据历史数据快速判断是索引失效、锁等待还是数据量突增。总的来说，SQL触发器是数据生命周期管理中的一个执行环节，而不是全部。把触发器、归档表、定时事件和监控结合起来，才能形成稳定可靠的自动清理机制。
SQL数据生命周期管理触发器自动清理过期数据修改时间：2026-10-07 00:26:27