Most of active WordPress users would know that WordPress is driven by a MySQL database. MySQL is a free relational database management system available in most web hosting services. All of the WordPress data like the posts, comments, categories, and settings are stored within the MySQL database. It is wrong to misuse your MySQL, but sometimes to use MySQL can greatly improve your efficiency, for example, for safety’s sake, you can change the default WordPress login username “admin” to whatever you want by using a SQL query, in the same way, you can collect all of the email addresses left by the commenter to achieve your email marketing purpose, and so on.

An easy way to run a SQL query is to use phpMyAdmin. phpMyAdmin is one of the most common database look-up and editing tool. Almost all the web hosting services’ control panels provide this.

How to use phpMyAdmin? First, login to phpMyAdmin panel and select your WordPress database; secondly, click on the SQL tab which will bring you to a page with a SQL query box. Once you see the SQL query box, like the screenshot shown below, you can run your SQL query there.

What you need to notice is all queries are direct SQL queries and will assume the standard table prefix ‘wp_’ for tables. So, if you are using a different table prefix, please modify queries accordingly. Of course, you had better make a copy of your WordPress database prior to executing a SQL query. It is a good practice to always backup your database before making any major changes. This ensures that even if anything were to go wrong, you would still be able to restore it. I recommend you to use WP-DB-Backup or WP-DBManager plugin to backup your database through your WordPress admin panel. If you don’t want to use plugins to backup your WordPress database, you can do these manually by logining to phpMyAdmin panel and click on Export tab at the top of the navigation as shown on the screenshot above.

1.Delete all Unused Tags

DELETE a,b,c 
FROM wp_terms AS a 
LEFT JOIN wp_term_taxonomy AS c ON a.term_id = c.term_id 
LEFT JOIN wp_term_relationships AS b ON b.term_taxonomy_id = c.term_taxonomy_id 
WHERE c.taxonomy = 'post_tag' AND c.count = 0

2.Update GUID

