MySQL mysql唯一编号生成
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4382185/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql unique number generation
提问by hippout
I want to generate a unique random integer (from 10000 to 99999) identity just by clean mySQL; any ideas?
我想通过干净的 mySQL 生成一个唯一的随机整数（从 10000 到 99999）身份；有任何想法吗？
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.
我不想通过循环在 php 中生成这个数字（生成数字 - > 在数据库中检查它），因为我想在 mySQL 查询中使用一些智能解决方案。
回答by unclenorton
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.
简单地说，它生成 N 个随机数，其中 N 是表行的计数，过滤掉表中已经存在的那些，并将剩余的集合限制为 1。
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.
在大桌子上它可能会有点慢。为了加快速度，您可以从这些唯一的 id 创建一个视图，并使用它代替嵌套的 select 语句。
EDIT: removed quotes
编辑：删除引号
回答by Martin
Build a look-up table from sequential numbers to randomised id values in range 1 to 1M:
构建一个从序列号到 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)
(This all takes about 30 seconds to run on my laptop. You would only need to do this once for each sequence.)
（这一切在我的笔记本电脑上运行大约需要 30 秒。您只需为每个序列执行一次。）
Now you have the mapping, just keep track of how many have been used (a counter or auto_increment key field in another table).
现在您有了映射，只需跟踪已使用的数量（另一个表中的计数器或 auto_increment 键字段）。
回答by HeavyHead
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;
我在这里解决了一段时间，然后意识到如果列有 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
Fiddle here; http://sqlfiddle.com/#!2/620de1/1
在这里小提琴; http://sqlfiddle.com/#!2/620de1/1
Hope its helpful :)
希望它有帮助:)
回答by James
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.
我能想出的唯一合理的想法是创建一个具有有限 ID 池的表，并在使用它们时将它们从该表中删除。这些键可以是唯一的，并且可以创建一个脚本来生成该表。然后，您可以通过从可用密钥中生成随机选择来提取其中一个密钥。我说“半途而废”是合理的，老实说这是一种慷慨的方式，但它胜过随机生成密钥，直到你创建一个我认为的唯一密钥。
回答by Granduca
My solution, implemented in Cakephp 2.4.7, is to create a table with one auto_incremental type field
我在 Cakephp 2.4.7 中实现的解决方案是创建一个带有一个 auto_incremental 类型字段的表
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
然后我创建了一个 php 函数，以便每次插入新记录时，读取生成的 id 并立即删除它。Mysql 保持其内存计数器状态。生成的所有数字都是唯一的，直到您重置 mysql 计数器或运行 TRUNCATE TABLE 操作
find below the Model created in Cakephp to implement all
下面找到在 Cakephp 中创建的 Model 来实现所有
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
通过操作获得的结果，可以在同一函数中实现对结果所属范围的任何检查
回答by Kaleb Brasee
The RAND() function will generate a random number, but will notguarantee uniqueness. The proper way to handle unique identifiers in MySQL is to declare them using AUTO_INCREMENT.
RAND() 函数将生成一个随机数，但不保证唯一性。在 MySQL 中处理唯一标识符的正确方法是使用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:
例如，在插入时不需要提供下表中的 id 字段，它总是加 1：
CREATE TABLE animal (
id INT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
回答by Ankur
回答by user3649739
Create a unique index on the field you want the unique random #
在您想要唯一随机的字段上创建唯一索引#
Then run
然后运行
Update IGNORE Table
Set RandomUniqueIntegerField=FLOOR(RAND() * 1000000);
Update IGNORE Table
Set RandomUniqueIntegerField=FLOOR(RAND() * 1000000);
回答by John Parker
I worry about whyyou want to do this, but you could simply use:
我担心你为什么要这样做，但你可以简单地使用：
SELECT FLOOR(RAND() * 1000000);
See the full MySQL RAND documentationfor more information.
有关更多信息，请参阅完整的MySQL RAND 文档。
However, I hope you're not using this as a unique identifier (use an auto_increment attributeon 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?
但是，我希望您不要使用它作为唯一标识符（如果这是您所追求的，请在适当大的无符号整数字段上使用auto_increment 属性），我想知道为什么您会为此使用 MySQL 而不是一种脚本语言。你想达到什么目的？
