In the past ten years, MySQL has become a widely popular database, and WordPress blogs use the MySQL database. Although using plug-ins can solve some problems, when implementing certain special tasks, executing SQL statements in phpMyAdmin is the best way. In a concise way, here is a summary of eight useful SQL statements in the WordPress system to solve some actual problems.
1. Create a backup database.
Backing up the database is the first thing to do. You can simply back up the database through the following methods:
After logging in to phpMyAdmin. Select your WordPress database, then click the "Export" button, select a compression method (you can use gzip) and click the "Execute" button. When the browser prompts whether to download, click "Yes" to download the database file to local.
2. Batch deletion of article revisions.
WordPress version 2.6 and later has added a Post revisions function. Although it is somewhat useful, article revisions increase the size of your database. We can choose to delete them in batches.
After logging in to phpMyAdmin, execute the following SQL statement to delete in batches.
DELETE FROM wp_posts WHERE post_type = "revision";
3. Delete spam comments in batches.
A true story is that a friend of mine established a blog online. Once he spent a few days on vacation without accessing the Internet. When he came back, he logged in to his blog and saw that There are more than 5,000 comments waiting for review. Of course, most of them are spam comments. It will take a lot of time to delete these comments manually, so we can use the following method.
After logging in to phpMyAdmin, execute the following SQL statement.
DELETE from wp_comments WHERE comment_approved = '0';
Be careful, while this solution is great for dealing with millions of snail mail spam, it will also remove unapproved comments, so it's better to install Akismet to deal with spam.
4. Modify article attributes
After you install WordPress, an admin account is created. Many people mistakenly use this account to blog until they realize that it is not a personal account.
The solution is that it takes a lot of time to modify the author attributes of each article. The following SQL statement can help you quickly complete this function.
First you need to find your correct user name. Use the following SQL statement to find your user ID number.
SELECT ID, display_name FROM wp_users;
Assume that this ID is NEW_AUTHOR_ID, and the ID of the administrator admin is OLD_AUTHOR_ID. Then, run the following SQL statement.
UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;
5. Manually reset the password.
Many people use very complex passwords to protect their blogs from being hacked. Although this is a good thing, it often happens that the administrator password is forgotten.
Of course, WordPress can send you a link to reset your password via email, but if you don’t have access to your email address, then you’ll have to use the following SQL statement to reset your password.
UPDATE wp_users SET user_pass = MD5('PASSWORD') WHERE wp_users.user_login ='admin' LIMIT 1;
MD5 is MySQL's built-in hash function used to convert passwords into hash values.
6. Change the domain name of WordPress
You may sometimes want to change the domain name of your blog, but WordPress will store your domain name in the database, so you need to use the following SQL statement to modify it.
UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldsite.com', 'http://www.newsite.com') WHERE option_name = 'home' OR option_name = 'siteurl';
Next, you have to use the following SQL to modify the GUID of the article.
UPDATE wp_posts SET guid = replace(guid, 'http://www.oldsite.com','http://www.newsite.com');
Finally, use the following statement to replace all old domain names in the article with new domain names.
UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldsite.com', 'http://www.newsite.com');
7. Display the number of SQL queries.
When you have concerns about blog performance, it is very important to understand the number of database queries. In order to reduce database queries, we need to know how many queries there are on a page.
This time, there is no need to log in to phpMyAdmin. You only need to modify the footer.php file and add the following lines of code at the end of the file.
<?php if (is_user_logged_in()) { ?>
<?php echo get_num_queries(); ?> queries in <?php timer_stop(1); ?> seconds.
<?php } ?>
8. Restore your WordPress database.
When your database is damaged or lost due to some reasons (hackers or upgrade errors), you can restore your WordPress database if you have a backup.
Log in to phpMyAdmin, select your WordPress database, click the "Import" button, click the "Browse" button, then select the backup file from your hard drive, and click the "Execute" button to import the database.
If all goes well, your WordPress functionality will return to normal.