UPDATE wp_posts 
SET guid = REPLACE (guid, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com')

3.Update the URL in Post Content

UPDATE wp_postmeta 
SET meta_value = REPLACE(meta_value,'http://www.oldsiteurl.com','http://www.newsiteurl.com');

4.Change the Destination URL of a WordPress Site

Once you’ve moved your blog (template files, uploads & the database) from one server to another, the next thing you will then need to do is to tell WordPress your new address.

Don’t forget to change? http://www.old-site.com? to your old URL, and the? http://www.new-site.com to your new URL.
The first command to use is:

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

And then,? you will have to change the url from the table wp_postswith this snippet:

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

Finally, you’ll need to do a search through the content of your posts to be absolutely sure that your new URL link is not messing with the old URL:

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

Source

5.Delete Revisions

DELETE a,b,c FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision';

6.Update Post Meta

UPDATE wp_postmeta 
SET meta_value = REPLACE (meta_value, 'http://www.oldsiteurl.com','http://www.newsiteurl.com');

7.Change Default “Admin” Username

UPDATE wp_users 
SET user_login = 'Your New Username' 
WHERE user_login = 'Admin';

8.Reset your Password in WordPress

UPDATE wp_users 
SET user_pass = MD5( 'new_password' ) 
WHERE user_login = 'your-username';

9.Assign all Articles by Author B to Author A

UPDATE wp_posts 
SET post_author = 'new-author-id' 
WHERE post_author = 'old-author-id';

10.Delete Post Meta

DELETE FROM wp_postmeta 
WHERE meta_key = 'your-meta-key';

11.Export all Commentators Emails

SELECT DISTINCT comment_author_email 
FROM wp_comments;

12.Remove all Pingbacks

DELETE FROM wp_comments 
WHERE comment_type = 'pingback';

13.Delete all Spam Comments

DELETE FROM wp_comments 
WHERE comment_approved = 'spam';

14.Disable all Active Plugins

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

Source

15.List all Unused Tags

SELECT * 
FROM wp_postmeta pm 
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id 
WHERE wp.ID IS NULL

16.Disable Comments on Older Posts

UPDATE wp_posts 
SET comment_status = 'closed' 
WHERE post_date < '2009-01-01' AND post_status = 'publish'

17.Update Comment Author URL

UPDATE wp_comments 
SET comment_author_url = REPLACE( comment_author_url, 'http://oldsiteurl.com', 'http://newsiteurl.com' )

18.Change ‘target=”_blank”’ to ‘rel=”nofollow”’ In Post Content

UPDATE wp_posts 
SET post_content = REPLACE (post_content, 'target="_blank', 'rel="nofollow')

19.Replace Commentator Email Address

UPDATE wp_comments 
SET comment_author_email = REPLACE( comment_author_email, 'old-email@address.com', 'new-email@address.com' );

20.Delete all Comments With a Specific URL

The “%” signs means that any url containing the string within the % signs will be deleted.

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

21.Batch Deleting All Unapproved Comments

DELETE FROM wp_comments 
WHERE comment_approved = 0

22.Disabling and Enabling Trackbacks & Pingbacks

Globally enable pingbacks/trackbacks for all users:

UPDATE wp_posts 
SET ping_status = 'open';

Globally disable pingbacks/trackbacks for all users:

UPDATE wp_posts 
SET ping_status = 'closed';

23.Enable/Disable Pingbacks & Trackbacks Before a Certain Date

For this query, specify the ping_status as either open or closed. Also, specify the date by editing the ’2008-01-01′ to suit your needs.

UPDATE wp_posts 
SET ping_status = 'closed' 
WHERE post_date < '2008-01-01' AND post_status = 'publish';

24.Disabling And Enabling Comments

Globally enable comments for all users.

UPDATE wp_posts 
SET comment_status = 'open';

Globally disable comments for all users.

UPDATE wp_posts 
SET comment_status = 'closed';

Globally enable comments for registered users only.

UPDATE wp_posts 
SET comment_status = 'registered_only';

Globally enable/disable comments before a certain date.

For this query, specify the comment_status as either open, closed, or registered_only. Also, specify the date by editing the “2008-01-01” to suit your needs.

UPDATE wp_posts 
SET comment_status = 'closed' 
WHERE post_date < '2008-01-01' AND post_status = 'publish';

25.Identify and Delete Posts that are over ‘x’ Days Old

To identify any posts that are over ‘x’ amount of days run this query, remembering to replace the ‘x’ with the amount of days you are looking for:

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

To delete any posts that are over ‘x’ amount of days run this query:

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

26.Removing Unwanted Shortcodes

Replace [tweet] with your own shortcode name.

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

source

27.Change Your WP Posts Into Pages and Vice-Versa

To change posts to pages :

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

To change pages to posts:

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

Source

28.Change Author Attribution on all Posts at once

The first thing to do is getting the IDs of WordPress users. Once logged in phpmyadmin, insert the following SQL command:

SELECT ID, display_name FROM wp_users;

Right now, phpmyadmin displayed a list of WordPress users associated with their IDs. Let’s say that NEW_AUTHOR_ID is the ID of the “new” author, and OLD_AUTHOR_ID is the old author ID.

UPDATE wp_posts 
SET post_author=NEW_AUTHOR_ID 
WHERE post_author=OLD_AUTHOR_ID;

Source

29.Search and Replace Post Content

To search and replace within the post content area of WordPress use the following code. Replace ‘OriginalText‘ with the original text you would like to change and replace ReplacedText with your new text.

UPDATE wp_posts
SET `post_content`= REPLACE (`post_content`,'OriginalText','ReplacedText');

Source

30.Changing the URL of Images

To change the paths of your images,? use this SQL command below:

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

Source

31.Delete all Trash Comments from the Database

Use the following SQL command, you can delete all? the trash comments form the WordPress database forever.

Delete from wp_comments 
Where comment_approved=’trash’;

Source

32.Delete all Pending Posts Within a Range of Date

This is most useful to websites that allow user generated content (like directories, classifieds websites etc.)

DELETE FROM wp_posts 
WHERE post_date < ’2011-11-06 19:18:00′ 
AND post_status = ‘pending’;

Source

33.Add a Custom Field to all Articles and Pages

The following snippet will add a custom field to both posts and pages within your WP database. You have to replace the UniversalCutomField? to whatever name you like and then change MyValue to the value of your choice.

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

Add a custom field to posts only:

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

Add a custom field to pages only:

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

Source

There are some of other relevant posts here:

13 Useful WordPress SQL Queries You Wish You Knew Earlier

WordPress : 10+ life saving SQL queries

20 Helpful WordPress SQL Queries Snippets

WordPress Discussion Management: Enable or Disable Comments and Pingbacks via SQL

Related Posts

• How To Change The Prefix Of Your WordPress Database (0)