Last active
September 17, 2019 19:55
-
-
Save jacks0n/89b3f9f429950f3ddaae to your computer and use it in GitHub Desktop.
Useful SQL Queries to Cleanup a WordPress Database
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Delete all old post revisions | |
DELETE a,b,c FROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) | |
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = 'revision'; | |
-- Cleanup weird characters | |
UPDATE wp_posts SET post_content = REPLACE(post_content, '“', '“'); | |
UPDATE wp_posts SET post_content = REPLACE(post_content, 'â€', '”'); | |
UPDATE wp_posts SET post_content = REPLACE(post_content, '’', '’'); | |
UPDATE wp_posts SET post_content = REPLACE(post_content, '‘', '‘'); | |
UPDATE wp_posts SET post_content = REPLACE(post_content, '—', '–'); | |
UPDATE wp_posts SET post_content = REPLACE(post_content, '–', '—'); | |
UPDATE wp_posts SET post_content = REPLACE(post_content, '•', '-'); | |
UPDATE wp_posts SET post_content = REPLACE(post_content, '…', '…'); | |
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '“', '“'); | |
UPDATE wp_comments SET comment_content = REPLACE(comment_content, 'â€', '”'); | |
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '’', '’'); | |
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '‘', '‘'); | |
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '—', '–'); | |
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '–', '—'); | |
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '•', '-'); | |
UPDATE wp_comments SET comment_content = REPLACE(comment_content, '…', '…'); | |
-- Delete all in trash | |
DELETE FROM wp_posts | |
WHERE post_status = 'trash'; | |
-- Change home and site URL | |
UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com') WHERE option_name = 'home' OR option_name = 'siteurl'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment