20+ Useful SQL Queries for WordPress

The WordPress admin panel, like any other admin panel, is essentially a database management dashboard for the engine. Almost all site information is stored in the database, and sometimes it can be quite a hassle to make changes to certain information. For example, to close comments on all posts, it would take a considerable amount of time. If there are around 5000 such posts, doing it through the admin panel is hardly realistic. Only those who are unaware that such things can be done using SQL queries would dare to attempt it. Below are examples of such queries.

Contents:

In this article, I have compiled the best from similar articles and supplemented it with my own examples, making some adjustments.

SQL queries can be executed using a small and completely harmless plugin: SQL Executioner: https://wordpress.org/plugins/sql-executioner/.

Or using the construct:

global $wpdb;
$wpdb->query("here_query_code");

You can use this construction in theme files, for example in functions.php or in some template of a static page, anywhere, the main thing is to implement it.

Before executing queries, don't be lazy to make database dumps (backups), otherwise you may regret it very much...

wp_ in the examples should be changed if the table prefix in your database is different.

Deleting Unused Taxonomy Items from the Database with One SQL Query

A few days ago, I deleted unnecessary records from the client's site. After that, I discovered that there were a lot of unnecessary tags on the site (entries with no posts). They clutter the database unnecessarily, and it is advisable to delete such empty tags. To avoid doing this manually, I wrote this query:

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE `count` = 0 );
DELETE FROM wp_term_taxonomy WHERE term_id NOT IN (SELECT term_id FROM wp_terms);
DELETE FROM wp_term_relationships WHERE term_taxonomy_id NOT IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

Here, 3 queries delete rows from the tables: wp_terms, wp_term_taxonomy, wp_term_relationships. The first query finds all terms with a count value of 0 and deletes them. The second and third queries delete rows from the tables wp_term_taxonomy and wp_term_relationships to match wp_terms.

This query deletes all empty terms from all existing taxonomies (category, post_tag). If you need to limit such deletion, for example, only with tags (post_tag), then the first line should look like this:

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE taxonomy='post_tag' AND count=0 );

After deleting data from the database, it is advisable to optimize the tables to clean up unused indexes and defragment data files. It is useful to do this when large volumes of information have been deleted from the table. Tables are optimized with this query:

OPTIMIZE TABLE `wp_terms` , `wp_term_taxonomy` , `wp_term_relationships`;

How to Reset a User's Password

Sometimes it is vital to reset a password or simply change it, but there is no access to the site's admin panel. To change the password, you can use this SQL query:

UPDATE wp_users SET user_pass = MD5('newpass') WHERE user_login = 'admin'

Here, the password is 'newpass', and the user's login whose password will be changed is 'admin'.

If you happen to have forgotten the login, but you definitely remember that you were the first user on the blog, and hence your ID is 1, you can identify the user for password change by ID (WHERE ID=1):

UPDATE wp_users SET user_pass = MD5('newpass') WHERE ID=1;

MD5('newpass') = e6053eb8d35e02ae40beeeacef203c1a

How to Change a User's Login

By default, in WordPress, a login is created once and cannot be changed thereafter. However, this is not entirely true. You can change the login using this SQL query:

UPDATE wp_users SET user_login = 'shef' WHERE user_login = 'admin'

Here we are changing the login to shef for the user whose login was admin.

Comments

It is often necessary to mass close or open comments or do something else with comments. For such manipulations, you can use the following SQL queries:

Close comments for all posts
UPDATE wp_posts SET comment_status = 'closed'
Open comments for all posts
UPDATE wp_posts SET comment_status = 'open'
Commenting only for registered users
UPDATE wp_posts SET comment_status = 'registered_only'
Deleting spam comments

