将UUID v4存储在MySQL中

Question

我正在使用PHP生成UUID，根据在这里找到的函数。

现在我想将其存储在MySQL数据库中。对于存储UUID v4，最好/最高效的MySQL 数据类型是什么？

我目前使用的是varchar(256)，但我相信这远远超过了必要的大小。我找到了很多几乎答案，但它们通常对所指的UUID形式含糊不清，所以我要求具体的格式。

- Stephen R

---

你不需要任何 uuid-anything。只需使用随机字节即可，可以将它们存储为十六进制或其他格式。http://php.net/manual/en/function.random-bytes.php - pvg

获取16个随机字节。以十六进制存储。就这样。登录完成后不要忘记从数据库中删除。哦，不要像那个答案建议的那样作为GET请求传递，因为那太疯狂了。 - pvg

MySQL 提供了 UUID() 函数，因此您无需使用 PHP 来生成 UUID。您可以删除横线并将十六进制数字保存为 binary(16)。如果您通过触发器执行此操作，则应使用 SELECT UNHEX(REPLACE(UUID(), '-', ''));，如果需要索引，则将其设置为 unique，从而获得利润。 - N.B.

UUID实际上没有任何意义。这就是我一直试图告诉你的 :) 你关心的只是令牌足够安全(16个random_bytes)和令牌无法被获取/重复(即使用SSL，使令牌失效)。其他部分不那么重要(甚至包括获取参数)。 - pvg

UUID是一个受信任的、定义明确的标准，即使在大规模环境下使用，也应该产生无冲突的标识符。它们是将不同信息源链接到一起所必需的标识符的顺序ID值的良好替代品。但是，它们不适用于生成安全令牌或密钥，因为它们的搜索空间显着小于纯随机字符串的搜索空间。也就是说，它们具有冲突抵抗能力，但搜索空间相对较窄。 - tadman

Answer 1

如果你希望完全匹配，请使用VARCHAR(36)，或者使用VARCHAR(255)，因为它们具有相同的存储成本。这里没有必要关注字节数。

请注意，VARCHAR字段是可变长度的，因此存储成本与其中实际包含的数据量成比例，而不是可能包含的数据量。

将其存储为BINARY非常麻烦，这些值无法打印，并且在运行查询时可能显示为垃圾。很少有理由使用字面二进制表示法。人可读的值可以复制粘贴并轻松处理。

其他一些平台（如Postgres）有一个正确的UUID列，它以更紧凑的格式存储，同时以人类可读的方式显示，因此可以同时使用这两种方法的最佳方式。

- tadman

---

考虑使用二进制(16)来节省存储空间... MySQL提供了函数，使将UUID（以二进制形式）存储变得非常简单，因此如果您只需要更新查询，则没有任何借口不这样做... UUID_TO_BIN BIN_TO_UUID IS_UUID例如：http://www.mysqltutorial.org/mysql-uuid/ - StratusBase LLC

让我感到困惑的是这怎么可能成为被接受的答案。MySQL仅为VARCHAR列的前4个字节创建索引，并且UUID中的ID表明它将被用于标识（因此也是搜索）。这个答案会导致性能灾难。正确的存储方式是使用BINARY(16)，或者更好的方法是使用支持UUID的现代数据库。 - vstoyanov

@vstoyanov 尽管MySQL不一定索引整个字符串，这可能会对长VARCHAR列上的唯一索引造成问题，但我的经验是它并没有仅索引前4个字节。是否有任何解释这一点的文档？这可能只是索引了一个字符，这对我来说毫无意义。早期版本的MySQL确实具有更有限的索引覆盖范围，但从5.7版本开始，这些限制已经大大增加。 - tadman

使用BINARY(16)相对于Varchar，搜索速度会更快吗？ - huggie

在大多数情况下，如果一个数据字段只是未索引的，那么其性能和存储成本几乎可以忽略不计。但如果它是一个被索引的字段，这就成为了更大的问题，如果被用作主键，那么对于写入操作来说差异就会很明显，详见 https://www.percona.com/blog/2019/11/22/uuids-are-popular-but-bad-for-performance-lets-discuss/。因此，这要视情况而定，我所看到的，OP并没有说明具体情况。 - Frank Hopkins

Answer 2

问题是关于在MySQL中存储UUID。

从mySQL 8.0版开始，您可以使用binary(16)以及通过UUID_TO_BIN/BIN_TO_UUID函数的自动转换： https://mysqlserverteam.com/mysql-8-0-uuid-support/

请注意，mySQL还有一种快速生成UUID作为主键的方法：

INSERT INTO t VALUES(UUID_TO_BIN(UUID(), true))

- Karsten R.

---

内置的MySQL UUID函数不是创建UUID v4而是v1吗？ - Stephen R

UUID_TO_BIN/BIN_TO_UUID适用于v4 UUID（与版本无关）。UUID()生成“小于”v1 UUID：https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_uuid - Karsten R.

