14 Handy WordPress MySQL/MariaDB Query Snippets and Hacks

In today’s post, we will be going over a few MySQL queries that allow you to make changes to your database. We recommend that before making any changes to your WordPress database, you perform a full backup and do not run these queries on a production website.

Additionally, these should only be run by developers or engineers who are already familiar with the WordPress database and framework. If you need help, feel free to contact our premium support for 24/7 WordPress support and development.

Let’s proceed to see the useful WordPress database snippets below:

When you need to change the site URL as well as the home URL of your website, you can use the snippet below:

UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com') WHERE option_name = 'home' OR option_name = 'siteurl';

When you need to change the GUID of your WordPress website

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

You can use the snippet below when you need to change the URLs in your post content

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

The snippet below helps you change the Image Path in your posts content

UPDATE wp_posts SET post_content = REPLACE (post_content, 'src="http://www.oldsiteurl.com', 'src="http://yourcdn.newsiteurl.com');
UPDATE wp_posts SET  guid = REPLACE (guid, 'http://www.oldsiteurl.com', 'http://yourcdn.newsiteurl.com') WHERE post_type = 'attachment';

When you need to update all your post meta

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

You can use this snippet to change the username of the admin user to something else

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

This is particularly useful when you need to reset your password in the database. This is particularly useful when you get logout of the website

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

When you need to reassign articles between authors. The example below is assign all articles by Author B to Author A

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

This query becomes useful when you want to delete all post revisions. Post revisions tend to grow and increase the size of the database, this snippet usually comes in handy.

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'

When you want to delete all meta_keys for a post. Usually, plugins and function tend to use this to store their configuration, once the plugin is uninstalled it can be helpful to remove all the metadata they stored with it.

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

To view all comment emails with no duplicates, use the snippet below:

SELECT DISTINCT comment_author_email FROM wp_comments;

To delete all pingbacks, use the snippet below:

DELETE FROM wp_comments WHERE comment_type = 'pingback';

This snippet below allows you to delete all spam comments

DELETE FROM wp_comments WHERE comment_approved = 'spam';
#     * 0 = Comment Awaiting Moderation
#     * 1 = Approved Comment
#     * spam = Comment marked as Spam

To view all tags that are not used, you can use the following MySQL query. You can view and then proceed to delete all tags that are not used.

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;