Collation of some practical WordPress background MySQL operation commands

But if you have hundreds of thousands of articles on your WordPress site and you need to make site-wide changes, editing from the back end is a bit time-consuming and laborious, and your chances of making a mistake go up. The best way to do this is to go into WordPress's MySQL database and perform the necessary queries (changes). With MySQL, you can do these tasks quickly, saving you even more time.

The following are some time-saving WordPress SQL query methods.

Backup in advance
The WordPress database stores every article you craft, all the comments from your readers, and all the personalization you do for your site. So, no matter how confident you are in yourself, remember to back up the WordPress database first. You can use the backup plugin for backup.

Add custom fields for all articles and pages
This code adds a custom field for all articles and pages in the WordPress database. All you need to do is replace 'UniversalCutomField' in the code with the text you want, and then change 'MyValue' to the value you want.

 
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 this 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 this 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 the article meta data
When you install or remove a plug-in, the system stores data via the meta tags. After the plugin is deleted, the data will still be in the post_meta table, but you don't need the data anymore, so you can delete it. Remember to replace the 'YourMetaKey' in the code with the value you need before running the query.

 
DELETE FROM wp_postmeta WHERE meta_key = 'YourMetaKey'; 

Find garbage tags
If you perform a query in the WordPress database to delete old posts, as you did when you deleted the plugin, the tags that belong to the posts will remain in the database and will appear in the tag list/tag cloud. The following query will help you find the tags that are not useful.

 
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; 

Bulk removal of spam comments
Execute the following SQL command:

 
DELETE FROM wp_comments WHERE wp_comments.comment_approved = 'spam'; 

Batch removal of all unmoderated comments
This SQL query removes all unmoderated comments from your site and does not affect moderated comments.

 
DELETE FROM wp_comments WHERE comment_approved = 0 

Do not comment on earlier articles
Specify the value of comment_status as open, closed, or registered_only.
You also need to set the date (modify 2010-01-01 in the code) :

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

Deactivate/activate trackback and pingback
Specify the value of comment_status as open, closed, or registered_only.
Activate pingbacks/trackbacks for all users:

 
UPDATE wp_posts SET ping_status = 'open'; 

Disable pingbacks/trackbacks for all users:

 
UPDATE wp_posts SET ping_status = 'closed'; 

Activate/deactivate Pingbacks before a certain 1 date & Trackbacks
Specify the value of ping_status as open, closed, or registered_only.
You also need to set the date (modify 2010-01-01 in the 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'; 

0
Delete comments for specific URL
When you find that many spam comments have the same URL link, take advantage of the query 1 below to remove the comments. % means that all URL containing strings within the "%" symbol will be deleted.

 
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'; 

1
Identify and delete "X" articles from days ago
Find all articles from "X" days ago (note that X is replaced with the corresponding value) :

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

Delete all posts from "X" days ago:

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

Remove unwanted short code
Short code doesn't automatically disappear when you decide to stop using it. You can use a simple SQL query to remove all unwanted short code. Replace "tweet" with the corresponding short code name:

 
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'; 

4
Turn the article into a page
You can still run an SQL query through PHPMyAdmin:

 
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'; 

5
Convert pages to articles:

 
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'; 

6
Change the author property on all articles
First retrieve the author's ID via the SQL command below:

 
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'; 

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

 
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'; 

8
Batch delete article revision history
Article revision history preservation can be very practical, can also be very annoying. You can delete the revision history manually or save yourself time with the SQL query.

 
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'; 

9
Deactivate/activate all WordPress plug-ins
If you can't log in to the WordPress admin panel after activating a plugin, try the following query command, which immediately disables all plugins and lets you log in again.

 
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'; 

0
Change the target URL for the WordPress site
Put the WordPress blog (template file, upload content & After moving from one server to another, you need to tell WordPress your new blog address.
, paid attention to when using the following commands will http: / / www old - site. com into your original URL http: / / blog doucube. com into new URL address.
First of all:

 
UPDATE wp_options 
SET option_value = replace(option_value, 'http://www.old-site.com', 'http://blog.doucube.com') 
WHERE option_name = 'home' OR option_name = 'siteurl'; 

Then change URL in wp_posts with the following command:

 
UPDATE wp_posts SET guid = replace(guid, 'http://www.old-site.com','http://blog.doucube.com); 

Finally, search the article content to make sure the new URL link is not confused with the original:

 
UPDATE wp_posts SET post_content = replace(post_content, ' http://www.ancien-site.com ', ' http://blog.doucube.com '); 

Change the default username Admin
Replace YourNewUsername with the new username.

 
UPDATE wp_users SET user_login = 'YourNewUsername' WHERE user_login = 'Admin'; 

Reset the WordPress password manually
If you are the sole author on your WordPress site and you have not changed the default user name, you can reset the password using the SQL query below (replace PASSWORD with a new password) :

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

Search and replace article content
Replace OriginalText with replaced content, ReplacedText with target content:

 
UPDATE wp_posts SET `post_content` = REPLACE (`post_content`, 'OriginalText','ReplacedText'); 

Change image URL
The SQL command below can help you change the image path:

 
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'; 

7