SQL删除WordPress数据表wp_postmeta中重复浏览数据

共计 1380 个字符，预计需要花费 4 分钟才能阅读完成。

wordpress站点迁移过程中发现，文章的浏览量是通过meta_key='views'，并与post_id绑定，存储在数据表wp_postmeta中，于是仔细查询了一下，存在很多重复的行数据

Tips：wordpress的每篇文章的浏览量是只取其中一行数据展示，但是总浏览量却是将所有的views浏览量求和，这样就会出现数据失真（根据各自主题决定，我的主题出现这样的问题）

查询重复数据

-- 先通过MIN函数拿到重复数据的第一条数据（需要保留的数据）
SELECT MIN(temp.meta_id) AS ID, temp.post_id, temp.meta_key, temp.meta_value
FROM (SELECT * FROM wp_postmeta WHERE meta_key = 'views') temp
GROUP BY temp.post_id, temp.meta_key, temp.meta_value
HAVING COUNT(1) > 1;

-- 再通过INNER JOIN得到所有重复的行记录
SELECT postmeta.meta_id, postmeta.post_id, postmeta.meta_key, postmeta.meta_value
FROM wp_postmeta postmeta
         INNER JOIN (SELECT MIN(temp.meta_id) AS ID,
                            temp.post_id      AS post_id,
                            temp.meta_key     AS meta_key,
                            temp.meta_value   AS meta_value
                     FROM (SELECT * FROM wp_postmeta WHERE meta_key = 'views') temp
                     GROUP BY temp.post_id, temp.meta_key, temp.meta_value
                     HAVING COUNT(1) > 1) temp ON postmeta.post_id = temp.post_id
    AND postmeta.meta_key = temp.meta_key
    AND postmeta.meta_value = temp.meta_value
    AND postmeta.meta_id > temp.ID

删除重复数据

DELETE
FROM wp_postmeta
WHERE meta_id IN (SELECT temp.ID
                  FROM (SELECT postmeta.meta_id AS ID
                        FROM wp_postmeta postmeta
                                 INNER JOIN (SELECT MIN(temp.meta_id) AS ID,
                                                    temp.post_id      AS post_id,
                                                    temp.meta_key     AS meta_key,
                                                    temp.meta_value   AS meta_value
                                             FROM (SELECT * FROM wp_postmeta WHERE meta_key = 'views') temp
                                             GROUP BY temp.post_id, temp.meta_key, temp.meta_value
                                             HAVING COUNT(1) > 1) temp ON postmeta.post_id = temp.post_id
                            AND postmeta.meta_key = temp.meta_key
                            AND postmeta.meta_value = temp.meta_value
                            AND postmeta.meta_id > temp.ID) temp);