WeChat official account

Twenty Practical WordPress SQL Queries

web front end five thousand six hundred and forty-two 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

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, 'UniversalCustomField'
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

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, 'UniversalCustomField'
AS meta_key 'MyValue AS meta_value
FROM wp_posts WHERE ID NOT IN
(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

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, 'UniversalCustomField'
AS meta_key 'MyValue AS meta_value
FROM wp_posts WHERE ID NOT IN
(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

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

SELECT * From wp_terms wtINNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id
WHERE wtt.taxonomy= 'post_tag' AND wtt. count =0;

Batch delete junk comments

Execute the following SQL commands:

SQL code

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

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

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

UPDATE wp_posts SET ping_status = 'open' ;

Disable pingbacks/trackbacks for all users:

SQL code

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

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

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

SELECT * FROM `wp_posts`
WHERE `post_type` = 'post' AND DATEDIFF(NOW(), `post_date`) > X

Delete all articles before "X":

SQL code

DELETE FROM `wp_posts`
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

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

UPDATE wp_posts SET post_type = 'page' WHERE post_type = 'post'

To convert a page to an article:

SQL code

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

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

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

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

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

UPDATE wp_options
SET option_value = replace (option_value, 'Here is the old domain name' , 'Here is the new domain name' )
WHERE option_name = 'home' OR option_name = 'siteurl' ;

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

SQL code

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

UPDATE wp_posts
SET post_content = replace (post_content, 'Here is the old domain name' , 'This is the new domain name' );

Change the default user name Admin

Replace YourNewUsername with the new user name.

SQL code

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