Some anti-spam plugins let all comments through but mark suspicious ones as spam, so that if they are not, they can be approved later. For example, the acclaimed Akismet does this (I've never used it, is it really that good?). If a large number of spam comments have accumulated, cleaning up all this junk manually is not comparable to using such a simple SQL query:

DELETE FROM wp_comments WHERE comment_approved = 'spam'

If you need to delete all unapproved comments, use:

DELETE FROM wp_comments WHERE comment_approved = 0
Remove URL from all comments

To clear the URL field values for all comments, you can use this query:

UPDATE wp_comments SET comment_author_url=''
Close comments in a category

If you need to close comments only in a specific category, you can use this SQL query, which will close the ability to comment for all posts in the category, for example, stat (alternative category name, slug):

UPDATE wp_posts p
	LEFT JOIN wp_term_relationships rel ON ( p.ID = rel.object_id )
	LEFT JOIN wp_term_taxonomy tax ON ( tax.term_taxonomy_id = rel.term_taxonomy_id  )
	LEFT JOIN wp_terms tm ON ( tm.term_id = tax.term_id )
SET p.comment_status = 'closed'
WHERE tm.slug = 'stat'

stat - the category in which we are closing comments.

Close commenting on old posts

To disable the ability to leave comments for old posts, for example, published before January 1, 2010, you can use this SQL query:

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

where, as you guessed, 2010-01-01 is the date after which comments will be closed.

Deleting comments with bad URLs

You can delete comments based on a pattern, for example, if the commentator's URL contains the letters poker:

DELETE a,b
FROM wp_comments a
	LEFT JOIN wp_commentmeta b ON ( a.comment_ID = b.comment_id )
WHERE
	a.comment_author_url LIKE '%poker%'
Changing the commentator's site

Theoretically, the URL that the commentator specifies in the "site" field can be changed when editing a comment. However, if there are many comments from this commentator, changing the URL everywhere is a job for monkeys. It's easier to do it with a query:

UPDATE wp_comments SET comment_author_url = 'http://example.com'
WHERE comment_author = 'Kama'
AND comment_author_email = '[email protected]'

Where example.com is the URL we want to set for the commentator, Kama is the commentator's nickname, and [email protected] is their email. I specified the nickname and email to accurately identify the commentator.

Another option similar to the previous example. It will replace all occurrences of old-example.com with new-example.com in the URLs of all commentators:

UPDATE wp_comments
SET comment_author_url = REPLACE( comment_author_url, 'old-example.com', 'new-example.com' )
Changing the ID of the parent comment

Sometimes users incorrectly post threaded comments—when replying to a comment, they do not use the "reply" button, resulting in the reply being lost. To correct this situation, you can change the ID of the parent comment to correctly position the new comment in the thread:

UPDATE wp_comments SET comment_parent=21 WHERE comment_ID=97

21 - the parent comment, 97 - the comment that needs to be corrected.

Commenter emails

To collect all commenter emails, for example, to do some kind of mailing, you can use this SQL query:

SELECT DISTINCT comment_author_email FROM wp_comments

As a result, you will get a list of unique emails.

Pings and Notifications

Just like comments, you can manage pings. Here are a few examples of such management:

Close the ability to receive pings

You were running a blog, and suddenly there was a server load through pings, which in general, serve no practical purpose and can be completely closed and forgotten. To close all pings, use this SQL query.

UPDATE wp_posts p SET p.ping_status = 'closed'
Open the ability to receive pings
UPDATE wp_posts p SET p.ping_status = 'open'
Delete all notifications

Notifications are the same as pings—they are "comments" that notify you that Vasya Pupkin referenced you. If you decide that these unfortunate comments are not needed, you can delete them in bulk using this SQL query:

DELETE FROM wp_comments WHERE comment_type = 'pingback';

After deleting pings, it's logical to close the ability to receive pings (see the previous example), and also pings need to be closed in the settings.

Tags

Despite WordPress providing a fairly flexible interface for managing tags, you may find the following SQL queries useful.

Get empty tags

After several years of blogging, empty tags may appear—tags with no posts. In later versions of WP, such tags can be deleted on the tags settings page by sorting them by the number of posts. However, in earlier versions, it is not possible to sort them this way, and for such an operation, you can use this SQL query, which will get all tags with no posts:

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

Instead of post_tag, you can write any other taxonomy, for example, categories.

Delete empty tags

In the previous example, we obtained empty tags, and now we will simply delete them:

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

Well, you understand, if you change c.count = 0 to c.count < 2, all tags with 0 and 1 posts in them will be deleted.

Domain Change

Fixing the domain in options

When moving to another domain, in addition to replacing the domain wherever it appears in the template, it is also necessary to change the records in the Database in two places, in the options table (wp_options):

UPDATE wp_options SET option_value = 'http://example.com/'
WHERE option_name = 'home' OR option_name = 'siteurl'

This query changes the values of the fields siteurl and home. Don't forget to change example.com to your new domain!

Fixing the domain in posts

When changing the domain, it is necessary to ensure that there are correct internal links in the posts, i.e. links from articles to other blog articles, after changing the domain will become broken. Usually, redirection from the old domain to the new one is set up in .htaccess or PHP with a 301 redirect, but in addition to this, it is aesthetically correct if there are no links to the old domain in the articles. With this query, we will replace all types of links, including links to images:

UPDATE wp_posts
SET post_content = REPLACE (post_content, 'http://old-example.com', 'http://new-example.com')

old-example.com and new-example.com are the old and new domains, respectively. Don't forget to change them.

Similarly, you can change any string, for example, the word "Вордпресс" to "WordPress". See the example below.

Changing the domain in custom fields

There may also be records in custom fields storing any URLs on the old domain, so when changing the domain, it may be necessary to replace the domain in custom fields as well:

UPDATE wp_postmeta
SET meta_value = REPLACE (meta_value, 'http://old-example.com','http://new-example.com')
Editing GUID

When changing the domain of the site, it is advisable to ensure that all records have the correct value in the guid field in the wp_posts table.

This field is used as a unique ID for identifying the record in the RSS feed. Also, it is said that it is needed for correct redirection from incorrect URLs, but this is not true - I checked secret

UPDATE wp_posts
SET guid = REPLACE (guid, 'http://www.oldblog.ru', 'http://www.newblog.ru')

For some projects, I use the guid field for other purposes, and for updating this field, I wrote a mini-plugin.

Replacing text in posts

You can replace text in posts and do it directly in the Database. For example, you can add the attribute target="blank" to all links with the rel="nofollow" attribute:

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

You can also add internal links with a specific anchor, for example, from the word "WordPress" to create a link to a relevant page to increase its significance. However, there are special plugins for this purpose that do not modify the text in the database but create links on the fly:

UPDATE wp_posts SET post_content = REPLACE (post_content, ' WordPress ', ' <a href="http://example.com/article-about-wordpress">WordPress</a> ')

Custom fields (postmeta)

Deleting unnecessary custom fields

If a plugin created custom fields and then became unnecessary and you deleted it, but orphaned, unnecessary custom fields remained in the Database. In such a situation, all custom fields with the name, for example, "meta_name" can be deleted with a simple SQL query:

DELETE pm FROM wp_postmeta pm WHERE pm.meta_key = 'meta_name'

If the name of the custom field (meta_name) is in Cyrillic, make sure that the file encoding from which the SQL query will be executed matches the blog's encoding (usually UTF-8 without BOM).

Get all custom fields with empty value

Despite the fact that in the admin panel, creating a custom field without assigning a value is not possible, at least by standard means, such "empty" custom fields can still be in your Database. For example, they can be left by various plugins or clumsy hands. To check if there are such fields and then decide their fate, use this SQL query:

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

Changing authors

In general, attributing your name to someone else's article is not good, so in this example, it is assumed that you changed the username or bought all the content with copyright, and you must change the authorship of the articles at all costs. To change the author of an article from one user to another (both users must be registered, of course), you can use this SQL query:

UPDATE wp_posts SET post_author=1 WHERE post_author=2

where 1 is the new user, and 2 is the old user. Find the user IDs in the admin panel.

Deleting post revisions

By default, WordPress includes post revisions. They clutter the database and are rarely truly necessary. If revisions are enabled, I recommend clearing them at least once a year. You can do this with the following query in phpMyAdmin:

-- Dependencies with taxonomies
DELETE FROM wp_term_relationships WHERE object_id IN (SELECT ID FROM wp_posts WHERE post_type = 'revision');

-- Meta fields
DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type = 'revision');

