14 useful MySQL Query statements

1. Manually modify the password

In some cases, we have forgotten the administrator's password. Or site after the attack, the administrator password has been maliciously modified. At this point, all we can do is reset the administrator's password.

The following query statement uses the MySQL MD5 () function to encrypt our passwords MD5.

UPDATE ' wp_users ' SET ' user_pass ' = MD5 (' PASSWORD ') WHERE ' user_login ' = ' admin ' LIMIT 1;

2. The author of the batch conversion Japanese style

Most people who just use WordPress may be using the "admin" user directly to publish the log directly. When you create a new user, you can use the following query if you want to bulk modify the author of the original journal to a new user.

Before executing, you need to know the user ID of the old user and the new user.

UPDATE wp_posts SET post_author=new_author_id WHERE post_author=old_author_id;

3. Delete the journal revisions and their associated information

The revised version of the log is useful when multiple users are working together to maintain a blog. However, it is also annoying to add too many records to the database.

Execute the following statement to remove the log revision records from the database and other information in it, including custom areas, and so on. After execution, a lot of database space is saved.

DELETE a,b,c from wp_posts a WHERE a.post_type = ' revision ' left JOIN wp_term_relationships b on (a.id = b.object_id) left JOIN Wp_postmeta C on (a.id = c.post_id);

4. Bulk Delete spam comments

In your blog does not install Akismet or other alternatives, under the premise of a period of time did not enter the background for review review. There may be hundreds of or even thousands of reviews that are not audited. And most likely a spam comment. This time, if you are in the background to delete, you may waste a lot of time. The following statement can bulk delete all unaudited comments in the database.

Advise a sentence, or install Akismet or similar plug-in bar, you can save a lot of trouble.

DELETE from wp_comments WHERE comment_approved = ' 0 ';

5. Find unused labels

The record of the label is stored in the Wp_terms data table. Sometimes, some tags may be created but not used, and they will still be in the datasheet. The following statements can query these unused labels, and you can safely delete them.

SELECT * from wp_terms wt INNER JOIN wp_term_taxonomy WTT on wt.term_id=wtt.term_id WHERE wtt.taxonomy= ' Post_tag ' and WTT. count=0;

6. Find and replace data

This statement is not only used in the process of WordPress use. You can use this MySQL query in many places. The replace () function in MySQL replaces a section of string with another string that you specify.

UPDATE table_name SET field_name = replace (field_name, ' string_to_find ', ' string_to_replace ');

7. Get the email address list of the visitors who made comments

This statement returns a list of the e-mail addresses of users who have commented on your blog. The DISTINCT parameter in the statement is used to ensure that each e-mail address is displayed only once.

SELECT DISTINCT comment_author_email from wp_comments;

8. Disable all plugins at once

Although the new version of WordPress can be directly in the background of the plug-in to the bulk of the enable and disable operations. But when some plug-ins cause the entire site can not be normal access, we can use the following statements to disable all the Plug-ins in the site, and then into the WordPress backstage debugging operations.

UPDATE wp_options SET option_value = ' WHERE option_name = ' active_plugins ';

9. Delete all labels

The information of the label is stored in the Wp_terms datasheet. Moreover, it is stored in the same table as the classification and some other information. If you want to delete all of the label records, it is not easy to empty wp_terms this datasheet. This causes the classification information to be lost.

Execute the following statement, you can simply delete the label record and the associated information with the log, and other categories, and other information will not change.

DELETE A,b,cfrom Database.prefix_terms as a left JOIN database.prefix_term_taxonomy as C on a.term_id = c.term_id left JOI N Database.prefix_term_relationships as B on b.term_taxonomy_id = c.term_taxonomy_idwhere (c.taxonomy = ' Post_tag ' and C. Count = 0);

10. Enumerate the Useless log information

The Meta information for a log is usually created by a plug-in or custom area. They are very useful, but they also make your database size quickly bloated. The following statement shows the records in the Postmeta table that do not have a corresponding relationship in the post table, making it easier for you to clean up the database.

SELECT * from Wp_postmeta pm left JOIN wp_posts wp on wp.id = pm.post_id WHERE wp.id is NULL;

11. Disable the comment function of the old log

At some point, you want to turn off the previous log comment feature. There are a number of reasons why you might make such a decision, and you can use the following statement to turn off the log comment functionality that was published before the specified date.

UPDATE wp_posts SET comment_status = ' closed ' WHERE post_date < ' 2009-01-01 ' and post_status = ' publish ';

12. Replace the reviewer's link

The replace () function of MySQL is mentioned above. The statement is a good example. If your site's domain name has changed, you can use it to bulk replace the link in the comments that you posted on your own station with the new domain name.

UPDATE wp_comments SET comment_author_url = REPLACE (Comment_author_url, ' http://oldurl.com ', ' http://newurl.com ');

13. Replace the email address of the reviewer

Similar to the previous one, bulk replace the e-mail address.

UPDATE wp_comments SET comment_author_email = REPLACE (comment_author_email, ' old-email@address.com ', ' New-email@address.com ');

14. Delete comments containing the specified link

Some spammers still have a lot of loopholes. Frequently publish comments that seem to be relevant to the article, and comment on the links that usually contain malicious Web sites. When you find out, this may be a lot of comments. Using the following code, you can easily delete a comment that contains a specified link address at once.

DELETE from wp_comments WHERE comment_author_url like "%wpbeginner%";