mysql unique number generation

Question

I want to generate a unique random integer (from 10000 to 99999) identity just by clean mySQL; any ideas?

I don't want to generate this number in php by cycling (generate number -> check it in database) because I want to use some intelligent solution in a mySQL query.

Answer 1

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

SELECT FLOOR(10000 + RAND() * 89999) AS random_number
FROM table
WHERE 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

Answer 2

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).