生成11,000,000个唯一ID的最快方法 [英] Fastest way to generate 11,000,000 unique ids

问题描述

我正在尝试创建php脚本，以按顺序生成11,000,000百万个唯一ID.但是，我正在尝试在20分钟内非常快地完成操作，它应该会生成这1100万个唯一ID.同样，一旦达到12,000,000，它应该回绕并从零开始.

I am trying to create php script that generates 11,000,000 million unique ids in sequential order. However, I am trying to do it very quickly within 20 min it should generate these 11 million unique ids. Also, once it reaches 12,000,000 it should wrap around and start back at zero.

这是我到目前为止所拥有的.该脚本一次只会返回一个ID.我刚刚添加了一个循环，以查看生成ID所需的时间.

Here is what I have so far. This script would would only return one id at a time. I just added a loop to see how long it would take to generate the ids.

while(true){

    try {

        $this->getAdapter()->query('INSERT INTO generate_ids (assigned_id) SELECT (MAX(assigned_id)+1) FROM generate_ids');
        $id = $this->getAdapter()->lastInsertId();


        $sql = 'SELECT assigned_id FROM generate_ids WHERE id = $id';  
        $query = $this->getAdapter()->query($sql);
        $result = $query->fetchAll();
            //Live system would return id here
        $assigned_id = $result[0]['assigned_id'];


    } catch (Exception $e) {
        //do nothing
    }

    if($count == 11000000){
        die();
    }

    $count++;
}

}

推荐答案

如果创建下表:

 CREATE TABLE sequence (
     sequence_id BIGINT NOT NULL AUTO_INCREMENT,
     PRIMARY KEY (`sequence_id`)
) 

然后依次发出这三个查询:

Then issue these three queries one after the other:

INSERT INTO sequence () VALUES ();
DELETE FROM sequence WHERE sequence_id < LAST_INSERT_ID();
SELECT LAST_INSERT_ID() AS sequence;

第三个查询得到保证，它们返回唯一的序列号.即使您有数十个不同的客户端程序连接到数据库，该保证仍然有效.这就是AUTO_INCREMENT的优点.

The third query is guaranteed to return a unique sequence number. This guarantee holds even if you have dozens of different client programs connected to your database. That's the beauty of AUTO_INCREMENT.

您不仅可以预先生成一千一百万个序列号，还可以使用这些SQL查询在需要时获取唯一的序列号.

Instead of just generating eleven million of these sequence numbers up front, you can use these SQL queries to get a unique sequence number whenever you need it.

如果必须以1200万的顺序回绕，则可以改用这些查询.

If you must wrap around at sequence number 12 million you can use these queries instead.

INSERT INTO sequence () VALUES ();
DELETE FROM sequence WHERE sequence_id < LAST_INSERT_ID();
SELECT LAST_INSERT_ID() MOD 12000000 AS sequence;

这里的窍门是使用自动递增的序列号来确保唯一性，同时还要删除表中的行，以免占用大量空间.

The trick here is to use an auto-increment sequence number for uniqueness, but to also delete the rows in the table so it doesn't gobble up lots of space.

请注意，您也可以将LAST_INSERT_ID()的序列号用于其他目的，例如.

Note that you can also use the sequence number of LAST_INSERT_ID() for other purposes, like so for example.

INSERT INTO sequence () VALUES ();
DELETE FROM sequence WHERE sequence_id < LAST_INSERT_ID();
INSERT INTO user (userid, username, phone) 
          VALUES (LAST_INSERT_ID() MOD 12000000, 'Joe', '800-555-1212');
SELECT LAST_INSERT_ID() MOD 12000000 AS sequence;

这篇关于生成11,000,000个唯一ID的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！