如何删除数据库中重复的Wordpress帖子
问题描述:
当我在wp_posts表上运行此代码(使用phpmyadmin)时,它会返回一个重复的帖子。如何删除数据库中重复的Wordpress帖子
SELECT a.ID, a.post_title, a.post_type, a.post_status
FROM wp_posts AS a
INNER JOIN (
SELECT post_title, MIN(id) AS min_id
FROM wp_posts
WHERE post_type = 'post'
AND post_status = 'publish'
GROUP BY post_title
HAVING COUNT(*) > 1
) AS b ON b.post_title = a.post_title
AND b.min_id <> a.id
AND a.post_type = 'post'
AND a.post_status = 'publish'
注意,这个特定职位其实不是重复的,它只有标题为另一个(给出一个正常的行为上面的代码中查找重复的标题)相同。然而,问题是数据库中实际存在数百个重复帖子(如果我手动查看,我可以看到它们),那么为什么我无法使用上述代码找到它们呢?
- 我还可以看到从WordPress管理面板这两个重复的帖子...
- 我也试着使用一些WordPress插件,它们都发现了同样的重复后就像上面的代码,但没有其他)。
编辑:如果我看到两个随机重复,一个有:
ID 9462
post date 2017-03-07 13:06:31
post content "foo"
post title "Les pendules à l'heure"
post status "publish"
post type "post"
guid "http://www.exemple.com/?p=9462"
其他有:
ID 11409
post date 2017-03-07 13:06:31
post content "foo"
post title "Les pendules à l'heure"
post status "publish"
post type "post"
guid "http://www.exemple.com/?p=9462"
感谢
答
这是我如何管理解决我的问题...
我注意到,尽管标题在重复帖子之间看起来相同,但是其中一个有撇号,而另一个有撇号,
所以首先我跑这个查询看到许多文章标题怎么过的撇号:
SELECT post_title FROM wp_posts WHERE post_title LIKE "%'%",
它返回1825个结果。然后我运行以下命令查看有多少个帖子标题&#039;
SELECT post_title FROM wp_posts WHERE post_title LIKE "%'%"
它返回了720个结果。所以我想我会取代所有的&#039;撇号,使用下面的查询:
UPDATE wp_posts SET post_title = REPLACE (post_title, ''', '\'');
然后,我可以使用:
SELECT a.ID, a.post_title, a.post_type, a.post_status
FROM wp_posts AS a
INNER JOIN (
SELECT post_title, MIN(id) AS min_id
FROM wp_posts
WHERE post_type = 'post'
AND post_status = 'publish'
GROUP BY post_title
HAVING COUNT(*) > 1
) AS b ON b.post_title = a.post_title
AND b.min_id <> a.id
AND a.post_type = 'post'
AND a.post_status = 'publish'
哪些返回572个职位,其中我只需使用删除:
DELETE a.*
FROM wp_posts AS a
INNER JOIN (
SELECT post_title, MIN(id) AS min_id
FROM wp_posts
WHERE post_type = 'post'
AND post_status = 'publish'
GROUP BY post_title
HAVING COUNT(*) > 1
) AS b ON b.post_title = a.post_title
AND b.min_id <> a.id
AND a.post_type = 'post'
AND a.post_status = 'publish'
答
Try this
DELETE a.*
FROM wp_posts AS a
INNER JOIN (
SELECT post_title, MIN(id) AS min_id
FROM wp_posts
WHERE post_type = 'post'
AND post_status = 'publish'
GROUP BY post_title
HAVING COUNT(*) > 1
) AS b ON b.post_title = a.post_title
AND b.min_id <> a.id
AND a.post_type = 'post'
AND a.post_status = 'publish'
+0
这不是我想要做的。我只想找到现在选择重复项。我将在使用SELECT时删除它们返回重复项。 – CharleyB0y
当你手动看看数据库,你是否检查post_type和post_status匹配你的查询? –
我在帖子中添加了更多信息。是的,post_type和post_status匹配。 @lan Kenney – CharleyB0y
@Lan Kenney,对不起......我刚刚编辑了我的帖子......看起来他们在浏览器中显示时是匹配的,但是在查看代码时我看到一个有“'”,而另一个有'。也许我需要先修正特殊字符?我应该在我的数据库中保留哪一个? “'”还是'? – CharleyB0y