This section describes 10 commonly used MySQL query_mysql when using WordPress.

This article mainly introduces 10 commonly used MySQL queries when using WordPress. many users choose to use MySQL when using WordPress. This article is very useful for users who have just started, you can refer to most websites built using WordPress. the backend is a MySQL database. we often need to customize the WordPress function. Here we list 10 Most Useful WordPress database queries, you need a database management tool, such as phpMyAdmin or Navicat, to execute these SQL statements.

1. change the author of all files to another user.

Before modification, you must first know the IDs of two different users. you can find this ID on the Author & User page of the WP background, or click the User name link when viewing User information, the value corresponding to the user_id in the address bar is the user ID, and then run the following command to modify it:

UPDATE wp_posts SET post_author = 'new-author-id' WHERE post_author = 'old-author-id';

2. modify the name of the default administrator admin.

By default, WP will create an administrator account named admin. you can modify the account name:

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

3. delete Revision

The revision of an article wastes a lot of storage resources. when you have thousands of articles, this value is even more astonishing, which will affect the performance and data acquisition of program execution and reduce the page loading time, the solution is to delete the useless revision information:

DELETE a,b,c FROM wp_posts aLEFT 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

During blog transplantation, you need to fix the GUID information in the URL in the wp_posts table. this is critical because GUID is used to match the URL path with 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 website from the local server to the real server, the website cannot be used because the complete path still points to localhost. you need to modify the URL and Homepage URL of the website:

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 will receive a large amount of pingback information, which makes the database bulky. you can use the following SQL statement to delete it:

DELETE FROM wp_comments WHERE comment_type = 'pingback';

7. change the image path

If you use CDN to process Image access, after creating a CNAME record, you can use the following query to modify the path of all images:

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

You also need to use the following statement to modify the GUID of the Image Attachment:

UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.oldsiteurl.com', 'http://yourcdn.newsiteurl.com') WHERE post_type = 'attachment';

8. Mark useless labels

When you delete an article, the corresponding tags are not guaranteed to be deleted. you must do this manually. the following query will help you find the unused tags:

SELECT * From wp_terms wtINNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.count=0;

9. reset the password

If you want to reset the logon password, use the following SQL statement to complete it:

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

10. Update article metadata

If you maintain 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 back up the database before performing the operation.