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

Answer 3

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

Answer 4

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.

Answer 5

My solution, implemented in Cakephp 2.4.7, is to create a table with one auto_incremental type field

CREATE TABLE `unique_counters` (
  `counter` int(11) NOT NULL AUTO_INCREMENT,
  `field` int(11) NOT NULL,
  PRIMARY KEY (`counter`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

App::uses('AppModel', 'Model');
/**
 * UniqueCounter Model
 *
 */
class UniqueCounter extends AppModel {

/**
 * Primary key field
 *
 * @var string
 */
    public $primaryKey = 'counter';

/**
 * Validation rules
 *
 * @var array
 */
    public $validate = array(
        'counter' => array(
            'numeric' => array(
                'rule' => array('numeric'),
                //'message' => 'Your custom message here',
                //'allowEmpty' => false,
                //'required' => false,
                //'last' => false, // Stop validation after this rule
                //'on' => 'create', // Limit validation to 'create' or 'update' operations
            ),
        ),
    );

    public function get_unique_counter(){
        $data=array();
        $data['UniqueCounter']['counter']=0;
        $data['UniqueCounter']['field']=1;

        if($this->save($data)){
            $new_id=$this->getLastInsertID();
            $this->delete($new_id);
            return($new_id);
        }
        return(-1);
    }
}

Any checks on the range of belonging of the result can be implemented in the same function, by manipulating the result obtained

Answer 6

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

Then run

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

Answer 7

I tried to use this answer, but it didn't work for me, so I had to change the original query a little.

SELECT FLOOR(1000 + RAND() * 89999) AS random_number
FROM Table
WHERE NOT EXISTS (SELECT ID FROM Table WHERE Table.ID=random_number) LIMIT 1

Answer 8

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:

CREATE TABLE animal (
     id INT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

Answer 9

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?