WordPressの重複した投稿を削除したいときに使えるSQLです。
重複を削除するプラグインはいくつかありますが、投稿の数が膨大になると重すぎて使えなくなってしまいます。
そこで、MySQLのレコードをSQLで直接削除してしまおうという試みです。
※必ずバックアップを取ってから実行しましょう。
重複した投稿を探すSQL
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';
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';
かんたんですね!
参考)Find and delete duplicate posts in WordPress
http://wpkrauts.com/2013/find-and-delete-duplicate-posts-in-wordpress/