在 MySQL 中存储 UUID v4

小开

最佳答案

如果你正在寻找一个精确的适合存储它作为 VARCHAR(36)，或者 VARCHAR(255)将工作在相同的存储成本无论如何。这里没有理由对字节大惊小怪。

请记住，VARCHAR字段是 可变长度，因此存储成本与其中实际存在的数据量成正比，而不是与其中可能存在的数据量成正比。

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

其他一些平台，比如 Postgres，有一个适当的 UUID 列，它在内部以更紧凑的格式存储它，但是以人类可读的方式显示它，所以这两种方法都是最好的。

小开

如果每行始终有一个 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 中的每个字符保留四个字节字符集 utf8mb4列，因为这是最大可能值例如，MySQL 必须为 CHAR (10)保留40个字节字符集 utf8mb4列。 Https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html

小开

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

自从8.0版本的 mySQL 以来，你可以通过 UUID_TO_BIN/BIN_TO_UUID函数自动转换使用 binary(16): Https://mysqlserverteam.com/mysql-8-0-uuid-support/

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

插入 t 值(UUID _ TO _ BIN (UUID () ，true))

小开

最有效的当然是 BINARY(16)，存储人类可读的字符使用超过两倍的存储空间，这意味着更大的索引和更慢的查找。如果您的数据足够小，以文本形式存储数据不会影响性能，那么您可能不需要比单调的整数键更多的 UUID。存储原始数据真的不像其他人建议的那样痛苦，因为任何像样的 db 管理工具都会将八进制字节显示/转储为十六进制，而不是“ text”的文字字节。您不应该需要在数据库中手动查找 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;

小开

最节省空间的是 BINARY(16)或两个 BIGINT UNSIGNED。

前者可能会让您头疼，因为手动查询不能(以直接的方式)提供可读/可复制的值。后者可能会让您头疼，因为必须在一个值和两个列之间进行映射。

如果这是一个主键，我肯定不会在它上面浪费任何空间，因为它也成为每个辅助索引的一部分。换句话说，我会选择这些类型之一。

就性能而言，随机 UUID (即随机化的 UUID v4)的随机性将造成严重损害。如果 UUID 是您的主键，或者您对它进行了大量的范围查询，那么就会出现这种情况。插入到主索引中的内容将遍布所有位置，而不是全部位于(或接近)末尾。您的数据丢失了时间位置，这在各种情况下都是一个有用的属性。

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

Timestamp | Machine Identifier | Counter

这样，我们得到一个类似于自动增量值的局部性。

小开

我刚刚发现了一篇很好的文章，更深入地讨论了这些主题: https://www.xaprb.com/blog/2009/02/12/5-ways-to-make-hexadecimal-identifiers-perform-better-on-mysql/

它涵盖了值的存储，在本页的不同答案中已经表达了相同的选项:

一: 小心字符集
二: 使用固定长度、不可为空的值
三: 二进制

但也增加了一些关于指数的有趣见解:

四: 使用前缀索引

在许多情况下(但并非所有情况下) ，不需要索引我通常发现前8到10个字符是如果它是一个次要索引，这通常是足够好的这种方法的美妙之处在于你可以把它应用到现有的应用程序无需将列修改为 BINARY 或其他任何内容ーー它是仅索引更改，不需要应用程序或要更改的查询。

注意，本文没有告诉您如何创建这样的“前缀”索引。查看列索引的 MySQL 文档，我们发现:

[ ... ]可以创建一个索引，该索引只使用仅以这种方式索引列值的前缀可以使当你索引一个 BLOB 或者 TEXT 列的时候，你需要必须为索引指定前缀长度。例如:

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

[ ... ]中的前缀长度将解释 CREATETABLE、 ALTERTABLE 和 CREATEINDEX 语句作为非二进制字符串类型的字符数(CHAR，VARCHAR, 和二进制字符串类型的字节数(二进制字符串类型，VARBINARY, BLOB).

五: 构建散列索引

您可以做的是生成一个值的校验和，并对。是的，一个杂乱无章的杂乱无章。对于大多数情况，CRC32()工作得很好 Well (如果没有，可以使用64位散列函数) [ ... ] CRC 列不一定是唯一的，所以需要 WHERE 子句中的两个条件，否则这种技术将不起作用。散列冲突发生得很快; 您可能会遇到大约10万，这比你想象的要快得多ーー不要假设一个32位哈希表示您可以在您的在碰撞之前把桌子放好。

小开

如果您使用二进制(16)数据类型，这将非常有用:

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

小开

在 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'

小开

这是一个相当老的职位，但仍然相关，并出现在搜索结果往往，所以我会添加我的答案的组合。因为您已经必须在查询中使用触发器或自己对 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//