-- Revisions themselves
DELETE FROM wp_posts WHERE post_type = 'revision';

This query will delete post revisions and autosaves. It will also delete associated meta fields (if any) and the revision's association with taxonomy (if any).

Option 2

As an alternative query, you can use this example for deletion with JOIN:

DELETE a,b,c,d
	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)
		LEFT JOIN wp_comments d ON (a.ID = d.comment_post_ID)
	WHERE a.post_type = 'revision'

Here, I also added the deletion of rows from the wp_comments table, just in case there are comments related to revisions. There shouldn't be any.

Deactivating all plugins

There are situations when it is impossible to access the plugins page due to one plugin not working correctly. You can delete such a plugin via FTP, or you can simply deactivate all plugins with a SQL query, which will allow you to access the plugin settings page:

UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins'

Clearing the feed cache

WordPress saves the feed in the options table and updates it when a new post is published or after a certain period of time. If you need to clear the feed cache, you can use this query:

DELETE FROM `wp_options` WHERE `option_name` LIKE ('_transient%_feed_%')

Getting the names of all columns (fields) in a table

SHOW COLUMNS FROM my_posts;

Or

global $wpdb;

$data = $wpdb->get_results("SHOW COLUMNS FROM $wpdb->posts");

print_r( $data );

/*
Array (
	[0] => stdClass Object
		(
			[Field] => ID
			[Type] => bigint(20) unsigned
			[Null] => NO
			[Key] => PRI
			[Default] =>
			[Extra] => auto_increment
		)

	[1] => stdClass Object
		(
			[Field] => post_author
			[Type] => bigint(20) unsigned
			[Null] => NO
			[Key] => MUL
			[Default] => 0
			[Extra] =>
		)

	[2] => stdClass Object
		(
			[Field] => post_date
			[Type] => datetime
			[Null] => NO
			[Key] =>
			[Default] => 0000-00-00 00:00:00
			[Extra] =>
		)

	...
)
*/