求MySQL SQL查询语句：清理WordPress重复文章并保留最长标题

注意事项 #

• 务必先备份数据库，删除操作不可逆
• 替换SQL中的wp_为你的WordPress数据库表前缀
• 先执行验证查询，确认逻辑正确后再执行删除操作

1. 验证查询：查看需保留的文章 #

先运行此查询，确认每个前3个单词组中标题最长的文章是否符合预期：

SELECT 
    SUBSTRING_INDEX(post_title, ' ', 3) AS first_three_words,
    ID,
    post_title,
    -- 计算标题单词数（空格数+1）
    LENGTH(post_title) - LENGTH(REPLACE(post_title, ' ', '')) + 1 AS word_count
FROM wp_posts
WHERE post_type = 'post' AND post_status = 'publish' -- 仅处理已发布文章，可按需调整
GROUP BY first_three_words
HAVING word_count = MAX(LENGTH(post_title) - LENGTH(REPLACE(post_title, ' ', '')) + 1);

2. 删除重复文章 #

确认验证结果无误后，执行此语句删除重复文章，仅保留每个前3个单词组中标题最长的一篇：

DELETE p FROM wp_posts p
LEFT JOIN (
    SELECT 
        ID AS keep_id
    FROM (
        SELECT 
            ID,
            post_title,
            SUBSTRING_INDEX(post_title, ' ', 3) AS first_three_words,
            -- 按标题单词数降序、ID降序排序，取每个组的第一条
            ROW_NUMBER() OVER (
                PARTITION BY SUBSTRING_INDEX(post_title, ' ', 3) 
                ORDER BY (LENGTH(post_title) - LENGTH(REPLACE(post_title, ' ', '')) + 1) DESC, ID DESC
            ) AS rn
        FROM wp_posts
        WHERE post_type = 'post' AND post_status = 'publish'
    ) ranked
    WHERE rn = 1
) keep ON p.ID = keep.keep_id
WHERE keep.keep_id IS NULL
AND p.post_type = 'post' AND p.post_status = 'publish';

补充说明 #

• 若存在标题单词数相同的文章，此SQL会保留ID最大的那篇，可根据需求调整ORDER BY子句（例如改为按post_date DESC保留最新发布的）
• 若需处理草稿或其他状态文章，修改post_status条件即可（如post_status IN ('publish', 'draft')）

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