我要指出的是，使用uuid v4作为主键是非常糟糕的想法！这个函数所做的转换实际上变得毫无意义。UUID v4的随机性会影响数据库的性能。 - Braden Rockwell Napier

@BradenRockwellNapier 这篇文章声称使用UUID作为键可以比整数键更快 - https://www.qcode.in/ready-to-use-uuid-in-your-next-laravel-app/ - digout

@digout - 我只看了几秒钟，但看起来他在使用的是uuidv1，这将非常好。不过我可能错了。对于Sequelize用户，我还提供了这个链接，它可以进行正确的格式化并允许使用Sequelize https://github.com/odo-network/sequelize-binary-uuid - Braden Rockwell Napier

@digout的链接最终指向这里--https://mysqlserverteam.com/storing-uuid-values-in-mysql-tables/ --对于因某种原因被困在MySQL 5.7上的人来说，这是非常有用的信息，因为它手动复制了UUID_TO_BIN和BIN_TO_UUID函数。 - Stephen R

Answer 3

如果每行都有UUID，您可以将其存储为CHAR(36)，与VARCHAR(36)相比，每行可节省1个字节。

uuid CHAR(36) CHARACTER SET ascii

与CHAR不同，VARCHAR的值以1字节或2字节的长度前缀加数据存储。长度前缀指示值中的字节数。如果值不超过255个字节，则列使用一个长度字节，如果值可能需要超过255个字节，则使用两个长度字节。 https://dev.mysql.com/doc/refman/5.7/en/char.html 但是要小心CHAR，即使字段为空，它也会始终消耗定义的完整长度。此外，请确保使用ASCII字符集，因为否则CHAR将为最坏情况（即在utf8中每个字符3个字节，在utf8mb4中4个字节）做计划。

[...] MySQL必须为CHAR CHARACTER SET utf8mb4列中的每个字符保留四个字节，因为这是最大可能的长度。例如，MySQL必须为CHAR（10）CHARACTER SET utf8mb4列保留40个字节。 https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html

- Mathieu Rey

Answer 4

最高效的肯定是BINARY(16)，存储可读字符使用的存储空间超过了两倍，意味着更大的索引和更慢的查找速度。

如果您的数据足够小，以至于将其存储为文本不会影响性能，那么您可能不需要使用UUID而可以使用普通的整数键。存储原始数据并不像其他人所说的那么痛苦，因为任何像样的数据库管理工具都会将八位字节显示/转储为十六进制，而不是“文本”的字面字节。您不应该手动在数据库中查找UUID；如果必须这样做，HEX()和x'deadbeef01'字面值将会是您的朋友。

在您的应用程序中编写一个函数（就像您引用的那个函数）来处理这个问题非常简单。您甚至可以在数据库中使用虚拟列和存储过程来完成，这样应用程序就不需要处理原始数据。

我建议将UUID生成逻辑与显示逻辑分开，以确保现有数据不会被更改，并且可以检测到错误。

function guidv4($prettify = false)
{
    static $native = function_exists('random_bytes');

    $data = $native ? random_bytes(16) : openssl_random_pseudo_bytes(16);
    $data[6] = chr(ord($data[6]) & 0x0f | 0x40); // set version to 0100
    $data[8] = chr(ord($data[8]) & 0x3f | 0x80); // set bits 6-7 to 10
    if ($prettify) {
        return guid_pretty($data);
    }
    return $data;
}

function guid_pretty($data)
{
    return strlen($data) == 16 ?
        vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($data), 4)) :
        false;
}

function guid_ugly($data)
{
    $data = preg_replace('/[^[:xdigit:]]+/', '', $data);
    return strlen($data) == 32 ? hex2bin($data) : false;
}

---

编辑

如果您只需要在读取数据库时使列变得漂亮，那么以下语句就足够了：

ALTER TABLE test 
ADD uuid_pretty CHAR(36) GENERATED ALWAYS AS (
    CONCAT_WS(
        '-', 
        LEFT(HEX(uuid_ugly), 8), 
        SUBSTR(HEX(uuid_ugly), 9, 4), 
        SUBSTR(HEX(uuid_ugly), 13, 4), 
        SUBSTR(HEX(uuid_ugly), 17, 4), 
        RIGHT(HEX(uuid_ugly), 12)
    )
) VIRTUAL;

- Walf

Answer 5

对我来说，在MySQL 8.0.26中这可行如魔法一般。

create table t (
    uuid BINARY(16) default (UUID_TO_BIN(UUID())),
)

查询时可使用以下方法

select BIN_TO_UUID(uuid) uuid from t;

结果如下：

# uuid
'8c45583a-0e1f-11ec-804d-005056219395'

- Bobin

---

MySQL的UUID()函数不会生成UUID v4，因此默认情况下无法使用。但存储看起来还不错。 - Stephen R

Answer 6

最省空间的做法是使用BINARY(16)或两个BIGINT UNSIGNED。

