MySQL 如何为表的主键创建唯一的随机整数 ID？

How to to create unique random integer ID for primary key for table?

提问by MindGame

I was wondering if anybody knew a good way to create a unique random integer id for a primary key for a table. I'm using MySQL. The value has to be integer.

我想知道是否有人知道为表的主键创建唯一随机整数 id 的好方法。我正在使用 MySQL。该值必须是整数。

采纳答案by byte_slave

If your're open to suggestions and you can implement it, use UUIDs. MySQL's UUID()function will return a 36 chars value which can be used for ID.

如果您愿意接受建议并且可以实施它，请使用 UUID。MySQL 的UUID()函数将返回一个 36 个字符的值，可用于ID.

If you want to use integer, still, I think you need to create a function getRandID()that you will use in the INSERTstatement. This function needs to use random + check of existing ids to return one that is not used before.

如果您想使用整数，我认为您仍然需要创建一个getRandID()将在INSERT语句中使用的函数。这个函数需要使用随机+检查现有的id来返回一个以前没有使用过的。

Check RAND()function for MySQL.

检查RAND()MySQL 的功能。

回答by Gary Green

In response to:"Because I want to use that value to Encode to Base62 and then use that for an id in a url. If i auto increment, it might be obvious to the user how the url id is generated."

回应：“因为我想使用该值将其编码为 Base62，然后将其用于 url 中的 id。如果我自动递增，用户可能很清楚 url id 是如何生成的。”

If security is your aim then using Base62, even with a "randomly" generated number won't help.

如果安全是您的目标，那么使用 Base62，即使使用“随机”生成的数字也无济于事。

A better option would:

更好的选择是：

• Do not re-invent the wheel -- use AUTO_INCREMENT
• Then use a cryptographic hash function + a randomly generated string (hidden in the db for that particular url) to generate the final "unique id for that url"

• 不要重新发明轮子——使用 AUTO_INCREMENT
• 然后使用加密散列函数 + 随机生成的字符串（隐藏在该特定 url 的数据库中）生成最终的“该 url 的唯一 id”

回答by Rob Beer

How you generate the unique_ids is a useful question - but you seem to be making a counter productive assumption about whenyou generate them!

您如何生成 unique_ids 是一个有用的问题 - 但您似乎对何时生成它们做出了适得其反的假设！

My point is that you do not need to generate these unique id's at the time of creating your rows, because they are essentially independent of the data being inserted.

我的观点是，您不需要在创建行时生成这些唯一 ID，因为它们本质上与插入的数据无关。

What I do is pre-generate unique id's for future use, that way I can take my own sweet time and absolutely guarantee they are unique, and there's no processing to be done at the time of the insert.

我所做的是预先生成唯一的 id 以备将来使用，这样我就可以享受自己的甜蜜时光并绝对保证它们是唯一的，并且在插入时无需进行任何处理。

For example I have an orders table with order_id in it. This id is generated on the fly when the user enters the order, incrementally 1,2,3 etc forever. The user does not need to see this internal id.

例如，我有一个带有 order_id 的订单表。这个 id 是在用户输入订单时即时生成的，永远递增 1,2,3 等。用户不需要看到这个内部 ID。

Then I have another table - unique_ids with (order_id, unique_id). I have a routine that runs every night which pre-loads this table with enough unique_id rows to more than cover the orders that might be inserted in the next 24 hours. (If I ever get 10000 orders in one day I'll have a problem - but that would be a good problem to have!)

然后我有另一个表 - unique_ids with (order_id, unique_id)。我有一个每天晚上运行的例程，它使用足够的 unique_id 行预加载此表，以涵盖可能在接下来的 24 小时内插入的订单。（如果我在一天内收到 10000 个订单，我就会遇到问题——但这将是一个很好的问题！）

This approach guarantees uniqueness and takes any processing load away from the insert transaction and into the batch routine, where it does not affect the user.

这种方法保证了唯一性，并将任何处理负载从插入事务转移到批处理例程中，在那里它不会影响用户。

回答by TheFrost

How about this approach (PHPand MySQL):

这种方法（PHP和MySQL）如何：

---

---

Short

短的

1. Generate random numberfor user_id (UNIQUE)
2. Insert row with generated numberas user_id
3. If inserted row count equal to 0, go to point 1

1. 生成随机number的user_id (UNIQUE)
2. 插入生number成为的行user_id
3. 如果插入的行数等于 0，则转到点 1

Looks heavy? Continue to read.

看起来很重？继续阅读。

---

---

Long:

长：

Table:

桌子：

users (user_id int UNIQUE)

Code:

代码：

<?php
// values stored in configuration
$min = 1;
$max = 1000000;

$numberOfLoops = 0;
do {
    $randomNumber = rand($min, $max);

    // the very insert
    $insertedRows = insert_to_table(
        'INSERT INTO foo_table (user_id) VALUES (:number)', 
        array(
            ':number' => $randomNumber
        ));

    $numberOfLoops++;

    // the magic
    if (!isset($reported) && $numberOfLoops / 10 > 0.5) {
        /**
         * We can assume that at least 50% of numbers
         * are already in use, so increment values of
         * $min and $max in configuration.
         */
        report_this_fact();
        $reported = true;
} while ($insertedRows < 1);

---

---

1. All values ($min, $max, 0.5) are just for explanation and they have no statistical meaning.
2. Functions insert_to_tableand report_this_factare not build in PHP. The are also as numbers just for clarify of explanation purposes.

1. 所有值 ( $min, $max, 0.5) 仅用于解释，没有统计意义。
2. 函数insert_to_table和report_this_fact不是在 PHP 中构建的。这些也作为数字只是为了说明目的。

回答by lbsweek

my way, for both 32bit and 64bit platform. result is 64bit

我的方式，适用于 32 位和 64 位平台。结果是 64 位

function hexstr2decstr($hexstr){
    $bigint = gmp_init($hexstr, 16);
    $bigint_string = gmp_strval($bigint);
    return $bigint_string;
}

function generate_64bitid(){
    return substr(md5(uniqid(rand(), true)), 16, 16);
}

function dbGetUniqueXXXId(){
    for($i = 0; $i < 10; $i++){
        $decstr = hexstr2decstr(generate_64bitid());

        //check duplicate for mysql.tablexxx
        if($dup == false){
            return $decstr;
        }
    }
    return false;
}

回答by Const Mi

You can use an AUTO_INCREMENTfor your table, but give the users the encrypted version:

您可以AUTO_INCREMENT为您的表使用 an ，但为用户提供加密版本：

encrypted_id: SELECT HEX(AES_ENCRYPT(id, 'my-private-key'));

加密 ID： SELECT HEX(AES_ENCRYPT(id, 'my-private-key'));

id: SELECT AES_DECRYPT(UNHEX(encrypted_id), 'my-private-key');

ID： SELECT AES_DECRYPT(UNHEX(encrypted_id), 'my-private-key');

回答by drew

There is an AUTO_INCREMENTfeature. I would use that.

有一个AUTO_INCREMENT特点。我会用那个。

See heremore examples.

见这里更多的例子。

回答by Webnet

AUTO_INCREMENTis going to be your best bet for this.

AUTO_INCREMENT将是你最好的选择。

Hereare some examples.

这里有一些例子。

If you need to you can adjust where the increment value starts (by default it's 1).

如果需要，您可以调整增量值的开始位置（默认为 1）。