CODEWITHSUNDEEP
phpmysqlsql
freento
freento

Reputation: 2949

Random unique Mysql Id

I have a table and want to genarate random unique value to it using 1 mysql query. Table structure like:

id | unique_id
1  | 1
2  | 5
3  | 2
4  | 7

etc

unique_id is integer(10) unsigned

So I want to fullfill unique_id field with unique random value (not 1, 5, 2, 7 in my example) every time.

Algorytm is:

1. Get 1 unique random value, which will not have duplicates in unique_id field
2. Create new row with unique_id = result of 1 query

I tried

SELECT FLOOR(RAND() * 9) AS random_number 
FROM table 
HAVING random_number NOT IN (SELECT unique_id FROM table) 
LIMIT 1

but it generates not unique values..

Note: multiplier = 9 is given just for example, it is easy to reproduce this problem with such multiplier

Upvotes: 3

Views: 4244

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

One way to do this is to use the id column, just as a random permutation:

select id
from tables
order by rand()
limit 1

(Your example only returns one value.)

To return a repeatable random number for each id, you can do something like:

select id,
       (select count(*) from table t2 where rand(t2.id) < rand(t.id)) as randomnumber
from table t

What this is doing is producing a stable sort order by seeding the random number generator. This guarantees uniqueness, although this is not particularly efficient.

A more efficient alternative that uses variables is:

SELECT  id, @curRow := @curRow + 1 AS random_number
FROM table CROSS JOIN (SELECT @curRow := 0) r
order by rand()

Note: this returns random numbers up to the size of the table, not necessarily from the ids. This may be a good thing.

Finally, you can get the idea that you were attempting to work with a bit of a trick. Calculate an md5 hash, then cast the first four characters as an integer and check back in the table:

SELECT convert(hex(left(md5(rand()), 4)), unsigned) AS random_number 
FROM table 
HAVING random_number NOT IN (SELECT unique_id FROM table) 
LIMIT 1

You need to insert the value back into the table. And, there is no guarantee that you will actually be able to get a value not in the table, but it should work for up to millions of values.

Upvotes: 3

Daniel Park
Daniel Park

Reputation: 4021

If you have the availability to use an MD5 hash as a unique_ID, go for MD5(NOW()). This will almost certainly generate a unique ID every time.

Reference: MySQL Forums

Upvotes: 0

Related Questions