In the past decade, MySQL database has become popular, and WordPress blog using a MySQL database, although the use of plug-ins can solve some problems, but to achieve some special tasks, execute SQL statements in phpMyAdmin is the most concise way, here is a useful summary of eight WordPress system SQL statements used to solve some practical problems encountered.
1. create a backup of the database
Backup of the database is the first thing to do, just by following a simple method you can back up the database:
After login phpMyAdmin. Choose your WordPress database, and then click on the "Export" button, select a compression method (you can use gzip) and click the "Go" button, when the browser when prompted to download, click "Yes", download the file to the database locally.
2. bulk delete articles Amendment
WordPress2.6 later version adds a Post revisions feature, though some use, but the article is amended to increase the size of your database, we can choose to bulk delete.
After logging in phpMyAdmin execute the following SQL statement to bulk delete.
DELETE FROM wp_posts WHERE post_type = "revision";
3. batch delete spam comments
A true story is that one of my friends set up a blog on the Internet, there are times he spent a few days on vacation, no Internet, and when he came back, log in to your blog, saw more than 5000 comments Wait audit, of course, most of them are spam comments to manually delete these comments to spend a lot of time, so we can use the following approach.
Execute the following SQL statement after login phpMyAdmin.
DELETE from wp_comments WHERE comment_approved = '0';
Be careful, though this solution is useful for processing millions of ordinary garbage, but will remove comments without approval, so it is best to install or use Akismet to fight comment spam.
4. modify article properties
After you install WordPress, admin account is created, a lot of people are wrong to use this account to write a blog, until they realize that this is not a personal account.
The solution, each article to modify the properties need a lot of time, the following SQL statement can help you quickly complete this function.
First you have to find your correct user name, use the following SQL statements can be found in your user ID number.
SELECT ID, display_name FROM wp_users;
Assuming that this ID is NEW_AUTHOR_ID, and administrator admin ID for
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.Manual Reset Password
A lot of people in order to protect their own blog not being hacked, using a very complex password, although this is a good thing, but often forgotten administrator password things will happen.
Of course, you can send to your WordPress link to reset your password via e-mail, but if you can not access your e-mail address, then had 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 hash function is a built-in MySQL for converting password hashes.
6.change WordPress domain
Sometimes you may want to change your blog's domain name, but WordPress will put your name stored in the database, so you have to use the following SQL statement to modify.
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';
Then, you have to use the following SQL GUID of the article will also be modified.
UPDATE wp_posts SET guid = replace (guid, 'http: //www.oldsite.com','http: //www.newsite.com');
Finally, use the following statement will replace the article all the old domain to the new domain name.
UPDATE wp_posts SET post_content = replace (post_content, 'http://www.oldsite.com', 'http://www.newsite.com');
7. showing the number of SQL queries
You have something to blog in performance when the number of queries the database learned is very important, in order to reduce database query, we need to know on one page in the end how many queries.
This time, do not need to log in phpMyAdmin, you only need to modify the footer.php file, at the end of the file add the following lines of code.
<? 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 for some reason (hacking or upgrade error) is damaged or lost, if you have a backup, then you can restore your WordPress database.
Sign in phpMyAdmin, select your WordPress database, click "Import" button, click "Browse" button, then "Run" button to import the database from your hard drive to select the backup file, point.
If all goes well, your WordPress functions will return to normal.
.jpg)
.png "English"){kind=small}
Posts
