How to Query wp_users and wp_usermeta via SQL or WP-CLI

To query wp_users and wp_usermeta directly, use SQL when you need database-level reporting or troubleshooting, and use WP-CLI when you need a safer, WordPress-aware way to list users, inspect roles, or manage user meta. The wp_users table stores core user records, while wp_usermeta stores additional user data such as capabilities, nicknames, descriptions, and custom fields.

 

This guide shows you how to query WordPress users with SQL and WP-CLI, including how to find users by role, email, registration date, meta key, and meta value. It also explains the database prefix, serialized user capabilities, multisite table prefixes, and the safety precautions you should follow before running any query.

Understand wp_users and wp_usermeta

WordPress stores user information in two main database tables:

• wp_users — stores one row for each user account
• wp_usermeta — stores additional metadata for each user

WordPress’s learning resources describe wp_users as the table that stores all user information, with each row representing a single user. The wp_usermeta table stores additional information about each user.

wp_users table structure

Column	What it stores
ID	Unique user ID
user_login	Username used to log in
user_pass	Hashed password
user_nicename	URL-friendly version of the username
user_email	User email address
user_url	User website URL
user_registered	Date and time the user registered
user_activation_key	Password reset or activation key
user_status	Legacy user status field
display_name	Public display name

wp_usermeta table structure

Column	What it stores
umeta_id	Unique meta row ID
user_id	The ID of the user this meta belongs to
meta_key	The name of the custom field, such as nickname or wp_capabilities
meta_value	The value stored for that meta key

The WordPress database documentation lists umeta_id, user_id, meta_key, and meta_value as the main columns in wp_usermeta.

How the tables connect

The relationship is simple:

wp_users.ID = wp_usermeta.user_id

For example, if user ID 123 exists in wp_users, all metadata for that user will have user_id = 123 in wp_usermeta.

Before You Query: Safety Rules

Direct database queries are powerful. A mistake can expose private data, corrupt user records, or break your website.

1. Always back up first

Before running any SQL query—especially an UPDATE, DELETE, or INSERT query—create a full database backup.

2. Use SELECT queries first

Start with read-only SELECT queries. Do not run UPDATE or DELETE queries until you have confirmed exactly which rows will be affected.

3. Check your database prefix

The examples below use the default WordPress prefix, wp_. Many sites use a custom prefix, such as wprl_, wp2_, or mysite_.

Check your prefix in wp-config.php:

$table_prefix = 'wp_';

If your prefix is wprl_, replace wp_users with wprl_users and wp_usermeta with wprl_usermeta.

4. Be careful with serialized data

WordPress stores user roles in wp_usermeta under the wp_capabilities meta key. The value is serialized PHP, not plain text.

For example:

a:1:{s:13:"administrator";b:1;}

Do not manually edit serialized values unless you understand PHP serialization. Changing the string length or structure incorrectly can break the user’s role assignment.

5. Prefer WP-CLI or WP_User_Query when possible

Direct SQL is useful for reporting and debugging, but WP-CLI and WordPress APIs understand WordPress data structures better. WP-CLI provides commands to manage users, roles, capabilities, and meta.

SQL Query Basics for WordPress Users

Select all users

SELECT *
FROM wp_users;

Select specific user columns

SELECT ID, user_login, user_email, display_name, user_registered
FROM wp_users;

Limit the number of results

SELECT ID, user_login, user_email, display_name
FROM wp_users
LIMIT 20;

Order users by registration date

SELECT ID, user_login, user_email, user_registered
FROM wp_users
ORDER BY user_registered DESC;

Count all users

SELECT COUNT(*) AS total_users
FROM wp_users;

SQL Examples for wp_users

Find a user by email

SELECT ID, user_login, user_email, display_name
FROM wp_users
WHERE user_email = 'example@example.com';

Find a user by username

SELECT ID, user_login, user_email, display_name
FROM wp_users
WHERE user_login = 'johndoe';

Find users by partial email

SELECT ID, user_login, user_email, display_name
FROM wp_users
WHERE user_email LIKE '%@example.com';

Find users registered after a date

SELECT ID, user_login, user_email, user_registered
FROM wp_users
WHERE user_registered >= '2026-01-01 00:00:00'
ORDER BY user_registered DESC;

Find users registered in the last 30 days

SELECT ID, user_login, user_email, user_registered
FROM wp_users
WHERE user_registered >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY user_registered DESC;

Find duplicate email addresses

SELECT user_email, COUNT(*) AS duplicate_count
FROM wp_users
GROUP BY user_email
HAVING COUNT(*) > 1;

SQL Examples for wp_usermeta

View all meta for one user

SELECT umeta_id, meta_key, meta_value
FROM wp_usermeta
WHERE user_id = 123
ORDER BY meta_key ASC;

Find users with a specific meta key

SELECT user_id, meta_key, meta_value
FROM wp_usermeta
WHERE meta_key = 'nickname';

