Some practical WordPress MySQL operation commands

However, if you have hundreds of articles on your WordPress website, and you need to make full-site changes, it will be time-consuming and laborious to edit one by one from the background, and the chance of making mistakes will also increase. The best way is to enter the MySQL database of WordPress to execute necessary queries (changes ). With MySQL, you can quickly complete the preceding tasks, saving you more time.

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

Backup beforehand
The WordPress database stores every article you carefully published, all comments from your readers, and all your personalized settings for your website. Therefore, no matter how confident you are, remember to back up the WordPress database in advance. You can back up data through the backup plug-in.

Add custom fields for all articles and pages
This Code adds a custom field to all articles and pages in the WordPress database. What you need to do is to replace 'universalcutomfield' in the Code with the text you need, and then change 'myvalue' to the desired value.
Copy codeThe 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 for the article, you can use the following code:
Copy codeThe 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 codeThe 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 article meta data
When you install or delete a plug-in, the system uses the meta tag to store data. After the plug-in is deleted, the data will 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 desired value before running the query.
Copy codeThe Code is as follows:
Delete from wp_postmeta WHERE meta_key = 'yourmetakey ';

Search for useless tags
If you perform a query in the WordPress database to delete the old article, the tag of the article will remain in the database and appear in the tag list/Tag Cloud as before when you delete the plug-in. The following query can help you find useless tags.
Copy codeThe 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;

Batch delete spam comments
Run the following SQL command:
Copy codeThe Code is as follows:
Delete from wp_comments WHERE wp_comments.comment_approved = 'spam ';

Batch Delete All unreviewed comments
This SQL query will delete all unreviewed comments on your website and will not affect the reviewed comments.
Copy codeThe Code is as follows:
Delete from wp_comments WHERE comment_approved = 0

Forbid comment on earlier articles
The value of comment_status is open, closed, or registered_only.
You also need to set the date (in the Code ):
Copy codeThe Code is as follows:
UPDATE wp_posts SET comment_status = 'closed 'WHERE post_date <'2017-01-01' AND post_status = 'Publish ';

Disable/activate trackback and pingback
The value of comment_status is open, closed, or registered_only.
Activate pingbacks/trackbacks to all users:
Copy codeThe Code is as follows:
UPDATE wp_posts SET ping_status = 'open ';

Disable pingbacks/trackbacks for all users:
Copy codeThe Code is as follows:
UPDATE wp_posts SET ping_status = 'closed ';

Activate/disable Pingbacks & Trackbacks before a certain date
The value of ping_status is open, closed, or registered_only.
You also need to set the date (in the Code ):
Copy codeThe Code is as follows:
UPDATE wp_posts SET ping_status = 'closed 'WHERE post_date <'2017-01-01' AND post_status = 'Publish ';

Delete a comment from a specific URL
When you find that many spam comments have the same URL link, you can use the following query to delete these comments at one time. % Indicates that all URLs containing strings in the "%" symbol will be deleted.
Copy codeThe Code is as follows:
DELETE from wp_comments WHERE comment_author_url LIKE "% nastyspamurl % ";

Identify and delete the article "X" days ago
Search for all the articles "X" Days Ago (replace X with the corresponding value ):
Copy codeThe Code is as follows:
SELECT * FROM 'wp _ posts' WHERE 'Post _ type' = 'post' and datediff (NOW (), 'Post _ date')> X

Delete all articles from "X" days ago:
Copy codeThe Code is as follows:
Delete from 'wp _ posts' WHERE 'Post _ type' = 'post' and datediff (NOW (), 'Post _ date')> X

Delete unnecessary short code
When you decide not to use short code, they will not disappear automatically. You can use a simple SQL query command to delete all unneeded short code. Replace "tweet" with the corresponding short code name:
Copy codeThe Code is as follows:
UPDATE wp_post SET post_content = replace (post_content, '[tweet]', '');

Convert an article to a page
You can still run an SQL query using PHPMyAdmin:
Copy codeThe Code is as follows:
UPDATE wp_posts SET post_type = 'page' WHERE post_type = 'post'

Convert a page to an article:
Copy codeThe Code is as follows:
UPDATE wp_posts SET post_type = 'post' WHERE post_type = 'page'

Change author attributes of all articles
First, use the following SQL command to retrieve the author's ID:
Copy codeThe Code is as follows:
Select id, display_name FROM wp_users;

After obtaining the new and old IDs of the author, insert the following command, remember to replace NEW_AUTHOR_ID with the new Author ID, and replace OLD_AUTHOR_ID with the old Author ID.
Copy codeThe Code is as follows:
UPDATE wp_posts SET post_author = NEW_AUTHOR_ID WHERE post_author = OLD_AUTHOR_ID;

Batch Delete document revision history
Saving revision history can be very practical and annoying. You can manually delete the revision history or use SQL queries to save time.
Copy codeThe Code is as follows:
Delete from wp_posts WHERE post_type = "revision ";

Disable/activate all WordPress plug-ins
After activating a plug-in, you cannot log on to the WordPress Management Panel. Try the following query command. It immediately disables all plug-ins and allows you to log on again.
Copy codeThe Code is as follows:
UPDATE wp_options SET option_value = 'a: 0 :{} 'WHERE option_name = 'Active _ ins ';

Change the target URL of a WordPress website
After you move a WordPress blog (template file, uploaded content & database) from one server to another, you need to tell WordPress your new blog address.
When using the following command, note that you replace the http://www.old-site.com with your original URL, and the http://blog.doucube.com with a new URL address.
First:
Copy codeThe 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 codeThe Code is as follows:
UPDATE wp_posts SET guid = replace (guid, 'HTTP: // www.old-site.com ', 'HTTP: // blog.doucube.com );

Finally, search for the article content to ensure that the new URL link is not mixed with the original link:
Copy codeThe 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 user name Admin
Replace YourNewUsername with the new user name.
Copy codeThe Code is as follows:
UPDATE wp_users SET user_login = 'yourusername' WHERE user_login = 'admin ';

Manually reset the WordPress Password
If you are the only author on your WordPress website and you have not modified the default username, You can reset the PASSWORD using the following SQL query (replace the PASSWORD with the new PASSWORD ):
Copy codeThe Code is as follows:
UPDATE 'wordpress '. 'wp _ users' SET 'user _ pass' = MD5 ('Password ')
WHERE 'wp _ users'. 'user _ login' = 'admin' LIMIT 1;

Search and replace the document content
Replace OriginalText with replaced content, and ReplacedText with the target content:
Copy codeThe Code is as follows:
UPDATE wp_posts SET 'Post _ content' = REPLACE ('Post _ content', 'ininaltext', 'replace text ');

Change image URL
The following SQL command can help you modify the image path:
Copy codeThe Code is as follows:
UPDATE wp_postsSET post_content = REPLACE (post_content, 'src = "http://www.myoldurl.com ', 'src =" http://blog.doucube.com ');