前者可能会让你头痛，因为手动查询不会直接给出可读/可复制的值。 后者可能会让你头痛，因为需要在一个值和两个列之间进行映射。

如果这是一个主键，我绝对不会浪费任何空间，因为它也会成为每个二级索引的一部分。换句话说，我会选择这些类型中的一个。

为了性能考虑，随机UUID的随机性（即UUID v4，即随机生成）会严重影响效率。当UUID是您的主键，或者您需要频繁进行范围查询时，就会出现这种情况。将数据插入到主索引中时，它们将出现在各处，而不是全部在（或附近）。您的数据失去了时间局部性，在各种情况下都是有帮助的属性。

我的主要改进是使用类似于UUID v1的东西，它使用时间戳作为其数据的一部分，并确保时间戳位于最高位。例如，UUID可能类似于这样组成：

Timestamp | Machine Identifier | Counter

这样，我们就可以获得类似于自增值的本地性。

- Timo

---

在C#中，如果需要一种具有增量属性的UUID替代方案（但仍保留大部分UUID的属性），可以使用DistributedId。这些ID非常高效，可用作数据库键。实际上，它们甚至可以存储为DECIMAL(28, 0)或CHAR(16)（确保使用二进制排序规则和ASCII字符集）。 - Timo

Answer 7

这将非常有用，如果您使用二进制（16）数据类型：

INSERT INTO table (UUID) VALUES
   (UNHEX(REPLACE(UUID(), "-","")))

- B.Habibzadeh

Answer 8

我刚刚发现了一篇深入探讨这些主题的好文章：https://www.xaprb.com/blog/2009/02/12/5-ways-to-make-hexadecimal-identifiers-perform-better-on-mysql/ 它涵盖了值的存储，其中包括已在此页面上不同答案中表达的相同选项：

- 一：注意字符集 - 二：使用固定长度、非空值 - 三：将其设为BINARY

但还提供了有关索引的一些有趣见解：

- 四：使用前缀索引

在许多情况下，您不需要索引完整的值。我通常发现前8到10个字符是唯一的。如果它是二级索引，这通常已经足够了。这种方法的好处是，您可以将其应用于现有应用程序，而无需修改列为BINARY或其他任何内容 - 它仅是索引更改，不需要应用程序或查询进行更改。

请注意，本文未告诉您如何创建此“前缀”索引。查看MySQL Column Indexes 文档，我们可以找到：

[...] 您可以创建一个仅使用列的前N个字符的索引。以这种方式仅对列值的前缀进行索引可以使索引文件更小。当您索引BLOB或TEXT列时，必须为索引指定前缀长度。例如：

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

[...] 在CREATE TABLE、ALTER TABLE和CREATE INDEX语句中的前缀长度被解释为非二进制字符串类型（CHAR、VARCHAR、TEXT）的字符数和二进制字符串类型（BINARY、VARBINARY、BLOB）的字节数。

五：构建哈希索引

你可以做的是生成值和索引的校验和并对其进行索引。没错，就是一个哈希的哈希。在大多数情况下，CRC32()效果很好（如果不行，您可以使用64位哈希函数）。创建另一列。[...] CRC列不能保证唯一性，因此您需要在WHERE子句中同时满足这两个条件，否则该技术将无法工作。哈希冲突很快发生；您可能会在大约100,000个值时发生冲突，这比您想象的要早得多 - 不要认为32位哈希意味着您可以在表中放置40亿行而不会发生冲突。

- Mathieu Rey

---

在某些情况下，“前缀”索引会让事情变得更糟。 - Rick James

Answer 9

这是一个相当古老的帖子，但仍然相关，并经常出现在搜索结果中，因此我将添加我的答案。由于您已经必须在查询中使用触发器或自己的UUID()调用，因此这里有一对函数，我使用它们将UUID保留为文本以便在数据库中轻松查看，但将其占用空间从36减少到24个字符。（节省33%）

delimiter //

DROP FUNCTION IF EXISTS `base64_uuid`//
DROP FUNCTION IF EXISTS `uuid_from_base64`//


CREATE definer='root'@'localhost' FUNCTION base64_uuid() RETURNS varchar(24)
DETERMINISTIC
BEGIN
    /* converting INTO base 64 is easy, just turn the uuid into binary and base64 encode */
    return to_base64(unhex(replace(uuid(),'-','')));
END//

CREATE definer='root'@'localhost' FUNCTION uuid_from_base64(base64_uuid varchar(24)) RETURNS varchar(36)
DETERMINISTIC
BEGIN
    /* Getting the uuid back from the base 64 version requires a little more work as we need to put the dashes back */
    set @hex = hex(from_base64(base64_uuid));
    return lower(concat(substring(@hex,1,8),'-',substring(@hex,9,4),'-',substring(@hex,13,4),'-',substring(@hex,17,4),'-',substring(@hex,-12)));
END//

- Glenn J. Schworak