How to Generate unique random number?

Question

Can someone tell me a good method for automatically placing a unique random number in a mysql database table when a new record is created.

Answer 1

I would create a table with a pool of numbers:

Create Table pool (number int PRIMARY KEY AUTO_INCREMENT);
Insert Into pool (),(),(),(),(),(),(),(),…;

And then define a trigger which picks one random number from that pool:

CREATE TRIGGER pickrand BEFORE INSERT ON mytable 
FOR EACH ROW BEGIN
  DECLARE nr int;
  SET nr = (SELECT number FROM pool order by rand() limit 1);
  DELETE FROM pool WHERE number = nr;
  SET NEW.nr = nr;
END

In order to avoid concurrency issues you have to run queries in transactions. If performance becomes an issue (because of the slow order by rand()) you can change the way to select a random record.

Answer 2

Your best option is Autoincrement Column

see here for syntax

perhaps for Random number try this!

select FLOOR(RAND() * 401) + 100

edit

SELECT FLOOR(RAND() * 99999) AS sup_rand
FROM table
WHERE "sup_rand" NOT IN (SELECT sup_rand FROM table)
LIMIT 1

Steps:

1.Generate a random number
2.Check if its already present
3.if not continue use the number
4.Repeat

Answer 3

Your criteria of unique and random are generally conflicting. You can easily accomplish one or the other, but both is difficult, and would require evaluating every row when testing a new potential number to insert.

The best method that meets your criteria is to generate a UUID with the UUID function.

The better choice would be to re-evaluate your design and remove one of the (unique, random) criteria.