Practical WordPress background MySQL operation command _ MySQL

Practical WordPress background MySQL operation command WordPress

BitsCN.com

Keywords:WordPress MySQL Background Database
Address: http://www.cnblogs.com/txw1958/archive/2013/01/06/wordpress-sql.html

WordPress stores all its information fragments (including articles, pages, comments, blog links, and plug-in Settings) in the MySQL database. Although WordPress users can control the above information fragments through the website background editing. 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.

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:

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:

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.

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.

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:

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.

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 ):

UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2010-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:

UPDATE wp_posts SET ping_status = 'open';

Disable pingbacks/trackbacks for all users:

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 ):

UPDATE wp_posts SET ping_status = 'closed' WHERE post_date < '2010-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.

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

Recognize and delete articles from "X" days ago
Search for all the articles from "X" days ago (replace X with the corresponding value ):

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

Delete all articles from "X" days ago:

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:

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:

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

Convert a page to an article:

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:

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.

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.

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.

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

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:

UPDATE wp_optionsSET 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:

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:

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.

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 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 ):

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:

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:

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

BitsCN.com