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

993次阅读
一条评论

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

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

SQL删除WordPress数据表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);
正文完
 0
裴先生
版权声明:本站原创文章,由 裴先生 2024-02-07发表,共计1380字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
评论(一条评论)
sWBmGqkCMerHDydh 评论达人 LV.1
2024-03-09 23:32:11 回复

WordPress浏览数据冗余清除

 Windows  Chrome  墨西哥Mexico
本站勉强运行: