mysql unique number generation

While it seems somewhat awkward, this is what can be done to achieve the goal:

SELECT FLOOR(10000 + RAND() * 89999) AS random_numberFROM tableWHERE random_number NOT IN (SELECT unique_id FROM table)LIMIT 1

Simply put, it generates N random numbers, where N is the count of table rows, filters out those already present in the table, and limits the remaining set to one.

It could be somewhat slow on large tables. To speed things up, you could create a view from these unique ids, and use it instead of nested select statement.

EDIT: removed quotes

Build a look-up table from sequential numbers to randomised id values in range 1 to 1M:

create table seed ( i int not null auto_increment primary key );insert into seed values (NULL),(NULL),(NULL),(NULL),(NULL),                        (NULL),(NULL),(NULL),(NULL),(NULL);insert into seed select NULL from seed s1, seed s2, seed s3, seed s4, seed s5, seed s6;delete from seed where i < 100000;create table idmap ( n int not null auto_increment primary key, id int not null );insert into idmap select NULL, i from seed order by rand();drop table seed;select * from idmap limit 10;+----+--------+| n  | id     |+----+--------+|  1 | 678744 ||  2 | 338234 ||  3 | 469412 ||  4 | 825481 ||  5 | 769641 ||  6 | 680909 ||  7 | 470672 ||  8 | 574313 ||  9 | 483113 || 10 | 824655 |+----+--------+10 rows in set (0.00 sec)

(This all takes about 30 seconds to run on my laptop. You would only need to do this once for each sequence.)

Now you have the mapping, just keep track of how many have been used (a counter or auto_increment key field in another table).

I struggled with the solution here for a while and then realised it fails if the column has NULL entries. I reworked this with the following code;

SELECT FLOOR(10000 + RAND() * 89999) AS my_tracker FROM Table1 WHERE "tracker" NOT IN (SELECT tracker FROM Table1 WHERE tracker IS NOT NULL) LIMIT 1

Fiddle here;http://sqlfiddle.com/#!2/620de1/1

Hope its helpful :)