MySQL 5.7如何编写按发送及最后回复日期擦除非活跃消息内容的查询

原语句问题说明 #

你提供的查询仅能筛选出没有直接子回复的消息，无法递归关联整条回复链路，也无法获取链路的最晚回复时间，不满足需求。

实现方案 #

步骤1：创建递归获取根消息ID的自定义函数 #

MySQL 5.7不支持递归CTE，通过自定义函数遍历回复链路，获取任意消息所属的顶层根消息ID：

DELIMITER //
CREATE FUNCTION get_root_message_id(message_id INT) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE current_id INT DEFAULT message_id;
    -- 循环向上查找父消息，直到找到没有父回复的根消息
    WHILE EXISTS (SELECT 1 FROM Message WHERE id = current_id AND replyToMessage_id IS NOT NULL) DO
        SELECT replyToMessage_id INTO current_id FROM Message WHERE id = current_id;
    END WHILE;
    RETURN current_id;
END //
DELIMITER ;

步骤2：查询验证非活跃消息 #

执行以下语句确认匹配的非活跃消息符合业务规则，确认无误后再执行更新：

SELECT 
    m.id AS 消息ID,
    m.sentDate AS 消息发送时间,
    t.last_reply_date AS 最后回复时间,
    m.content AS 原内容
FROM Message m
INNER JOIN (
    -- 按根消息分组，计算每个根消息对应的整条回复链的最晚发送时间
    SELECT 
        get_root_message_id(id) AS root_id,
        MAX(sentDate) AS last_reply_date
    FROM Message
    GROUP BY root_id
) t ON m.id = t.root_id
WHERE 
    -- 满足两个判定条件
    m.sentDate < DATE_SUB(NOW(), INTERVAL 720 DAY)
    AND t.last_reply_date < DATE_SUB(NOW(), INTERVAL 720 DAY);

步骤3：执行擦除操作 #

验证结果正确后运行以下语句完成内容擦除：

UPDATE Message m
INNER JOIN (
    SELECT 
        get_root_message_id(id) AS root_id,
        MAX(sentDate) AS last_reply_date
    FROM Message
    GROUP BY root_id
) t ON m.id = t.root_id
SET m.content = 'erased'
WHERE 
    m.sentDate < DATE_SUB(NOW(), INTERVAL 720 DAY)
    AND t.last_reply_date < DATE_SUB(NOW(), INTERVAL 720 DAY);

注意事项 #

• 执行操作前建议先对Message表做全量备份，避免误操作导致数据丢失
• 数据量较大时，建议先为replyToMessage_id和sentDate字段添加索引，提升执行效率
• 如果不需要保留函数，执行完成后可运行DROP FUNCTION IF EXISTS get_root_message_id;删除自定义函数

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