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.
Build a look-up table from sequential numbers to randomised id values in range 1 to 1M:
(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 tried to use this answer, but it didn't work for me, so I had to change the original query a little.
The only half-way reasonable idea I can come up with is to create a table with a finite pool of IDs and as they are used remove them from that table. Those keys can be unique and a script could be created to generate that table. Then you could pull one of those keys by generating a random select from the available keys. I said 'half-way' reasonable and honestly that was being way to generous, but it beats randomly generating keys until you create a unique one I suppose.
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;
Fiddle here; http://sqlfiddle.com/#!2/620de1/1
Hope its helpful :)
The RAND() function will generate a random number, but will not guarantee uniqueness. The proper way to handle unique identifiers in MySQL is to declare them using AUTO_INCREMENT.
For example, the id field in the following table will not need to be supplied on inserts, and it will always increment by 1:
My solution, implemented in Cakephp 2.4.7, is to create a table with one auto_incremental type field
I then created a php function so that every time insert a new record, reads the generated id and delete it immediately. Mysql keeps in its memory counter status. All the numbers generated are unique until you reset the mysql counter or you run a TRUNCATE TABLE operation
find below the Model created in Cakephp to implement all
Any checks on the range of belonging of the result can be implemented in the same function, by manipulating the result obtained