UPDATE a column in every row with a random unique number

Question

I have a small table of about 20k rows. In that table there is a column named random_uid (INT NOT NULL). I would like to update all 20k rows with a random unique number.

Since my table is small I dont think I need to use a string or a 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 cant update and select from the same table, so I was having trouble creating the UPDATE query.

Edit: I do not have an issue with the above randomness, since I am not using this for any security purpoces, simply to create unique ids for each row that are not just incremented. Because of the select that am using to verify that the same number doesnt already exist in another row, I cant use UPDATE, thats the issue.

Answer 1

Update 2023-06-08

You an accomplish this easily on mysql 8 if you are happy with UUIDs by:

SET field = (UUID())
where id=23;

Adjust the where clause accordingly to target the rows you wish to update. I have supplied id=23 just to minimize the risk of someone updating all rows in their database unwittingly by copy-pasting.

Original Post

Borrowing from @BillKarwin , UUIDs provide a way to get random and Unique fields in databases, and Mysql8 has some partial but totally workable support.

To store them, your field needs to be VARCHAR(37). Ensure you have assigned a unique constraint to the field as well. There are more efficient ways of storing these by packing them into binary of 16-bytes size, but that is outside the scope of this post -- there are other posts online that explain how to pack uuids.

SET @i = UUID(); 
UPDATE table1 set uuid_field = @i := UUID(); 
SELECT * from table1 LIMIT 10;

After the above query, each uuid_field has a universally unique id. You would also need to have a trigger that fills the value of the column on insert. Assuming the field is called 'uuid_field' and is a varchar, here's the trigger:

CREATE TRIGGER trigger_name
BEFORE INSERT ON table1
FOR EACH ROW
SET new.uuid_field = UUID();

Answer 2

If you have an unique ID, you could do something like this:

UPDATE table SET field = CONCAT(MD5(CONCAT(id, NOW())), '-', id); 

First of all, create a random value based on the MD5 string of your id and the current full datetime. As MD5 is not infinite, you could obtain the same hash from two different strings; you can solve it by concatening the original id to the MD5 string, because that id is unique.

Answer 3

You can generate a random sequence of the first N integer numbers and update your table with that (where N is the number of rows in your table).

Update table1 as st join (Select id, rnd_id
from (Select @rn3:=@rn3+1 as rowid, id from (select @rn3:=-1) as t4 cross join table1) as t5
join 
(Select @rn2:=@rn2+1 as rowid, rnd_id from (SELECT @rn2:=-1) as t1 cross join
(Select @rn1:=@rn1+1 as rnd_id from (SELECT @rn1:=-1) as t3 cross join table1 order by Rand()) as t2) as t6
on t5.rowid=t6.rowid) as t7 on st.id=t7.id set st.random_id=t7.rnd_id;

Explanation:

(Select @rn1:=@rn1+1 as rnd_id from (SELECT @rn1:=-1) as t3 cross join table1 order by Rand()) as t2

builds the random sequence of N numbers. we use a variable that increments for each line. (SELECT @rn1:=-1) as t3 cross join is equivalent to set @rn1:=-1; we use the cross join trick to put the two statements in only one line. So this generates the sequence from 0 to N-1 and scrambles it with order by Rand()

we augment this table with a row number by

(Select @rn2:=@rn2+1 as rowid, rnd_id from (SELECT @rn2:=-1) as t1 cross join ...

we augment the original table with the row number in similar way:

(Select @rn3:=@rn3+1 as rowid, id from (select @rn3:=-1) as t4 cross join table1) as t5

and we join the two parts using the row number:

on t5.rowid=t6.rowid

We effectively built a table with a column containing the id, and another column containing the random_uid (called rnd_id) mentioned on the question. At this point we can proceed with the update, augmenting the table with our new rnd_id table and setting the random_uid in the original table (called here random_id) equal to rnd_id:

Update table1 as st join ... as t7 on st.id=t7.id set st.random_id=t7.rnd_id;

Regarding the trouble in using an update and select with the same table, i think the trick is to use different aliases for the tables. refer to MySql - Update table using select statment from same table

This solves the problem of populating the random_uid for all the table. In my case when i'd add a row, i just add a random_uid that is equal to the number of elements in the table, so N (cause i start from 0). This is good enough in my case but not in general, depending on your constraints.

Answer 4

Here's a simple way to do it. I filled a test table with 512 rows, then did this:

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 |
+----+------------+

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

It won't be as random while assigning values to subsequently inserted rows, though.

Answer 5

You can use an update trigger to modify existing rows, and an insert trigger to generate random numbers for new rows. In the trigger body you generate a random number and check if it already exist in the table. You do it 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 in the table with:

update table1 set random_num = null where 1 = 1;

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

Since you only need to do this step once, you can now drop that 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 ;

The INSERT trigger has the same body. When you insert new rows, you don't need to set the random_num column. The trigger will take care of it. It even works fine with bulk 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 the uniqueness on a small range. However - you should not try to insert more rows than the number of possible unique numbers :-)

With 20K rows and 100M possible unique numbers the loop will need like 1.0002 (average) iteratons per row.

Answer 6

Conducted testing on 2M records, 100 iterations. The test is 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 7

Probably the easiest way to do this is to run this query repeatedly:

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're working in MySQL Workbench, you can create a temporary procedure to do it for you, like this:

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 ;

This is really just a brute-force way of doing it, and there are all sorts of ways you could optimize for performance, but this gets the job done, quick-and-dirty. I would really recommend doing this another way, e.g. with UUIDs.