Twenty Practical WordPress SQL Queries

web front end five thousand four hundred and seventeen 13 years ago (2011-03-31)

WordPress takes all its information fragments (including articles, pages, comments, blog links plug-in unit Settings, etc.) stored in MySQL data base Medium. Although WordPress users can control the above information fragments through the background editing of the website.

However, if there are hundreds of articles on your WordPress website and you need to make site wide changes, editing one by one from the background will be a bit time-consuming and laborious, and the probability of making mistakes will also increase. The best way is to enter the MySQL database of WordPress to execute necessary queries (changes). MySQL can quickly complete the above tasks, saving you more time.

Here are some time-saving and labor-saving WordPress SQL query methods.

Backup in advance

The WordPress database stores every article you carefully publish, all comments from your readers, and all personalized settings you have made for your website. Therefore, no matter how confident you are, remember to backup the WordPress database in advance. You can back up through the backup plug-in.

Add custom fields for all posts and pages

This code can add a custom field for all articles and pages in the WordPress database. What you need to do is to put the‘ UniversalCutomField‘ Replace with the text you need, and then put‘ MyValue‘ Change to the desired value.

SQL code
  1. INSERT   INTO  wp_postmeta  (post_id, meta_key, meta_value)   

  2.   SELECT  ID  AS  post_id,   'UniversalCustomField'   

  3.   AS  meta_key  'MyValue AS  meta_value FROM wp_postsWHERE ID NOT IN (SELECT  post_id FROM wp_postmeta WHERE meta_key = ' UniversalCustomField');    

 

If you only need to add custom fields to the article, you can use the following code:

SQL code
  1. INSERT   INTO  wp_postmeta  (post_id, meta_key, meta_value)   

  2.   SELECT  ID  AS  post_id,   'UniversalCustomField'   

  3.   AS  meta_key  'MyValue AS  meta_value  

  4.  FROM  wp_posts WHERE ID NOT IN  

  5.  (SELECT  post_id FROM wp_postmeta WHERE meta_key = ' UniversalCustomField ')`` AND post_type = ' post';   

 

If you only need to add custom fields to the page, you can use the following code:

SQL code
  1. INSERT   INTO  wp_postmeta  (post_id, meta_key, meta_value)   

  2.   SELECT  ID  AS  post_id,   'UniversalCustomField'   

  3.   AS  meta_key  'MyValue AS  meta_value  

  4.  FROM  wp_posts WHERE ID NOT IN  

  5.  (SELECT  post_id FROM wp_postmeta WHERE meta_key = ' UniversalCustomField ')AND `post_type` = ' page';   

 

Delete article meta data

When you install or delete plug-ins, the system stores data through the article meta tag. After the plug-in is deleted, the data will still be saved in the post_meta table. Of course, you no longer need these data at this time, and you can delete them completely. Remember to put the‘ YourMetaKey‘ Replace with the corresponding value you need.

SQL code
  1. DELETE   FROM   wp_postmeta  WHERE  meta_key =  'YourMetaKey' ;   

 

Find unwanted tags

If you execute a query in the WordPress database to delete an old article, the tag of the article will remain in the database and will also appear in the tag list/tag cloud, just as it did when you deleted the plug-in. The following query can help you find useless tags.

SQL code
  1. SELECT  *  From  wp_terms wtINNER  JOIN   wp_term_taxonomy wtt  ON  wt.term_id=wtt.term_id    

  2. WHERE  wtt.taxonomy= 'post_tag'    AND  wtt. count =0;   

 

Batch delete junk comments

Execute the following SQL commands:

SQL code
  1. DELETE   FROM   wp_comments  WHERE  wp_comments.comment_approved =  'spam' ;   

 

Batch delete all unapproved comments

This SQL query will delete all unapproved comments on your website without affecting the approved comments.

SQL code
  1. DELETE   FROM   wp_comments  WHERE  comment_approved = 0  

 

Prohibit comments on earlier articles

Specify the comment_status as open, closed, or registered_only. In addition, you need to set the date (modify 2010-01-01 in the code):

SQL code
  1. UPDATE  wp_posts   SET  comment_status =  'closed'   WHERE  post_date  <  '2010-01-01'   AND  post_status =  'publish' ;

Disable/activate trackback and pingback

Specify the comment_status as open, closed, or registered_only.

Activate pingbacks/trackbacks to all users:

SQL code
  1. UPDATE  wp_posts  SET  ping_status =  'open' ;   

 

Disable pingbacks/trackbacks for all users:

SQL code
  1. UPDATE  wp_posts  SET  ping_status =  'closed' ;   

 

Activate/deactivate pingbacks&trackbacks before a certain date

Specify ping_status as open, closed, or registered_only. In addition, you need to set the date (modify 2010-01-01 in the code):

