10 very useful MySQL queries using WordPress

Most use WordPress built site, after the table are MySQL database, often we need to customize the function of WordPress, here we list 10 most useful wordpress database query, you need a database management tools, such as phpMyAdmin or NAVICAT, etc. to execute these SQL statements.

1. Change the author of all documents to a different user

Before the change, you need to know two different user ID, you can in WP backstage Author & user page to find this ID, or in view of user information click on the user name of the link, the address bar user_id corresponding value is the user ID, Then run the following command to modify:

UPDATE wp_posts SET post_author = ' New-author-id ' WHERE post_author = ' Old-author-id ';

2. Modify the name of the default admin admin

WP default installation Creates an Admin account named Admin, you can modify the name of this account:

UPDATE wp_users SET user_login = ' Your New Username ' WHERE user_login = ' Admin ';

3. Delete revision Revision

Article revisions waste a lot of storage resources, when you have thousands of articles, this value is even more amazing, this will affect the performance of program execution, data acquisition, reduce page load time, the solution is to remove these useless revisions information:

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 '

4. Change the GUID

In the case of a blog transplant, you need to fix the GUID information in the URL in the Wp_posts table, which is critical, because the GUID is used to map the URL path to the article information:

UPDATE wp_posts SET GUID = REPLACE (GUID, ' http://www.oldsiteurl.com ', ' http://www.newsiteurl.com ');

5. Change SiteURL & Homeurl

When you move the site from the local to the real server, the site is not available, because the complete path still points to localhost, you need to modify the URL of the site and the first page 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 ';

6. Delete Pingback data

Popular articles receive a lot of pingback information, which makes the database bulky and can be deleted using the following SQL statement:

DELETE from wp_comments WHERE comment_type = ' Pingback ';

7. Change Picture Path

If you use a CDN to process image access, after you create the CNAME record, you can modify the path of all images by using the following query:

UPDATE wp_posts SET post_content = REPLACE (post_content, ' src= ' http://www.oldsiteurl.com ', ' src= ' http:// Yourcdn.newsiteurl.com ');

You will also need to modify the GUID information of the image attachment by using the following statement:

UPDATE wp_posts SET GUID = REPLACE (GUID, ' http://www.oldsiteurl.com ', ' http://yourcdn.newsiteurl.com ') WHERE Post_type = ' Attachment ';

8. Mark out the useless label

Deleting an article does not guarantee deletion of the corresponding label, you have to do it by hand, and the following query lets you find out which tags are not used:

SELECT * from Wp_terms WT

INNER JOIN wp_term_taxonomy wtt on wt.term_id=wtt.term_id wherewtt.taxonomy= ' Post_tag ' and wtt.count=0;

9. Reset Password

If you want to reset your login password, you can do it directly with the following SQL statement:

UPDATE wp_users SET user_pass = MD5 (' new_password ') WHERE user_login = ' your-username ';

10. Update article Meta Data

If you keep a special URL for each article, you can use the following statement to handle it:

UPDATE Wp_postmeta SET meta_value = REPLACE (meta_value, ' http://www.oldsiteurl.com ', ' http://www.newsiteurl.com ');

Before making any changes, we recommend that you do a backup of the database before you do it.