MySQL has become a popular database in the past decade, and WordPress blogs use MySQL database. Although using plug-ins can solve some problems, executing SQL statements in phpMyAdmin is the most important thing to do when implementing certain special tasks. A concise method, here we summarize eight useful SQL statements for WordPress system to solve some practical problems. 1. Create a backup database Backing up a database is the first thing to do. You can simply back up the database by simply using 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 it, click "Yes" to download the database file to local. 2. Batch deletion of article revisions Versions after WordPress 2.6 have added a Post revisions function. Although it is a bit useful, the article revisions have increased the size of your database, and we can choose to batch delete. After logging in to phpMyAdmin, execute the following SQL statement to delete it in batches. DELETE FROM wp_posts WHERE post_type = "revision"; A true story is that a friend of mine set up a blog online. Once he spent a few days on vacation and didn't surf the Internet. When he came back, he logged into his blog and saw more than 5,000 comments waiting. Review, of course, is mostly spam, and it takes a lot of time to manually delete these comments, so we can use the following approach. After logging in to phpMyAdmin, execute the following SQL statement. DELETE from wp_comments WHERE comment_approved = '0'; 4. Modify article attributes After you install WordPress, the admin account is created, and many people use this account incorrectly to write blogs until they realize that this is not a personal account. The solution is that it takes a lot of time to modify the author attributes in each article. The following SQL statement can help you quickly complete this function. First, you need to find your correct username. Use the following SQL statement to find your user ID number. SELECT ID, display_name FROM wp_users; UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID; Many people use very complicated passwords in order to protect their blog from being hacked. Although this is a good thing, it often happens that the administrator password is forgotten. Of course, you can send you a link to reset your password via email, but if you can't access your email address, you 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; 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'; UPDATE wp_posts SET guid = replace(guid, 'http://www.oldsite.com','http://www.newsite.com'); UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldsite.com', 'http://www.newsite.com'); When you have blog performance, it is very important to know the number of query databases. In order to reduce database queries, we need to know how many queries are on a page. This time, you don’t need to log in to phpMyAdmin. You only need to modify the footer.php file and add the following lines of code to the end of the file. <?php if (is_user_logged_in()) { ?> When your database is corrupted or lost for some reason (hacker or upgrade error), if you have a backup, you can restore your WordPress database. Log in to phpMyAdmin, select your WordPress database, click the "Import" button, click the "Browse" button, and then select the backup file from your hard disk, and click the "Execute" button to import the database. If it goes well, your WordPress function will return to normal.
3. Batch delete spam comments
Be careful, while this solution is very useful for handling millions of spam, it also removes unapproved comments, so it is better to install Akismet to deal with spam.
Suppose this ID is NEW_AUTHOR_ID, and the ID of the administrator admin is OLD_AUTHOR_ID, then run the following SQL statement.
5. Manually reset password
MD5 is a built-in hash function in MySQL for converting passwords to hash values.
Next, you also need to use the following SQL to modify the GUID of the article.
Finally, use the following statement to replace all old domain names in the article with new domain names.
7. Display the number of SQL queries
<?php echo get_num_queries(); ?> queries in <?php timer_stop(1); ?> seconds.
<?php } ?>
8. Restore your WordPress database