Skip to content

Instantly share code, notes, and snippets.

@macbookandrew

macbookandrew/wp_usermeta.sql Secret

Last active Jul 12, 2018
Embed
What would you like to do?
Clone dummy WP users
/* Create temporary table */
DROP TEMPORARY TABLE IF EXISTS wp_usermeta_to_clone;
CREATE TEMPORARY TABLE wp_usermeta_to_clone
SELECT * FROM wp_usermeta WHERE user_id >= 100; /* Set this to the ID of your first dummy user */
/* Set increment variables */
/* Update @ID_increment to 100K, 150K, etc., run this, and run then the `INSERT` query below as necessary */
SET @ID_increment = '50000';
/* Sanity checks */
SELECT * FROM wp_usermeta WHERE user_id >= 100 ORDER BY user_id ASC LIMIT 10;
SELECT * FROM wp_usermeta_to_clone WHERE user_id >= 100 ORDER BY user_id ASC LIMIT 10;
SELECT (user_id + @ID_increment) AS user_id, meta_key, meta_value FROM wp_usermeta_to_clone ORDER BY user_id ASC LIMIT 10;
/* Insert back into users table; update the variables above and run this as many times as necessary */
INSERT INTO wp_usermeta (`user_id`, `meta_key`, `meta_value`)
SELECT (user_id + @ID_increment) AS user_id, meta_key, meta_value FROM wp_usermeta_to_clone ORDER BY user_id ASC;
/* Drop the temporary table */
DROP TEMPORARY TABLE wp_usermeta_to_clone;
/* Create temporary table */
DROP TEMPORARY TABLE IF EXISTS wp_users_to_clone;
CREATE TEMPORARY TABLE wp_users_to_clone
SELECT * FROM wp_users WHERE ID >= 100; /* Set this to the ID of your first dummy user */
/* Set increment variables */
/* Update @increment to '-3', '-4', etc. and @ID_increment to 100K, 150K, etc., run these, and then run the `INSERT` query below as many times as necessary */
SET @increment = '-2' COLLATE utf8mb4_unicode_ci;
SET @ID_increment = '50000';
/* Sanity checks */
SELECT * FROM wp_users WHERE ID > 100 ORDER BY ID ASC LIMIT 10;
SELECT * FROM wp_users_to_clone WHERE ID > 100 ORDER BY ID ASC LIMIT 10;
SELECT (ID + 50000) AS ID, CONCAT(user_login, @increment) AS user_login, user_pass, CONCAT(user_nicename, @increment) AS user_nicename, REPLACE(user_email, '@', CONCAT(@increment, '@')) AS user_email, user_url, user_registered, user_activation_key, user_status, CONCAT(display_name, @increment) AS display_name FROM wp_users_to_clone ORDER BY ID ASC LIMIT 10;
/* Insert back into users table; update the variables above and run this as many times as necessary */
INSERT INTO wp_users
SELECT (ID + 50000) AS ID, CONCAT(user_login, @increment) AS user_login, user_pass, CONCAT(user_nicename, @increment) AS user_nicename, REPLACE(user_email, '@', CONCAT(@increment, '@')) AS user_email, user_url, user_registered, user_activation_key, user_status, CONCAT(display_name, @increment) AS display_name FROM wp_users_to_clone ORDER BY ID ASC;
/* Drop the temporary table */
DROP TEMPORARY TABLE wp_users_to_clone;
@macbookandrew

This comment has been minimized.

Copy link
Owner Author

@macbookandrew macbookandrew commented Jul 12, 2018

For details, see accompanying blog post.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment