Fourteen useful MySQL query statements

1. Manually change the password. In some cases, we forget the administrator password. Or the administrator password is maliciously modified after the website is attacked. At this time, we can only reset the administrator password. The following query statement uses the MD5 () function of MySQL to encrypt the password. UPDATEwp_usersSETuse

1. Manually change the password. In some cases, we forget the administrator password. Or the administrator password is maliciously modified after the website is attacked. At this time, we can only reset the administrator password. The following query statement uses the MD5 () function of MySQL to encrypt the password. UPDATE 'wp _ users' SET 'use

1. Manually change the password

In some cases, we forget the administrator password. Or the administrator password is maliciously modified after the website is attacked. At this time, we can only reset the administrator password.

The following query statement uses the MD5 () function of MySQL to encrypt the password.

UPDATE 'wp _ users' SET 'user _ pass' = MD5 ('Password') WHERE 'user _ login' = 'admin' LIMIT 1;

2. Authors of batch conversion to Japanese

Most people who are just using WordPress may directly use the "admin" user to directly publish logs. After you create a new user, you can use the following query statement to modify the authors of the original logs to new users in batches.

Before execution, you need to know the user IDs of old and new users.

UPDATE wp_posts SET post_author = NEW_AUTHOR_ID WHERE post_author = OLD_AUTHOR_ID;

3. Delete the log revision and its related information.

The log revision function is useful when multiple users jointly maintain a blog. However, it is annoying to add too many records to the database.

Run the following statement to delete the log revision record and other information (including the custom region) in the database. After execution, it will save a lot of database space.

DELETE a, B, c FROM wp_posts a WHERE. post_type = 'review' left join wp_term_relationships B ON (. ID = B. object_id) left join wp_postmeta c ON (. ID = c. post_id );

4. Batch delete spam comments

If your blog does not have Akismet or other alternatives installed, You will not enter the background for review for a while. Hundreds or even thousands of unreviewed comments may appear. And most of them may be Spam comments. At this time, if you delete it in the background, it may waste a lot of time. The following statement deletes all unreviewed comments in batch in the database.

I suggest you install Akismet or similar plug-ins, which saves your troubles.

DELETE from wp_comments WHERE comment_approved = '0 ';

5. Search for unused tags

Tag records are stored in the wp_terms data table. Sometimes some labels may be created but not used, and they will still be in the data table. The following statements can be used to query unused tags, 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. Search for and replace Data

This statement is not only used in the use of WordPress. You can use this MySQL query statement in many places. It uses the replace () function of MySQL to replace a string with another string you specified.

UPDATE table_name SET field_name = replace (field_name, 'string _ to_find ', 'string _ to_replace ');

7. Obtain the list of email addresses of comments

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

Select distinct comment_author_email FROM wp_comments;

8. Disable all plug-ins at one time

Although the new version of WordPress can be used to enable or disable plug-ins in batches in the background. However, when some plug-ins make the entire site inaccessible, we can use the following statement to disable all plug-ins in the site and then go to the WordPress background for debugging.

UPDATE wp_options SET option_value = ''where option_name = 'Active _ ins ';

9. Delete All tags

Tag information is stored in the wp_terms data table. Besides, it is stored in the same table as the classification and other information. If you want to delete all tag records, you cannot simply clear the wp_terms data table. This will cause loss of category information.

Run the following statement to delete the tag record and associated information of the log, but other types of information will not change.

DELETE a, B, cFROM database. prefix_terms AS a left join database. prefix_term_taxonomy AS c ON. term_id = c. term_id left join database. prefix_term_relationships AS B ON B. term_taxonomy_id = c. term_taxonomy_idWHERE (c. taxonomy = 'Post _ tag' AND c. count = 0 );

10. List useless log information

The Meta information of logs is usually created by the plug-in or custom region. They are very practical, 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, so that you can easily 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 old logs.

In some cases, you want to disable the previous log comment function. There are many reasons for this decision. You can use the following statement to disable the log comment feature released before the specified date.

UPDATE wp_posts SET comment_status = 'closed 'WHERE post_date <'2017-01-01' AND post_status = 'Publish ';

12. Replace the reviewer's Link

The replace () function of MySQL is mentioned above. This statement is a good example. If your site domain name has changed, you can use this statement to replace the links in comments posted on your site with new domain names in batches.

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

13. Replace the reviewer's email address

Similar to the previous one, replace email addresses in batches.

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 spam comments are very tricky. Some comments that seem to be related to articles are often published. Comments can usually contain links to malicious websites. When you find out, there may be a lot of such comments. Use the following code to conveniently delete a comment containing the specified link address at one time.

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