20 Practical WordPress SQL queries

Intermediary transaction http://www.aliyun.com/zixun/aggregation/6858.html ">seo diagnose Taobao guest cloud host technology Hall

WordPress stores all of its pieces of information (including articles, pages, comments, blog links, plug-in settings, and so on) in the MySQL database. Although WordPress users can control the above information fragment through the website backstage edit. To a certain extent.

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). With MySQL you can quickly accomplish these tasks and save you more time.

Here are 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 adds 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.

the following references:


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 ');

If you only need to add a custom field to the article, you can use this code:

the following references:


INSERT into Wp_postmeta (post_id, Meta_key, Meta_value) SELECT IDs as post_id, ' Universalcustomfield ' as Meta_key ' my Value 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:

the following references:


INSERT into Wp_postmeta (post_id, Meta_key, Meta_value) SELECT IDs as post_id, ' Universalcustomfield ' as Meta_key ' my Value 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.

the following references:


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.

the following references:


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;

Bulk Delete spam comments

Execute the following SQL command:

the following references:


DELETE from wp_comments WHERE wp_comments.comment_approved = ' spam ';

Bulk Delete all unaudited comments

This SQL query deletes all unaudited comments on your site without affecting the reviewed comments.

the following references:


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

the following references:


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 for all users:

the following references:


UPDATE wp_posts SET ping_status = ' open ';

Disable pingbacks/trackbacks for all users:

the following references:


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

the following references:


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.

the following references:


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)

the following references:


SELECT * from ' wp_posts ' WHERE ' post_type ' = ' post ' and DATEDIFF (now (), ' post_date ') > X

Delete all articles before "X" Days:

the following references:


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:

the following references:


UPDATE wp_post SET post_content = replace (post_content, ' [tweet] ');

Convert an article to a page

Still, just run a SQL query through phpMyAdmin:

the following references:


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

To convert a page to an article:

the following references:


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:

the following references:


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.

the following references:


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.

the following references:


DELETE from wp_posts WHERE post_type = "revision";

Deactivate/activate all WordPress plugins

After activating a plugin to find that you can't login to wordpress admin panel, try the following query command, it will immediately disable all plug-ins, so that you can log in again.

the following references:


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://www.new-site.com for a new URL address.

First of all:

the following references:


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


UPDATE wp_posts SET guid = replace (GUID, ' http://www.old-site.com ', ' http://www.new-site.com ');

Finally, search the content of the article to make sure that the new URL link is not confused with the original link:

the following references:


UPDATE wp_posts


SET post_content = replace (post_content, ' http://www.ancien-site.com ', ' http://www.nouveau-site.com ');

Change Default User name Admin

Replace the Yournewusername with the new username.

The following are the contents of the reference:


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

the following references:


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:

the following references:


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:

the following references:


UPDATE wp_posts SET post_content = REPLACE (post_content, ' src= ' http://www.myoldurl.com ', ' src= ' http://www.mynewurl.com ');

Source

WordPress, compile