Useful SQL query in WordPress

WordPress stores all of its information snippets (including articles, pages, comments, blog links, plugin settings, etc.) in the MySQL database. Although WordPress users can edit the above information snippet through the site background editing. to a certain degree.
But assuming that you have hundreds of articles on the WordPress site, and you need to change the whole station range, then edit from the background is a bit time-consuming effort, and the probability of mistakes will increase. The best way is to enter the WordPress MySQL database to perform the necessary queries (changes). Through MySQL can quickly complete the above tasks, for you to save more time.
The following is to introduce some time-saving WordPress SQL query method.

Pre-backup
The WordPress database stores all the articles you’ve posted carefully, all the comments from your readers, and all the personalizations you’ve made to your site. So, no matter how confident you are, please remember that you must back up the WordPress database in advance. You can back up the backup plugin.

Add a custom field for all articles and pages
This code can add a custom field to all articles and pages in the WordPress database. What you need to do is replace the ‘UniversalCutomField’ in the code with the text you want, and change the ‘MyValue’ to the desired value.
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’);
If you only need to add a custom field for the article, you can use the following 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 a custom field for the page, you can use the following 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 the plug-in, the system stores the data through the article meta tag. Plug-in is deleted, the data will remain in the post_meta table, of course, then you no longer need these data, you can delete it. Remember to replace the ‘YourMetaKey’ in the code with the corresponding value you need before running the query.
DELETE FROM wp_postmeta WHERE meta_key = ‘YourMetaKey’;

Find useless tags
If you delete the old article in the WordPress database, the article will remain in the database and will appear in the tag list / tag cloud, as in the case of the previous plugin. The following query can help you find useless tags.
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;

Batch delete spam comments
Execute the following SQL commands:
DELETE FROM wp_comments WHERE wp_comments.comment_approved = ‘spam’;

Bulk delete all unaudited comments
This SQL query will delete all unaudited comments on your site without affecting the comments that have been reviewed.
DELETE FROM wp_comments WHERE comment_approved = 0

No comment on earlier articles
Specifies that the value of comment_status is open, closed, or registered_only. Also need to set the date (modify the code in 2010-01-01):
UPDATE wp_posts SET comment_status = ‘closed’ WHERE post_date <‘2010-01-01’ AND post_status = ‘publish’;

Disable / activate trackback and pingback
Specifies that the value of comment_status is 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 & Trackbacks before a date
Specifies that the value of ping_status is open, closed, or registered_only. Also need to set the date (modify the code in 2010-01-01):
UPDATE wp_posts SET ping_status = ‘closed’ WHERE post_date <‘2010-01-01’ AND post_status = ‘publish’;

Delete comments for specific URLs
When you find a lot of spam comments with the same URL link, you can use the following query to delete these comments at once. % Means that all URLs containing the string within the “%” symbol will be deleted.
DELETE from wp_comments WHERE comment_author_url LIKE “% nastyspamurl%”;

Identify and remove articles from “X” days ago
Find all articles for “X” days (note that replace X with the corresponding value):
SELECT * FROM `wp_posts`
WHERE `post_type` = ‘post’
AND DATEDIFF (NOW (), `post_date`>> X

Remove all articles from “X” days ago:
DELETE FROM `wp_posts`
WHERE `post_type` = ‘post’
AND DATEDIFF (NOW (), `post_date`>> X

Remove unwanted short codes
When you decide not to use short code, they will not disappear automatically. You can use a simple SQL query command to remove all unwanted short codes. Replace “tweet” with the corresponding short code name:
UPDATE wp_post SET post_content = replace (post_content, ‘[tweet]’, ”);

Change the article to page
Still just through PHPMyAdmin run a SQL query can get:
UPDATE wp_posts SET post_type = ‘page’ WHERE post_type = ‘post’
Convert the page to article:
UPDATE wp_posts SET post_type = ‘post’ WHERE post_type = ‘page’

Change the author attribute on all articles
First, the author’s ID is retrieved by the following SQL command:
SELECT ID, display_name FROM wp_users;
After successfully getting the author’s new ID, insert the following command, remember to replace NEW_AUTHOR_ID with the new author ID, and replace the old author ID with OLD_AUTHOR_ID.
UPDATE wp_posts SET post_author = NEW_AUTHOR_ID WHERE post_author = OLD_AUTHOR_ID;

Batch delete article revision history
Article revision history can be very useful to save, it can be very annoying. You can manually delete the revision history, you can also use the SQL query to save their time.
DELETE FROM wp_posts WHERE post_type = “revision”;

Disable / activate all WordPress plugins
Activate a plug-in and found unable to log in WordPress management panel, and try the following query command, it will immediately disable all plug-ins, so you re-login.
UPDATE wp_options SET option_value = ‘a: 0: {}’ WHERE option_name = ‘active_plugins’;

Change the target URL for the WordPress site
After moving the WordPress blog (template file, upload content & database) from one server to another, then you need to tell WordPress your new blog address.
When using the following command, note that http://www.old-site.com is replaced by your original URL and http://www.new-site.com is replaced by a new URL.
First of all:
UPDATE wp_options
SET option_value = replace (option_value, ‘http://www.old-site.com’, ‘http://www.new-site.com’)
WHERE option_name = ‘home’ OR option_name = ‘siteurl’;
Then use the following command to change the URL in wp_posts:
UPDATE wp_posts SET guid = replace (guid, ‘http: //www.old-site.com’, ‘http: //www.new-site.com);
Finally, search the contents of the article to ensure that the new URL link is not confused with the original link:
UPDATE wp_posts
SET post_content = replace (post_content, ‘http://www.ancien-site.com’, ‘http://www.nouveau-site.com’);

Change the default user name Admin
Replace one of your YourNewUsername with a new username.
UPDATE wp_users SET user_login = ‘YourNewUsername’ WHERE user_login = ‘Admin’;

Manually reset the WordPress password
If you are the only author on your WordPress site and you have not modified the default username, you can use the following SQL query to reset the password (replace the PASSWORD with the new password):
UPDATE `wordpress`.`wp_users` SET` user_pass` = MD5 (‘PASSWORD’)
WHERE `wp_users`.`user_login` =` admin` LIMIT 1;

Search and replace article content
OriginalText replaced with replaced content, ReplacedText replaced with the target content:
UPDATE wp_posts SET `post_content`
= REPLACE (`post_content`,
‘OriginalText’,
‘ReplacedText’);

Change the image URL
The following SQL command can help you modify the image path:
UPDATE wp_posts
SET post_content = REPLACE