However, if you have hundreds of articles on your WordPress site, and you need to make a whole-site change, then editing from the background is a bit time-consuming and laborious, and the chances of making mistakes are increased. The best way is to enter the WordPress MySQL database to perform the necessary queries (changes). The above tasks can be accomplished quickly through MySQL, saving you more time.
the following is to introduce some time-saving and labor-saving WordPress SQL Query method.
Backup in advance
The WordPress database stores every article you publish carefully, all the comments from your readers, and all the personalization settings you have for your site. So no matter how confident you are with yourself, keep in mind that you must back up the WordPress database beforehand. You can back it up with a backup plugin.
Add a custom field to all articles and pages
This code can add a custom field to all articles and pages in the WordPress database. All you have to do is replace the ' Universalcutomfield ' in the code with the text you need, and then change ' myvalue ' to the desired value.
Copy Code code 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 = ' Univers Alcustomfield ');
If you only need to add a custom field to the article, you can use this code:
Copy Code code 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 isn't 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 Code code 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 isn't in
(SELECT post_id from Wp_postmeta WHERE meta_key = ' Universalcustomfield ') and ' post_type ' = ' page ';
Delete Article Meta data
When you install or remove Plug-ins, the system stores the data through the article Meta tag. After the plugin is deleted, the data will still remain in the Post_meta table, and of course you no longer need the data, you can completely delete it. Remember to replace the ' Yourmetakey ' in the code with the corresponding value you need before running the query.
Copy Code code as follows:
DELETE from Wp_postmeta WHERE meta_key = ' Yourmetakey ';
Find unwanted tags
If you execute a query in the WordPress database to delete the old article, as in the case of deleting the plugin, the article will remain in the database and will appear in the tag list/tag cloud. The following query will help you find the useless tags.
Copy Code code 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.c ount=0;
Bulk Delete spam comments
Execute the following SQL command:
Copy Code code as follows:
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 reviewed comments.
Copy Code code as follows:
DELETE from wp_comments WHERE comment_approved = 0
Prohibit comments on earlier articles
Specifies that the value of Comment_status is open, closed, or registered_only.
In addition, you need to set the date (2010-01-01 in the revision code):
Copy Code code as follows:
UPDATE wp_posts SET comment_status = ' closed ' WHERE post_date < ' 2010-01-01 ' and post_status = ' publish ';
Deactivate/activate Trackback and Pingback
Specifies that the value of Comment_status is open, closed, or registered_only.
Activate Pingbacks/trackbacks to all users:
Copy Code code as follows:
UPDATE wp_posts SET ping_status = ' open ';
To disable pingbacks/trackbacks for all users:
Copy Code code as follows:
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.
In addition, you need to set the date (2010-01-01 in the revision code):
Copy Code code as follows:
UPDATE wp_posts SET ping_status = ' closed ' WHERE post_date < ' 2010-01-01 ' and post_status = ' publish ';
Delete a comment for a specific URL
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. % indicates that all URLs containing a string within the "%" symbol will be deleted.
Copy Code code as follows:
DELETE from wp_comments WHERE comment_author_url like "%nastyspamurl%";
Identify and delete articles before "X" days
Find all articles before the "X" Day (note: replace x with the corresponding value)
Copy Code code as follows:
SELECT * from ' wp_posts ' WHERE ' post_type ' = ' post ' and DATEDIFF (now (), ' post_date ') > X
Delete all articles before "X" Days:
Copy Code code as follows:
DELETE from ' wp_posts ' WHERE ' post_type ' = ' post ' and DATEDIFF (now (), ' post_date ') > X
Delete unwanted Short Code
When you decide not to use the short code, they don't automatically disappear. You can use a simple SQL query command to remove any unwanted short code. Replace "tweet" with the corresponding short code name:
Copy Code code as follows:
UPDATE wp_post SET post_content = replace (post_content, ' [tweet] ', ');
Convert an article to a page
Still, just run a SQL query through phpMyAdmin:
Copy Code code as follows:
UPDATE wp_posts SET post_type = ' page ' WHERE post_type = ' post '
To convert a page to an article:
Copy Code code as follows:
UPDATE wp_posts SET post_type = ' Post ' WHERE post_type = ' page '
Change author properties on all articles
First, retrieve the author's ID by using the following SQL command:
Copy Code code as follows:
SELECT ID, display_name from Wp_users;
After you have successfully obtained the new and old ID for the author, insert the following command, remembering to replace new_author_id with the new author ID, and replace old_author_id with the old author ID.
Copy Code code as follows:
UPDATE wp_posts SET post_author=new_author_id WHERE post_author=old_author_id;
Bulk Delete Article revision history
The preservation of the history of the article can be very practical and annoying. You can manually delete revision history, or you can save yourself time by using SQL queries.
Copy Code code as follows:
DELETE from wp_posts WHERE post_type = "revision";
Deactivate/activate all WordPress plugins
After activating a plugin to find that you cannot login to the WordPress admin panel, try the following query command, it will immediately disable all plug-ins, so that you can log in again.
Copy Code code as follows:
UPDATE wp_options SET option_value = ' a:0:{} ' WHERE option_name = ' active_plugins ';
Change the target URL for the WordPress site
After you move the WordPress blog (template file, upload content & database) from one server to another, you need to tell WordPress your new blog address.
When using the following command, be careful to replace http://www.old-site.com with your original url,http://blog.doucube.com and replace it with a new URL address.
First of all:
Copy Code code 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 Code code as follows:
UPDATE wp_posts SET GUID = replace (GUID, ' http://www.old-site.com ', ' http://blog.doucube.com ');
Finally, search the content of the article to make sure that the new URL link is not confused with the original link:
Copy Code code as follows:
UPDATE wp_posts SET post_content = replace (post_content, ' http://www.ancien-site.com ', ' http://blog.doucube.com ');
Change Default User name Admin
Replace the Yournewusername with the new username.
Copy Code code as follows:
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 do not modify the default username, you can use the following SQL query to reset the password (replace the password with the new password):
Copy Code code as follows:
UPDATE ' WordPress '. ' Wp_users ' SET ' user_pass ' = MD5 (' PASSWORD ')
WHERE ' wp_users '. ' User_login ' = ' admin ' LIMIT 1;
Search for and replace article content
Originaltext Replace with replaced content, Replacedtext replaced with target content:
Copy Code code as follows:
UPDATE wp_posts SET ' post_content ' = REPLACE (' post_content ', ' originaltext ', ' replacedtext ');
Change the picture URL
The following SQL command can help you modify the image path:
Copy Code code as follows:
UPDATE Wp_postsset post_content = REPLACE (post_content, ' src= ' http:// Www.myoldurl.com ', ' src= ' http://blog.doucube.com ');
