wp db query

Overview

Run any SQL statement directly against the WordPress database without opening a MySQL shell. The power tool for bulk data operations, auditing, custom queries, and DB-level fixes.

What It Does

wp db query opens a MySQL connection using wp-config.php credentials and executes the SQL you provide — either as a quoted string or via stdin. It returns results in the MySQL client's default format.

Syntax

wp db query [<sql>] [--dbuser=<value>] [--dbpass=<value>]

Arguments & Options

Argument / Flag	Description
SQL	SQL statement to execute (omit to read from stdin)
--dbuser=VALUE	Override DB user
--dbpass=VALUE	Override DB password

Basic Usage

Run a SELECT query

wp db query "SELECT ID, post_title, post_status FROM wp_posts LIMIT 10;"

Output:

+----+---------------------+-------------+
| ID | post_title          | post_status |
+----+---------------------+-------------+
|  1 | Hello World         | publish     |
|  2 | Sample Page         | publish     |
+----+---------------------+-------------+

Count rows in a table

wp db query "SELECT COUNT(*) FROM wp_options WHERE autoload='yes';"

Run a multi-statement SQL file

wp db query < custom_migration.sql

Read from stdin

echo "SELECT option_name, option_value FROM wp_options WHERE option_name='siteurl';" | wp db query

Real-World Scenarios

Scenario 1: Bulk update post status

# Publish all posts that are stuck in 'pending'
wp db query "UPDATE wp_posts SET post_status='publish' WHERE post_status='pending' AND post_type='post';"

Scenario 2: Find users by role

wp db query "
  SELECT u.ID, u.user_login, u.user_email
  FROM wp_users u
  JOIN wp_usermeta m ON u.ID = m.user_id
  WHERE m.meta_key = 'wp_capabilities'
  AND m.meta_value LIKE '%administrator%';
"

Scenario 3: Audit oversized autoloaded options

wp db query "
  SELECT option_name, LENGTH(option_value) AS size_bytes
  FROM wp_options
  WHERE autoload='yes'
  ORDER BY size_bytes DESC
  LIMIT 20;
"

Scenario 4: Clean up spam comments

# Check count first
wp db query "SELECT COUNT(*) FROM wp_comments WHERE comment_approved='spam';"

# Then delete
wp db query "DELETE FROM wp_comments WHERE comment_approved='spam';"

Scenario 5: Fix broken serialized meta (raw SQL update)

# After a failed search-replace that corrupted serialized data
# This is a last resort — use wp search-replace instead
wp db query "UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, 'http://old.com', 'https://new.com') WHERE meta_value LIKE '%http://old.com%';"

Scenario 6: WooCommerce — find orders over a threshold

wp db query "
  SELECT post_id, meta_value AS order_total
  FROM wp_postmeta
  WHERE meta_key='_order_total'
  AND CAST(meta_value AS DECIMAL(10,2)) > 500.00
  ORDER BY CAST(meta_value AS DECIMAL(10,2)) DESC
  LIMIT 20;
"

Best Practices

1. Use wp search-replace for URL/string changes — it handles serialized data safely. Use wp db query for more complex or custom SQL only.
2. Always SELECT before UPDATE/DELETE — verify the affected rows before making changes.
3. Use transactions in complex operations: BEGIN; ... COMMIT; or ROLLBACK; if needed.
4. Back up with wp db export before running any destructive query.
5. Use table prefix variables — WordPress uses wp_ by default but this may differ. Always check with wp db tables.

Troubleshooting

Problem	Cause	Fix
ERROR 1064 (42000): Syntax error	SQL syntax mistake	Double-check quotes; use " for outer, ' for SQL strings
Zero rows affected for UPDATE	WHERE clause too restrictive	Add a SELECT first to verify the WHERE matches
Access denied	User lacks sufficient MySQL privileges	Connect with a more privileged user via --dbuser
Wrong table prefix	Site uses custom prefix	Check with wp config get table_prefix --type=variable

Quick Reference

wp db query "SELECT * FROM wp_options WHERE option_name='siteurl';"
wp db query "DELETE FROM wp_comments WHERE comment_approved='spam';"
wp db query "UPDATE wp_posts SET post_status='publish' WHERE ID=42;"
wp db query < migration.sql                               # Run SQL file
echo "SELECT COUNT(*) FROM wp_users;" | wp db query      # Stdin

Next Steps

• wp db export — back up before running destructive queries.
• wp search-replace — safe way to update serialized strings.
• wp db tables — list available tables to query.