![]() Left join _RandomLastNames l on l.id = u.idĪnd the last step, drop the tables with random names as we no longer need those DROP TABLE IF EXISTS _RandomFirstNames Īdding primary key after the tables were populated with random names solved an issue with the index skipping count. Left join _RandomFirstNames f on f.id = u.id Now we can update user table with the random names by joining the two new tables with the random names that we created above UPDATE users u ![]() We can now execute it and add incremental IDs to the populated tables CALL prepare_randon_names() ĪLTER TABLE _RandomFirstNames ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY ĪLTER TABLE _RandomLastNames ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY SELECT lastname FROM _masked_names._lastnames ORDER BY RAND() SELECT := COUNT(*) FROM _masked_names._lastnames SELECT firstname FROM _masked_names._firstnames ORDER BY RAND() INSERT INTO _RandomFirstNames (first_name) SELECT := COUNT(*) FROM _masked_names._firstnames SELECT := id FROM users ORDER BY id DESC LIMIT 0, 1 DELIMITER $$ĭROP PROCEDURE IF EXISTS prepare_randon_names$$ Then created a procedure to fill those tables with random names to make sure that we have one first name and one last name per each possible user ID. Rick's answer was the closest one and his comments helped me to create the full solution which I'm sharing below.įirst, create temporary tables to store random names DROP TABLE IF EXISTS _RandomFirstNames ĬREATE TABLE _RandomFirstNames (first_name VARCHAR(255)) ĬREATE TABLE _RandomLastNames (last_name VARCHAR(255)) Sticky Bit's solution would take to long to run. Thank you Rick and Sticky Bit for you inputs. This should give you ids without gaps (unless you are on a multi-Master cluster of any kind). Instead, do this once: SELECT := COUNT(*) FROM _masked_names Īs for the skipped ids, CREATE TABLE _RandomFirstNames without an id, then ALTER TABLE _RandomFirstNames ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY to get the ids. SELECT first_name FROM FirstNames ORDER BY RAND() ĭon't do SELECT count(id) INTO count_names FROM _RandomFirstNames Shuffling the table (step 1 of the loop) is something like CREATE TABLE RandomFirstNames ( Use ON Users.id % 5000 = RandomFirstNames.id (etc) ![]()
0 Comments
Leave a Reply. |