SQL code
  1. UPDATE  wp_posts   SET  ping_status =  'closed'   WHERE  post_date  <  '2010-01-01'   AND  post_status =  'publish' ;   

 

Delete comments for a specific URL

When you find that many spam comments have the same URL link, you can use the following query to delete these comments at one time.% Indicates that all URLs containing strings within the "%" symbol will be deleted.

SQL code
  1. DELETE   from   wp_comments  WHERE  comment_author_url  LIKE   "%nastyspamurl%"   ;   

 

Identify and delete articles older than "X" days

Find all articles before "X" (note to replace X with corresponding value):

SQL code
  1. SELECT  *  FROM  `wp_posts`   

  2.   WHERE  `post_type`  =  'post' AND  DATEDIFF(NOW(),  `post_date`) > X  

 

Delete all articles before "X":

SQL code
  1. DELETE   FROM  `wp_posts`   

  2.   WHERE  `post_type`  =  'post' AND  DATEDIFF(NOW(),  `post_date`) > X  

 

Delete unnecessary short codes

When you decide not to use short code, it will not disappear automatically. You can use a simple SQL query command to delete all unnecessary short codes. Replace "tweet" with the corresponding short code name:

SQL code
  1. UPDATE  wp_post   SET  post_content =  replace (post_content,  '[tweet]' ''  )  ;   

 

Turn an article into a page

You can still run an SQL query through PHPMyAdmin:

SQL code
  1. UPDATE  wp_posts   SET  post_type =  'page'   WHERE  post_type =   'post'   

 

To convert a page to an article:

SQL code
  1. UPDATE  wp_posts   SET  post_type =  'post'   WHERE  post_type =   'page'   

 

Change author properties on all posts

First, retrieve the author's ID through the following SQL command:

SQL code
  1. SELECT  ID,  display_name  FROM  wp_users;   

 

After successfully obtaining the new and old ID of the author, insert the following command, remember to replace NEW_AUTHOR_ID with the new author ID, and replace OLD_AUTHOR_ID with the old author ID.

SQL code
  1. UPDATE  wp_posts   SET  post_author=NEW_AUTHOR_ID  WHERE  post_author=OLD_AUTHOR_ID;   

 

Batch Delete Article Revision History

The preservation of the revision history of articles can be very practical and annoying. You can delete the revision history manually, or use SQL queries to save yourself time.

SQL code
  1. DELETE   FROM   wp_posts  WHERE  post_type =  "revision" ;   

 

Deactivate/activate all WordPress plug-ins

After activating a plug-in, you find that you cannot log in to the WordPress management panel. Try the following query command. It will immediately disable all plug-ins and let you log in again.

SQL code
  1. UPDATE  wp_options   SET  option_value =  'a:0:{}'   WHERE  option_name  =  'active_plugins' ;   

 

Change the target URL of the WordPress website

Transfer WordPress blog (template file, uploaded content&database) from one The server After moving to another server, you need to tell WordPress your new blog address.

When using the following command, pay attention to replacing the old domain name with your original URL and the new domain name with the new URL address.

first:

SQL code
  1. UPDATE  wp_options     

  2. SET  option_value =  replace (option_value,  'Here is the old domain name' 'This is the new domain name' )    

  3. WHERE  option_name  =  'home'   OR  option_name =  'siteurl' ;   

 

Then use the following command to change the URL in wp_posts:

SQL code
  1. UPDATE  wp_posts   SET  guid =  replace (guid,  'Here is the old domain name' , 'Here is the new domain name);   

 

Finally, search the content of the article to ensure that the new URL link is not confused with the original link:

SQL code
  1. UPDATE  wp_posts     

  2. SET  post_content =  replace (post_content,  'Here is the old domain name' 'Here is the new domain name' );   

 

Change the default user name Admin

Replace YourNewUsername with the new user name.

SQL code
  1. UPDATE  wp_users   SET  user_login =  'YourNewUsername'   WHERE  user_login  =  'Admin' ;   

 

Manually reset WordPress password

If you are the only author on your WordPress website and you have not changed the default user name, you can use the following SQL query to reset the password (change the PASSWORD into a new password):

SQL code
  1. UPDATE  `wordpress`.` wp_users`   SET  `user_pass` = MD5( 'PASSWORD' )    

  2. WHERE  `wp_users`.` user_login`  =`admin` LIMIT 1;   

 

Search and replace article content

Original Text is replaced with the replaced content, and ReplacedText is replaced with the target content:

SQL code
  1. UPDATE  wp_posts  SET  `post_content`   

  2.  =  REPLACE  (`post_content`,   

  3.   'OriginalText' , 'ReplacedText' );   

 

Change picture URL

The following SQL commands can help you modify the image path:

SQL code
  1. UPDATE  wp_postsSET post_content  =  REPLACE  (post_content,  'src="Here is the old domain name ' ,     

  2. 'src="Here is the new domain name ' );