WordPRess stores all its pieces of information (including posts, pages, comments, blog links, plugin settings, etc.) in a MySQL database. Although WordPress users can control the above information fragments through website background editing. To a certain extent.
But suppose you have hundreds or thousands of articles on your WordPress website and you need to make site-wide changes. At this time, editing them one by one from the background is a bit time-consuming and laborious, and the chance of making mistakes will also 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 beforehand
The WordPress database stores every post you carefully publish, all the comments from your readers, and all the personalization you have made to your website. 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 custom fields to all posts and pages
This code adds 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.
The following is the quoted content:
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:
The following is the quoted content:
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 just need to add custom fields to the page, you can use the following code:
The following is the quoted content:
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 remove a plugin, the system stores data through post meta tags. 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.
The following is the quoted content:
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 articles belong will remain in the database and will also appear in the tag list/tag cloud. The following query can help you find useless tags.
The following is the quoted content:
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:
The following is the quoted content:
DELETE FROM wp_comments WHERE wp_comments.comment_approved = 'spam';
Delete all unmoderated comments in bulk
This SQL query will delete all unmoderated comments on your website and will not affect moderated comments.
The following is the quoted content:
DELETE FROM wp_comments WHERE comment_approved = 0
Disable comments on older articles
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):
The following is the quoted content:
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 to all users:
The following is the quoted content:
UPDATE wp_posts SET ping_status = 'open';
Disable pingbacks/trackbacks for all users:
The following is the quoted content:
UPDATE wp_posts SET ping_status = 'closed';
Activate/deactivate Pingbacks & Trackbacks from 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):
The following is the quoted content:
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 many spam comments with the same URL link, you can use the following query to delete these comments at once. % means that all URLs containing strings within the "%" symbol will be deleted.
The following is the quoted content:
DELETE from wp_comments WHERE comment_author_url LIKE "%nastyspamurl%" ;
Identify and delete articles older than "X" days
Find all articles before "X" days ago (note to replace X with the corresponding value):
The following is the quoted content:
SELECT * FROM `wp_posts`
WHERE `post_type` = 'post'AND DATEDIFF(NOW(), `post_date`) > X
Delete all articles older than "X" days:
The following is the quoted content:
DELETE FROM `wp_posts`
WHERE `post_type` = 'post'AND DATEDIFF(NOW(), `post_date`) > X
Remove unnecessary shortcodes
Shortcodes don't automatically disappear when you decide you no longer want to use them. You can remove all unwanted shortcodes with a simple SQL query command. Replace "tweet" with the corresponding shortcode name:
The following is the quoted content:
UPDATE wp_post SET post_content = replace(post_content, '[tweet]', '' );
Convert articles to pages
Still just run a SQL query through phpMyAdmin:
The following is the quoted content:
UPDATE wp_posts SET post_type = 'page' WHERE post_type = 'post'
Convert pages into articles:
The following is the quoted content:
UPDATE wp_posts SET post_type = 'post' WHERE post_type = 'page'
Change author attributes on all posts
First retrieve the author's ID via the following SQL command:
The following is the quoted content:
SELECT ID, display_name FROM wp_users;
After successfully obtaining the old and new IDs of the author, insert the following commands, remember to replace NEW_AUTHOR_ID with the new author ID and OLD_AUTHOR_ID with the old author ID.
The following is the quoted content:
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 both practical and annoying. You can manually delete the revision history, or you can use SQL queries to save yourself time.
The following is the quoted content:
DELETE FROM wp_posts WHERE post_type = "revision";
Deactivate/activate all WordPress plugins
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 allow you to log in again.
The following is the quoted content:
UPDATE wp_options SET option_value = 'a:0:{}' WHERE option_name = 'active_plugins';
Change your WordPress website’s target URL
After moving your WordPress blog (template files, uploaded content & database) from one server to another, you next 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://www.new-site.com with the new URL address.
first:
The following is the quoted content:
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:
The following is the quoted content:
UPDATE wp_posts SET guid = replace(guid, 'http://www.old-site.com','http://www.new-site.com);
Finally, search the article content to make sure the new URL is not confused with the original link:
The following is the quoted content:
UPDATE wp_posts
SET post_content = replace(post_content, ' http://www.ancien-site.com ', ' http://www.nouveau-site.com ');
Change default username Admin
Replace YourNewUsername with your new username.
The following is the quoted content:
UPDATE wp_users SET user_login = 'YourNewUsername' WHERE user_login = 'Admin';
Manually reset WordPress password
If you are the only author on your WordPress site and you have not changed the default username, you can use the following SQL query to reset your password (replace PASSWORD with the new password):
The following is the quoted content:
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, and ReplacedText is replaced with the target content:
The following is the quoted content:
UPDATE wp_posts SET `post_content`
= REPLACE (`post_content`,
'OriginalText','ReplacedText');
Change image URL
The following SQL command can help you modify the image path:
The following is the quoted content:
UPDATE wp_postsSET post_content = REPLACE (post_content, 'src="http://www.myoldurl.com',
'src=”http://www.mynewurl.com’);
source
WordPress compiled