Instantly share code, notes, and snippets.

@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.

Owner

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