Find users by meta value

SELECT user_id, meta_key, meta_value
FROM wp_usermeta
WHERE meta_key = 'country'
  AND meta_value = 'Canada';

Find users by partial meta value

SELECT user_id, meta_key, meta_value
FROM wp_usermeta
WHERE meta_key = 'description'
  AND meta_value LIKE '%developer%';

Find users with empty meta values

SELECT user_id, meta_key
FROM wp_usermeta
WHERE meta_key = 'phone_number'
  AND (meta_value = '' OR meta_value IS NULL);

Count users by a custom meta value

SELECT meta_value, COUNT(*) AS user_count
FROM wp_usermeta
WHERE meta_key = 'country'
GROUP BY meta_value
ORDER BY user_count DESC;

Find serialized administrator capabilities

SELECT user_id, meta_key, meta_value
FROM wp_usermeta
WHERE meta_key = 'wp_capabilities'
  AND meta_value LIKE '%administrator%';

Important: This query finds users whose serialized capabilities contain the word administrator. It is useful for auditing, but it is not the safest way to manage roles. Use WP-CLI or the WordPress dashboard for role changes.

Join wp_users and wp_usermeta

Join users with a single meta field

SELECT u.ID, u.user_login, u.user_email, m.meta_value AS country
FROM wp_users u
INNER JOIN wp_usermeta m ON u.ID = m.user_id
WHERE m.meta_key = 'country';

Find users by meta key and value

SELECT u.ID, u.user_login, u.user_email, m.meta_value AS country
FROM wp_users u
INNER JOIN wp_usermeta m ON u.ID = m.user_id
WHERE m.meta_key = 'country'
  AND m.meta_value = 'Canada';

Find users with two meta conditions

SELECT u.ID, u.user_login, u.user_email,
       country.meta_value AS country,
       company.meta_value AS company
FROM wp_users u
INNER JOIN wp_usermeta country
  ON u.ID = country.user_id
  AND country.meta_key = 'country'
INNER JOIN wp_usermeta company
  ON u.ID = company.user_id
  AND company.meta_key = 'company'
WHERE country.meta_value = 'Canada'
  AND company.meta_value LIKE '%Acme%';

Find users with a custom meta field but no value

SELECT u.ID, u.user_login, u.user_email
FROM wp_users u
LEFT JOIN wp_usermeta m
  ON u.ID = m.user_id
  AND m.meta_key = 'phone_number'
WHERE m.meta_value IS NULL
  OR m.meta_value = '';

Find the most recently registered users with a custom meta field

SELECT u.ID, u.user_login, u.user_email, u.user_registered, m.meta_value AS company
FROM wp_users u
INNER JOIN wp_usermeta m
  ON u.ID = m.user_id
  AND m.meta_key = 'company'
ORDER BY u.user_registered DESC
LIMIT 25;

Query Users with WP-CLI

WP-CLI is usually safer than direct SQL because it understands WordPress roles, capabilities, and user meta. The official WP-CLI documentation describes wp user as the command group for managing users, roles, capabilities, and meta.

List all users

wp user list

The WordPress CLI handbook lists wp user list as the standard command for listing all users. [336]

List users in a table format

wp user list --format=table

List specific user fields

wp user list --fields=ID,user_login,user_email,display_name,roles

List users by role

wp user list --role=administrator

The official WP-CLI documentation shows that wp user list supports role filtering and CSV output.

List administrators as CSV

wp user list --role=administrator --format=csv

List users by email domain

wp user list --search='*@example.com' --fields=ID,user_login,user_email

Search users by name or email

wp user list --search='john'

Count all users

wp user list --format=count

Count administrators

wp user list --role=administrator --format=count

Get one user by ID

wp user get 123

Get one user by email

wp user get example@example.com

List user capabilities

wp user list-caps 123

WP-CLI includes wp user list-caps for listing all capabilities assigned to a user. [329]

WP-CLI User Meta Examples

WP-CLI provides dedicated commands for listing, adding, updating, and deleting user meta. The official documentation describes wp user meta as the command group for managing user custom fields.

List all meta for a user

wp user meta list 123

List specific meta keys

wp user meta list 123 --keys=nickname,description,wp_capabilities

This is the same example shown in the official WP-CLI documentation for listing selected user meta keys.

List meta in JSON format

wp user meta list 123 --format=json

List meta in CSV format

wp user meta list 123 --format=csv

Get one meta value

wp user meta get 123 nickname

Add a new user meta field

wp user meta update 123 company "Acme Corporation"

Update an existing user meta field

wp user meta update 123 phone_number "+15551234567"

Delete a user meta field

wp user meta delete 123 phone_number

Run a SQL query through WP-CLI

wp db query "SELECT ID, user_login, user_email FROM wp_users LIMIT 10"

WP-CLI includes wp db query for executing SQL queries using the database credentials configured in WordPress.

Query users with WP-CLI and export to CSV

wp user list --role=administrator --fields=ID,user_login,user_email,roles --format=csv > administrators.csv

Multisite Database Prefixes

On WordPress Multisite, users are usually stored in the main site’s wp_users and wp_usermeta tables. However, role and capability data can differ because each site in the network can assign different roles.

Check the main site prefix

If your main site uses wp_, user tables are usually:

wp_users
wp_usermeta

Check custom prefixes

If your main site uses wprl_, the tables may be:

wprl_users
wprl_usermeta

Query users on a Multisite network

SELECT ID, user_login, user_email, user_registered
FROM wp_users
ORDER BY user_registered DESC;

Find users with administrator capabilities

SELECT user_id, meta_value
FROM wp_usermeta
WHERE meta_key = 'wp_capabilities'
  AND meta_value LIKE '%administrator%';

Warning: On Multisite, a user may be an Administrator on one site but not another. Direct SQL can show the stored capability value, but WP-CLI and WordPress APIs are better for understanding site-specific roles.

Safer Alternatives to Direct SQL

1. Use WP_User_Query in PHP

WordPress provides WP_User_Query for querying users and user meta safely. The official documentation states that this class queries the wp_users and wp_usermeta tables. [334]

Find users by meta key and value

$user_query = new WP_User_Query(array(
    'meta_key'   => 'country',
    'meta_value' => 'Canada',
));

$users = $user_query->get_results();

foreach ($users as $user) {
    echo esc_html($user->display_name) . '<br>';
}

Find users by role

$user_query = new WP_User_Query(array(
    'role' => 'administrator',
));

$users = $user_query->get_results();

Find users with multiple meta conditions

$user_query = new WP_User_Query(array(
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'key'   => 'country',
            'value' => 'Canada',
        ),
        array(
            'key'   => 'company',
            'value' => 'Acme',
            'compare' => 'LIKE',
        ),
    ),
));

$users = $user_query->get_results();

WordPress documents that WP_User_Query supports meta_key, meta_value, and meta_compare arguments.

2. Use WP-CLI instead of phpMyAdmin

WP-CLI is safer for common user tasks because it uses WordPress functions and avoids many direct-database mistakes.

Task	SQL approach	WP-CLI approach
List users	SELECT * FROM wp_users;	wp user list
List administrators	Query serialized wp_capabilities	wp user list --role=administrator
View user meta	Query wp_usermeta	wp user meta list 123
Update user meta	Risky UPDATE query	wp user meta update
Delete user meta	Risky DELETE query	wp user meta delete

Query wp_users and wp_usermeta: Frequently Asked Questions

What is the wp_users table in WordPress?

The wp_users table stores core WordPress user records, including user ID, username, email address, display name, registration date, and hashed password.

What is the wp_usermeta table in WordPress?

The wp_usermeta table stores additional user metadata, such as nicknames, descriptions, capabilities, application passwords, and custom fields created by plugins or themes.

How do I query WordPress users with SQL?

Use a SELECT query against wp_users. For example: SELECT ID, user_login, user_email FROM wp_users;. Replace wp_ with your actual database prefix if it is different.

How do I query WordPress user meta with SQL?

Use a SELECT query against wp_usermeta. For example: SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE meta_key = 'nickname';

How do I find WordPress users by role using SQL?

Query the wp_capabilities meta key in wp_usermeta and search for the role name. However, use WP-CLI’s wp user list --role=administrator instead when possible, because WordPress roles are stored as serialized data.

How do I query WordPress users with WP-CLI?

Use wp user list. To list administrators, use wp user list --role=administrator. To export results, add --format=csv.

How do I list user meta with WP-CLI?

Use wp user meta list USER_ID. For example: wp user meta list 123. To limit output to specific keys, use --keys=nickname,description,wp_capabilities.

Is it safe to edit wp_usermeta directly?

It can be safe for simple, non-serialized values, but it is risky for serialized data such as wp_capabilities. Use WP-CLI or WordPress functions whenever possible.

Why does my WordPress database prefix not start with wp_?

Many site owners change the default database prefix for security reasons. Check the $table_prefix value in wp-config.php and use that prefix in your SQL queries.

What is the difference between wp_users and wp_usermeta?

wp_users stores one main record per user. wp_usermeta stores additional key-value metadata connected to each user through the user_id column.

Query wp_users and wp_usermeta: Final Thoughts

Querying wp_users and wp_usermeta directly is useful for audits, custom reports, migrations, and troubleshooting. Use SQL when you need precise database-level queries, and use WP-CLI when you need a safer, WordPress-aware way to manage users and meta.

Before changing any user data, back up your database, confirm your table prefix, test queries on staging, and prefer WP_User_Query or WP-CLI for production tasks.

Related guides: WordPress User Management | WordPress Roles and Permissions Guide | WordPress Role Audit Checklist | WordPress Activity Log: Track Every User Change

---