MySQL – How to Replace User’s Names with Random Names

MySQLrandom

How can I use SQL only on mysql DB to replace first and last names of real users in users table using a random name from two related tables: random_first_names and random_last_names. Our users table contains over 250K records and each of the rundome tables contain over 5000 names that should be picked at random for each record in users table. Is it possible to achieve with SQL only?

[UPDATE]

I solved the problem based on Rick's input and shared the full SQL script below.

Best Answer

If you decide that the Update is too slow, I suggest the following will be about 1000 times as fast.

Loop (can be done in a Stored Proc)...

[Re]Create a table with randomly ordered set first_names, with a `PRIMARY KEY of 1..5000. Ditto for last_names (a second table).
Multi-table UPDATE the 'next' 5000 rows joined to the two random tables. Use ON Users.id % 5000 = RandomFirstNames.id (etc)

End Loop

Shuffling the table (step 1 of the loop) is something like

CREATE TABLE RandomFirstNames (
    id SMALLINT UNSIGNED AUTO_INCREMENT,
    first_name VARCHAR(...),
    PRIMARY KEY(id) )
SELECT first_name FROM FirstNames ORDER BY RAND();

After OP's UPDATE

Don't do

SELECT count(id) INTO count_names FROM _RandomFirstNames;

Instead, do this once:

SELECT @mask_ct := COUNT(*) FROM _masked_names;

and use @mask_ct instead of count_names;

As 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. This should give you ids without gaps (unless you are on a multi-Master cluster of any kind).

Related Solutions

Mysql – How to make MySQL auto increment in random order

Here's what I would do. I hope I did understand your question correctly. It's a workaround and perhaps not very elegant:

create a random field, and then an AUTO_INCREMENT field ("ID") as the single field of a primary key, order by RND etc
create a temporary table with MIN(ID) for each combination of Col1/2 (first, but randomly):


SELECT
  MIN(ID) AS MIN_ID, Col1, Col2 
FROM 
  table 
GROUP BY 
 Col1, Col2

add a second INT field to be the first_index
update the table and set first_index to be the difference between the minimum value and the running value of the autoincremented field:


UPDATE 
  table t, temp_table tmp
SET 
  t.first_index = (t.ID - tmp.MIN_ID) + 1
WHERE 
  t.Col1=tmp.Col1 AND t.Col2=tmp.Col2

optionally remove the random and ID field, and add a new PK with the three fields Col1/Col2/first_index (auto incremented) for following inserts

Mysql – Help with a complicated MySQL Query inserting data using select from 2 tables

Okay its done, the cursor usage worked

here is the code m using

UPDATE player_list_items SET player_list_item_cellX = cell_X, player_list_item_cellY = cell_Y
             WHERE player_list_id IN (
                 SELECT player_list_id FROM (
                 SELECT player_list_id FROM player_list_items 
                 WHERE player_list_player_id = playerID AND player_list_list_id = @listID AND player_list_item_cellX IS NULL
                 ORDER BY player_list_id LIMIT 1
                 ) tmp
             );

please let me know if this solution is not a good one... as I found nothing besides this to update multiples rows with different values..

its in a loop against cursor that have all the map_details data needs to be put in this table

Best Answer

Related Solutions

Mysql – How to make MySQL auto increment in random order

Mysql – Help with a complicated MySQL Query inserting data using select from 2 tables

Related Question