MySQL中如何优雅实现匹配未更新记录标识及过期清理？

一、字段设计优化 #

首先给目标表新增matched字段，推荐使用TIMESTAMP类型，默认值设为CURRENT_TIMESTAMP，新插入的记录matched值会和created保持一致，符合初始状态逻辑：

ALTER TABLE your_table ADD COLUMN matched TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

二、匹配状态的更新逻辑 #

核心需求是只要记录被找到并尝试更新，无论是否实际修改字段，都要更新matched字段，提供两种实用实现方式：

1. 显式统一更新matched #

在UPDATE语句中直接带上matched = CURRENT_TIMESTAMP，无需额外判断，不管其他字段是否变化都会更新匹配时间：

UPDATE your_table
SET 
  column1 = IF(new_value != column1, new_value, column1),
  column2 = IF(new_value != column2, new_value, column2),
  matched = CURRENT_TIMESTAMP
WHERE id = target_id;

2. 基于受影响行数的条件更新 #

先执行常规更新，通过ROW_COUNT()函数判断是否有实际字段修改：若返回0，说明记录被找到但无字段可更新，此时单独更新matched。可封装成存储过程复用：

DELIMITER //
CREATE PROCEDURE update_or_mark_matched(
  IN target_id INT,
  IN new_col1 VARCHAR(255),
  IN new_col2 INT
)
BEGIN
  -- 执行常规字段更新
  UPDATE your_table 
  SET column1 = new_col1, column2 = new_col2 
  WHERE id = target_id;
  
  -- 无字段修改时更新匹配时间
  IF ROW_COUNT() = 0 THEN
    UPDATE your_table SET matched = CURRENT_TIMESTAMP WHERE id = target_id;
  END IF;
END //
DELIMITER ;

三、自动清理过期未匹配记录 #

利用MySQL事件调度器实现定期自动清理，无需外部脚本介入：

1. 开启事件调度器 #

先确保全局事件调度器处于开启状态：

SET GLOBAL event_scheduler = ON;

为避免MySQL重启后失效，将以下配置添加到my.cnf（Linux）或my.ini（Windows）：

event_scheduler = ON

2. 创建定时清理事件 #

比如删除30天内未被匹配的记录，设置每天凌晨1点执行清理：

DELIMITER //
CREATE EVENT clean_unmatched_records
ON SCHEDULE EVERY 1 DAY STARTS '2024-01-01 01:00:00'
DO
BEGIN
  DELETE FROM your_table WHERE matched < DATE_SUB(NOW(), INTERVAL 30 DAY);
END //
DELIMITER ;

可根据需求调整时间间隔（如EVERY 6 HOUR）或过期时长（如INTERVAL 7 DAY）。

四、性能优化建议 #

• 给matched字段添加索引，提升清理操作的查询效率：

  CREATE INDEX idx_matched ON your_table(matched);
  

• 若涉及跨时区场景，可改用DATETIME类型替代TIMESTAMP，手动设置默认值和更新值：

  ALTER TABLE your_table ADD COLUMN matched DATETIME DEFAULT NOW();
  

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