UPDATE a column in each row with a random unique number

Question

I have a small table about 20 thousand rows in size. This table has a column called random_uid ( INT NOT NULL ). I would like to update all 20k lines with a random unique number.

Since my table is small, I don’t think I need to use a row or UUID, so I went with

 SELECT FLOOR(RAND() * 100000000) AS random_num FROM table1 WHERE "random_num" NOT IN (SELECT random_uid FROM table1) LIMIT 1; 

My problem is that I cannot update and select from the same table, so I was unable to create an UPDATE query.

Edit: I have no problem with the above randomness, since I am not using this for any protection chains, just to create unique identifiers for each line that are not just incrementing. Due to the selection that is used to verify that the same number no longer exists on the other line, I cannot use UPDATE , that’s the problem.

Answer 1

Tested on 2M records, 100 iterations. The test was successful.

 UPDATE IGNORE table1 SET random_uid = ( RAND( ) * ( SELECT countID FROM ( SELECT MAX(random_uid) + COUNT(1) + 1 countID FROM table1) AS t3) ) + ( SELECT maxID FROM (SELECT MAX( random_uid ) maxID FROM table1) AS t) 

Answer 2

You can use the update trigger to modify existing lines and the insert trigger to generate random numbers for new lines. In the body of the trigger, you generate a random number and check if it exists in the table. You do this in a loop and leave the loop as soon as you find a new (unique) number.

UPDATE trigger

 DELIMITER // create trigger table1_before_update before update on table1 for each row begin declare rnd_num integer; loop1: loop set rnd_num := floor(rand() * 100000000); if not exists (select * from table1 where random_num = rnd_num) then set new.random_num = rnd_num; leave loop1; end if; end loop; end// DELIMITER ; 

You can update all rows of the table:

 update table1 set random_num = null where 1 = 1; 

Note that the random_num column must be NULL. But it could be UNIQUE . So you can define it as random_num int null unique .

Since you only need to do this step once, you can remove this trigger.

INSERT trigger

 DELIMITER // create trigger table1_before_insert before insert on table1 for each row begin declare rnd_num integer; loop1: loop set rnd_num := floor(rand() * 100000000); if not exists (select * from table1 where random_num = rnd_num) then set new.random_num = rnd_num; leave loop1; end if; end loop; end// DELIMITER ; 

An INSERT trigger has the same body. When you insert new rows, you do not need to set the random_num column. The trigger will take care of this. It even works great with volume inserts:

 insert into table1 (data) values ('data1'), ('data2'), ('data3'), ('data4'), ('data5'); 

Demo: http://rextester.com/ZIDG57947

Note that I use FLOOR(RAND() * 10) in the demo to demonstrate uniqueness over a small range. However - you should not try to insert more lines than the number of unique numbers possible :-)

With 20K lines and 100M possible unique numbers, a loop will be needed as 1.0002 (average) iterations per line.

Answer 3

Here is an easy way to do this. I filled the test table with 512 lines, then did the following:

 mysql> set @i = 0; mysql> update table1 set random_num = @i: =@i +1 order by rand(); mysql> select * from table1 limit 10; +----+------------+ | id | random_num | +----+------------+ | 1 | 345 | | 2 | 108 | | 3 | 18 | | 4 | 247 | | 6 | 202 | | 7 | 275 | | 8 | 289 | | 9 | 121 | | 13 | 237 | | 14 | 344 | +----+------------+ 

Numbers are now assigned randomly to rows, but each row has a unique value.

It will not be so random when assigning values ​​to subsequently inserted rows.

Answer 4

Probably the easiest way to do this is to retry this request again:

 UPDATE table1 SET random_uid = FLOOR(RAND() * 100000000); 

Between each round you can call:

 SELECT random_uid, COUNT(*) FROM table1 GROUP BY random_uid HAVING COUNT(*) > 1 

to see if there are duplicates.

If you work in MySQL Workbench, you can create a temporary procedure for this, for example:

 DELIMITER ;; DROP PROCEDURE IF EXISTS __SET_UNIQUE_IDS__;; CREATE PROCEDURE __SET_UNIQUE_IDS__() BEGIN while_loop: WHILE 1 = 1 DO UPDATE table1 SET random_uid = FLOOR(RAND() * 100000000); IF NOT EXISTS (SELECT random_uid FROM table1 GROUP BY random_uid HAVING COUNT(*) > 1) THEN LEAVE while_loop; END IF; END WHILE; END ;; CALL __SET_UNIQUE_IDS__();; DROP PROCEDURE __SET_UNIQUE_IDS__;; DELIMITER ; 

It's really just a rough way to do it, and there are all sorts of ways you could optimize for performance, but it speeds things up, quickly and dirty. I would recommend doing this in a different way, for example. with UUID.