Some practical WordPress backend MySQL operation commands compiled

However, suppose you have hundreds or thousands of articles on your WordPress website and you need to make site-wide changes. At this time, editing one by one from the background is a bit time-consuming and laborious, and the chance of making mistakes will increase. The best way is to go into WordPress’s MySQL database and perform the necessary queries (changes). The above tasks can be completed quickly through MySQL, saving you more time.

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

Back up in advance
The WordPress database stores every article you carefully publish, all comments from your readers, and all the changes you make to your website Personalization. Therefore, no matter how confident you are, please remember to back up your WordPress database beforehand. You can back up via the backup plugin.

Add a custom field to all posts and pages
This code can add a custom field to all posts and pages in the WordPress database. All you need to do is replace ‘UniversalCutomField’ in the code with the text you need, and then change ‘MyValue’ to the required value.

Copy code The code is as follows:

 
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 a custom field to the article, you can Use the following code:

Copy code The code is as follows:

 
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:

Copy the code The code is as follows:

 
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 post meta data
When you install or remove the plug-in, the system passes the post meta Tags store data. After the plug-in is deleted, the data will still remain in the post_meta table. Of course, you no longer need the data and can delete it. Remember to replace ‘YourMetaKey’ in the code with the corresponding value you need before running the query.

Copy code The code is as follows:

DELETE FROM wp_postmeta WHERE meta_key = 'YourMetaKey';

Find useless tags
If you execute a query in the WordPress database to delete old articles, just like when you deleted the plugin before, the tags to which the article belongs will remain in the database and will also appear in the tag list/tag cloud. The following query can help you find useless tags.

Copy code The code is as follows:

 
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; 

Delete spam comments in batches
Execute the following SQL command:

Copy code The code is as follows:

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

Delete all unmoderated comments in batches
This SQL query will Delete all unmoderated comments on your site without affecting moderated comments.

Copy code The code is as follows:

DELETE FROM wp_comments WHERE comment_approved = 0

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

Copy the code The code is as follows:

 
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:

Copy code The code is as follows:

UPDATE wp_posts SET ping_status = 'open ';

Disable pingbacks/trackbacks for all users:

Copy code The code is as follows:

UPDATE wp_posts SET ping_status = 'closed';

Activate/deactivate Pingbacks & Trackbacks before a certain date
Specify the value of ping_status as open, closed or registered_only.
In addition, you need to set the date (modify 2010-01-01 in the code):

Copy the code The code is as follows:

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

Delete comments for specific URL
When you find a lot of spam The comments all have the same URL link, and you can use the following query to delete these comments at once. % means that all URLs containing strings within the "%" symbol will be deleted.

Copy code The code is as follows:

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

Identify and delete articles from "X" days ago
Find all articles from "X" days ago (note to replace X with the corresponding value):

Copy code The code is as follows:

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

Delete all posts from " ` WHERE `post_type` = 'post'AND DATEDIFF(NOW(), `post_date`) > , they will not disappear automatically. You can remove all unwanted shortcodes with a simple SQL query command. Replace "tweet" with the corresponding shortcode name:

Copy code The code is as follows:

UPDATE wp_post SET post_content = replace( post_content, '[tweet]', '' ) ;

Convert the article to a page
It can still be done by running a SQL query through PHPMyAdmin:

Copy code The code is as follows:

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

Convert the page to Article:

Copy code The code is as follows:

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

Change the author attribute on all articles
First retrieve the author's ID via the following SQL command:

Copy code The code is as follows :

SELECT ID, display_name FROM wp_users;

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

Copy code The code is as follows:

UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;

Delete article revision history in batches
Saving article revision history can be very practical or very annoying. You can manually delete the revision history, or you can use SQL queries to save yourself time.

Copy code The code is as follows:

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 query command below. It will immediately disable all plug-ins and allow you to log in again.

Copy code The code is as follows:

 
UPDATE wp_options SET option_value = 'a:0:{}' WHERE option_name = 'active_plugins'; 

Change the target URL of the WordPress site
Put the WordPress blog (template file , upload content & database) after moving from one server to another, next you need to tell WordPress your new blog address.
When using the following commands, be sure to replace http://www.old-site.com with your original URL and http://blog.doucube.com with the new URL address.
First:

Copy code The code is as follows:

 
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 use the following command to change the URL in wp_posts:

Copy the code The code is as follows:

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

Finally, search the article content to ensure that the new URL link is the same as the original link No confusion:

Copy the code The code is as follows:

 
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.

Copy code The code is as follows:

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 modified the default username, then you can use the following SQL query to reset the password (put where Change the PASSWORD to a new password):

Copy code The code is as follows:

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

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

Copy code The code is as follows:

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

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

Copy Code The code is as follows:

 
UPDATE wp_postsSET post_content = REPLACE (post_content, 'src="http://www.myoldurl.com', 'src="http:// blog.doucube.com'); 

Some practical WordPress backend MySQL operation commands compiled_PHP tutorial