MySQL唯一数字生成

Question

我希望通过纯净的mySQL生成一个独特的随机整数身份证号码（从10000到99999）; 有任何想法吗？

我不想通过循环在php中生成此数字（生成数字 -> 在数据库中检查它），因为我想在mySQL查询中使用一些智能解决方案。

- hippout

---

与表中的其他条目不同的是什么？ - Brad Christie

检查这将需要一个自定义函数，随着可用数字池的减少，它会逐渐变慢。即：生成随机数，检查表格，存在吗？重新生成，检查表格，存在吗？... - Darryl E. Clarke

如果这是为了为每一行生成唯一的ID，请按照我的答案使用auto_increment字段 - 这就是它们的用途。 :-) - John Parker

@hippout 意思是您想要一个在给定范围内但不在表列中的数字？您是否还想跟踪先前的输出，即不允许查询以后再次生成相同的数字？ - jball

@middaparka 是的，auto_increment 会按照预定义的顺序生成订单，但是 OP 要求一个唯一的随机整数。我也曾经需要这样的数字，我使用了创建-检查循环的方法 - 但我更喜欢更好的方法。 - khany

Answer 1

虽然看起来有些尴尬，但这就是为了实现目标所能做的事情：

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

简单来说，它生成N个随机数，其中N是表行数，过滤掉已经存在于表中的数字，并将剩余集合限制为一个。

在大表上可能会比较慢。为了加快速度，您可以从这些唯一ID创建视图，并使用它来代替嵌套的选择语句。

编辑：删除引号

- unclenorton

---

需要注意的一件事是，这要求table中至少有一条记录，否则不会返回任何随机数，这可能会引起问题。 - Anton Gildebrand

它将是一个字符串，被视为数字“0”。 - Rick James

@unclenorton 我一直收到> 1054 - Unknown column 'random_number'在'where clause'中的错误提示。 - user3649739

你怎么使用这个查询来更新现有字段？ - Piyush Dubey

Answer 2

构建一个从顺序数字到范围为1到1M的随机ID值的查找表：

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)

这些操作大概需要在我的笔记本电脑上运行30秒左右。对于每个序列，您只需要执行一次。

现在，您已经得到了映射关系，请跟踪使用了多少个映射（可以在另一个表中使用计数器或自动增量键字段）。

- Martin

Answer 3

我曾经在这里苦苦挣扎，后来发现如果列中有NULL条目，则失败。我用以下代码重新制作了它;

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

这里有一个演示程序; http://sqlfiddle.com/#!2/620de1/1 希望对你有所帮助 :)

- HeavyHead

Answer 4

我的解决方案是在Cakephp 2.4.7中创建一个带有一个自增类型字段的表。

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

我随后创建了一个php函数，以便每次插入新记录时读取生成的id并立即删除它。Mysql保留其内存计数器状态。生成的所有数字都是唯一的，直到您重置mysql计数器或运行TRUNCATE TABLE操作。

以下是在Cakephp中创建的模型以实现所有内容。

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);
    }
}

可以通过操作获得的结果，在同一函数中实现对结果所属范围的任何检查。

- Granduca

Answer 5

我能想到的唯一比较合理的方法是创建一个有限ID池的表格，当使用这些ID时，将其从表格中删除。这些键可以是唯一的，可以创建一个脚本来生成该表格。然后，您可以通过从可用键中生成随机选择来获取其中一个键。我说“半路”合理，老实说这已经太慷慨了，但这比随机生成键直到创建唯一键要好吧。

- James

Answer 6

在你想要唯一随机数的字段上创建一个唯一索引。

然后运行以下命令： Update IGNORE Table Set RandomUniqueIntegerField=FLOOR(RAND() * 1000000);

- user3649739

Answer 7

RAND()函数会生成一个随机数，但是并不能保证其唯一性。在MySQL中处理唯一标识符的正确方式是使用AUTO_INCREMENT声明。

例如，下面表中的id字段不需要在插入时提供，并且它将始终增加1：

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

- Kaleb Brasee

---

谢谢，但我需要随机数（这在我的问题中），而不是自增。 - hippout

嗯，好的。不过，仅凭这些信息，这听起来像是个坏主意。 - Kaleb Brasee

Answer 8

我尝试使用这个答案，但它对我没有起作用，所以我不得不稍微更改原始查询。

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

- Ankur

Answer 9

我担心你为什么要这样做，但你可以简单地使用：

SELECT FLOOR(RAND() * 1000000);

查看完整的MySQL RAND文档以获取更多信息。

然而，我希望你不要将其用作唯一标识符（如果这是你想要的，请在适当的大型无符号整数字段上使用auto_increment属性），我不得不想知道为什么你要使用MySQL而不是脚本语言。你想要实现什么？

- John Parker