WordPress Common Database SQL query statements Daquan

In the process of using WordPress, we need to make modifications to the database, such as changing the domain name, modify the attachment directory, bulk modification of the article content and so on. This time, using SQL query statements can greatly simplify our workload.

For information on how to manipulate SQL query statements, please visit the phpMyAdmin Tutorial using SQL query statements to modify the database

Below to share some WordPress common SQL query statements

Note: 1. Be sure to export the database backup before you use SQL query statements! !

2. The following SQL query statement, the database is using the wordpress default wp_ header, according to their actual modification.

1. Remove all unused tags

12345

DELETE A,b,cfrom wp_terms as Aleft join Wp_term_taxonomy as C on a.term_id = C.term_idleft JOIN wp_term_relationships as B On b.term_taxonomy_id = C.term_taxonomy_idwhere c.taxonomy = ' Post_tag ' and c.count = 0

2. Delete all article revisions (revisions) and their meta data

12345

DELETE a,b,cfrom wp_posts aleft JOIN wp_term_relationships b on (a.id = b.object_id) left JOIN Wp_postmeta c on (a.id = C.P ost_id) WHERE A.post_type = ' revision '

3. Change the WordPress address and home Address

123	UPDATE Wp_optionsset option_value = replace (Option_value, ' http://www. Old URL. com ', ' http://www. New URL. com ') WHERE option_ Name = ' Home ' OR option_name = ' SiteURL '

4. Change the GUID of an article

12	UPDATE Wp_postsset GUID = REPLACE (GUID, ' http://www. Old URL. com ', ' http://www. New URL. com ')

5. Change the link address in the body

12	UPDATE Wp_postsset post_content = REPLACE (post_content, ' http://www. Old URL. com ', ' http://www. New URL. com ')

6. Update the meta-value of the article

12	UPDATE Wp_postmetaset meta_value = REPLACE (Meta_value, ' http://www. Old URL. com ', ' http://www. New URL. com ')

7. Reset Admin Password

123	UPDATE Wp_usersset user_pass = MD5 (' new_password ') WHERE user_login = ' admin '

8. Reset Admin Username

123	UPDATE wp_usersset user_login = ' newname ' WHERE user_login = ' admin '

9. Transfer all author A's articles to author B

123	UPDATE Wp_postsset post_author = ' B ' WHERE post_author = ' a '

10. Delete the META tag of the article

12	DELETE from Wp_postmetawhere meta_key = ' Your-meta-key '

11. Export the email address in all comments

12	SELECT DISTINCT Comment_author_emailfrom wp_comments

12. Delete all Pingback

12	DELETE from Wp_commentswhere comment_type = ' Pingback '

13. Delete all spam comments

12	DELETE from Wp_commentswhere comment_approved = ' spam '

14. Disable all active plugins

123	UPDATE wp_optionsset option_value = ' WHERE option_name = ' Active_plugins '

15. List all unused meta tags

1234	SELECT *from Wp_postmeta pmleft JOIN wp_posts wp on wp.id = Pm.post_idwhere wp.id is NULL

16. Close messages from old posts

123	UPDATE wp_postsset comment_status = ' closed ' WHERE post_date < ' 2009-01-01 ' and post_status = ' publish '

17. Update the URL of the message

12	UPDATE Wp_commentsset Comment_author_url = REPLACE (Comment_author_url, ' http://old URL. com ', ' http://new URL. com ')

18. Update the body of all ' target= ' _blank "' for ' rel=" nofollow "'

12	UPDATE Wp_postsset post_content = REPLACE (post_content, ' target= ' _blank ', ' rel= ' nofollow ')

The above 18 articles from http://paranimage.com/19-wordpress-sql-hacks/, will continue to be added.

19. Delete Unused META tags

1234	DELETE pmfrom Wp_postmeta pmleft JOIN wp_posts wp on wp.id = Pm.post_idwhere Wp.id is NULL

20. Delete duplicate custom fields

See: http://www.wpdaxue.com/remove-duplicate-custom-fields.html

WordPress Common Database SQL query statements Daquan