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

I worry about why you want to do this, but you could simply use:

SELECT FLOOR(RAND() * 1000000);

See the full MySQL RAND documentation for more information.

However, I hope you're not using this as a unique identifier (use an auto_increment attribute on a suitably large unsigned integer field if this is what you're after) and I have to wonder why you'd use MySQL for this and not a scripting language. What are you trying to achieve?

Answer 3

Create a unique index on the field you want the unique random #

Then run

Update IGNORE Table Set RandomUniqueIntegerField=FLOOR(RAND() * 1000000);