我应该使用varchar（36），还是有更好的方法？
10个回答
120
我的数据库管理员问我，当我询问关于为我的对象存储GUID的最佳方式时，为什么需要存储16个字节而不是使用整数的4个字节就可以完成相同的操作。由于他向我提出了这个挑战，我觉得现在是一个好时机来提一下这个问题。话虽如此...
如果你想要充分利用存储空间，可以将GUID以CHAR（16）二进制形式存储。
如果你想要充分利用存储空间，可以将GUID以CHAR（16）二进制形式存储。
- thaBadDawg
12
191因为使用16个字节，你可以在不同的数据库、不同的机器上、不同的时间生成数据，并将这些数据无缝地合并在一起 :) - Billy ONeal
5需要回复，什么是 char 16 二进制？不是字符？不是二进制？我在任何 MySQL GUI 工具中都没有看到这种类型，也没有在 MySQL 网站上找到任何文档。@BillyONeal - nawfal
3@nawfal：Char是一种数据类型。BINARY是用于该类型的类型说明符。它唯一的影响是修改MySQL如何进行排序。请参阅http://dev.mysql.com/doc/refman/5.0/en/charset-binary-op.html了解更多详细信息。当然，如果您的数据库编辑工具允许，您也可以直接使用BINARY类型。 （旧版工具不知道二进制数据类型，但知道二进制列标志） - Billy ONeal
2一个CHAR类型的字段和一个BINARY类型的字段本质上是一样的。如果你想将其简单化，那么CHAR类型的二进制字段期望在0到255范围内存储数值，并且使用查找表（大多数情况下是UTF8）来表示该值。而BINARY类型的字段期望相同类型的数值，但不会使用查找表来表示数据。我在4.x版本的时候使用了CHAR(16)类型，因为那时MySQL的性能没有现在好。 - thaBadDawg
20使用GUID比使用自动递增值有几个好处。Jeff Atwood在他的博客中列出了这些原因。对我来说，使用GUID的最大优点是我的应用程序不需要数据库往返才能知道实体的主键：我可以通过编程方式填充它，而如果我使用自动递增字段，我就无法做到这一点。这防止了我遇到一些麻烦：使用GUID，我可以以相同的方式处理实体，无论实体是否已经被持久化或者是全新的。 - Arialdo Martini
显示剩余7条评论
57
我会将其存储为 char(36)。
- Brian Fisher
6
6我不明白为什么你要存储“-”。 - Afshin Mehrabani
2@AfshinMehrabani 这很简单，直接明了，易于阅读。当然，这并非必须，但如果存储额外的字节不会造成影响，那么这是最佳解决方案。 - user1717828
2存储破折号可能不是一个好主意，因为它会导致更多的开销。如果您想使其易读，请让应用程序在读取时带上破折号。 - Lucca Ferri
@AfshinMehrabani 另一个考虑因素是从数据库中解析它。大多数实现都希望在有效的 GUID 中包含破折号。 - Ryan Gates
你可以在提取时插入连字符，轻松地将char(32)转换为char(36)。使用mySql的Insert函数。 - joedotnot
1@joedotnot 这是一个权衡，取决于你的数据将如何使用。带破折号格式化GUID并不是免费的，它会为每个需要它的查询添加CPU周期。将它们包含在数据库中将使数据库本身膨胀，并为您的查询添加额外的CPU周期。如果您的数据将被频繁写入但很少读取，则将其存储而不带破折号，并根据需要进行格式化。如果您的数据将有大量查询运行，则最好将数据格式化为带破折号的形式进行存储。 - Nick Fotopoulos
33
在ThaBadDawg的回答基础上，使用以下方便的函数（感谢我的一位更有经验的同事）将长度为36的字符串转换为16字节的字节数组。
DELIMITER $$
CREATE FUNCTION `GuidToBinary`(
$Data VARCHAR(36)
) RETURNS binary(16)
DETERMINISTIC
NO SQL
BEGIN
DECLARE $Result BINARY(16) DEFAULT NULL;
IF $Data IS NOT NULL THEN
SET $Data = REPLACE($Data,'-','');
SET $Result =
CONCAT( UNHEX(SUBSTRING($Data,7,2)), UNHEX(SUBSTRING($Data,5,2)),
UNHEX(SUBSTRING($Data,3,2)), UNHEX(SUBSTRING($Data,1,2)),
UNHEX(SUBSTRING($Data,11,2)),UNHEX(SUBSTRING($Data,9,2)),
UNHEX(SUBSTRING($Data,15,2)),UNHEX(SUBSTRING($Data,13,2)),
UNHEX(SUBSTRING($Data,17,16)));
END IF;
RETURN $Result;
END
$$
CREATE FUNCTION `ToGuid`(
$Data BINARY(16)
) RETURNS char(36) CHARSET utf8
DETERMINISTIC
NO SQL
BEGIN
DECLARE $Result CHAR(36) DEFAULT NULL;
IF $Data IS NOT NULL THEN
SET $Result =
CONCAT(
HEX(SUBSTRING($Data,4,1)), HEX(SUBSTRING($Data,3,1)),
HEX(SUBSTRING($Data,2,1)), HEX(SUBSTRING($Data,1,1)), '-',
HEX(SUBSTRING($Data,6,1)), HEX(SUBSTRING($Data,5,1)), '-',
HEX(SUBSTRING($Data,8,1)), HEX(SUBSTRING($Data,7,1)), '-',
HEX(SUBSTRING($Data,9,2)), '-', HEX(SUBSTRING($Data,11,6)));
END IF;
RETURN $Result;
END
$$
CHAR(16)实际上是一个BINARY(16)，选择你喜欢的类型即可。
为了更好地跟踪代码，请以数字顺序排列的 GUID 示例为例。(这里使用非法字符只是为了说明 - 每个位置都有一个唯一的字符。) 函数将转换字节顺序以实现更好的索引聚集。重新排序后的 GUID 如下所示:
12345678-9ABC-DEFG-HIJK-LMNOPQRSTUVW
78563412-BC9A-FGDE-HIJK-LMNOPQRSTUVW
去掉破折号后：
123456789ABCDEFGHIJKLMNOPQRSTUVW
78563412BC9AFGDEHIJKLMNOPQRSTUVW
- KCD
7
以下是不删除字符串中连字符的GuidToBinary函数代码：CREATE FUNCTION
GuidToBinary($guid char(36)) RETURNS binary(16)
RETURN CONCAT(
UNHEX(SUBSTRING($guid, 7, 2)),
UNHEX(SUBSTRING($guid, 5, 2)),
UNHEX(SUBSTRING($guid, 3, 2)),
UNHEX(SUBSTRING($guid, 1, 2)),
UNHEX(SUBSTRING($guid, 12, 2)),
UNHEX(SUBSTRING($guid, 10, 2)),
UNHEX(SUBSTRING($guid, 17, 2)),
UNHEX(SUBSTRING($guid, 15, 2)),
UNHEX(SUBSTRING($guid, 20, 4)),
UNHEX(SUBSTRING($guid, 25, 12))); - Jonathan Oliver5对于好奇的人来说，这些函数优于仅使用UNHEX(REPLACE(UUID(),'-',''))，因为它们按一定顺序排列比特，可以在聚集索引中表现更好。 - Slashterix
当我使用这个代码时，我的GUID被改变了。我尝试过使用unhex(replace(string, '-', ''))和上面的函数来插入它，但是当我使用同样的方法将它们转换回来时，所选择的GUID并不是插入的那个。是什么在改变GUID？我所做的一切都只是从上面复制代码。 - Misbit
1哇，太啰嗦了。我还是坚持使用UNHEX（REPLACE（UUID（），' - ',''）和嵌套插入函数insert（
insert（
insert（
insert（HEX（MyBin16Col），9,0，'-'),
14,0,'-'),
19,0,'-'),
24,0,'-') - joedotnot
显示剩余2条评论
28
char(36)是一个不错的选择。此外，可以使用MySQL的UUID()函数，该函数返回一个36个字符的文本格式（带有连字符的十六进制），可用于从数据库中检索此类ID。
- Learning
25
"更好"取决于你所优化的方面。
你对存储大小/性能和开发易用性有多在意？更重要的是，请问您是否生成了足够多的GUID或者频繁地获取它们，这会产生影响吗？
如果答案是“没有”，那么char(36)已经足够好了，并且它可以使存储/获取GUID变得非常简单。否则，binary(16)也是合理的选择，但您需要依靠MySQL和/或您选择的编程语言来进行字符串表示形式与二进制之间的相互转换。
- candu
2
3如果您托管软件（例如网页），而不在客户端销售/安装，您可以始终从char(36)开始进行简单开发，随着系统的使用增长并需要优化，可以逐渐缩小格式。 - Xavi Montero
1更大的char(36)最大的缺点是索引所占用的空间会更多。如果你的数据库中有大量的记录，那么索引的大小将会增加一倍。 - bpeikes
8
使用二进制(16)会更好，比使用varchar(32)更好。
- Onkar Janwa
7
KCD发布的GuidToBinary例程应该进行调整，以考虑GUID字符串中时间戳的位布局。如果该字符串表示版本1 UUID，例如由uuid() mysql例程返回的那些，则时间组件嵌入在字母1-G中，不包括D。
12345678-9ABC-DEFG-HIJK-LMNOPQRSTUVW
12345678 = least significant 4 bytes of the timestamp in big endian order
9ABC = middle 2 timestamp bytes in big endian
D = 1 to signify a version 1 UUID
EFG = most significant 12 bits of the timestamp in big endian
当你将其转换为二进制时，最好的索引顺序是：EFG9ABC12345678D + 其余部分。
你不想将12345678交换到78563412，因为大端已经产生了最好的二进制索引字节顺序。然而，您确实希望将最高有效字节移动到较低字节的前面。因此，EFG首先，然后是中间位和较低位。使用uuid()在一分钟内生成十几个UUID，您应该看到这个顺序可以产生正确的顺序。
select uuid(), 0
union
select uuid(), sleep(.001)
union
select uuid(), sleep(.010)
union
select uuid(), sleep(.100)
union
select uuid(), sleep(1)
union
select uuid(), sleep(10)
union
select uuid(), 0;
/* output */
6eec5eb6-9755-11e4-b981-feb7b39d48d6
6eec5f10-9755-11e4-b981-feb7b39d48d6
6eec8ddc-9755-11e4-b981-feb7b39d48d6
6eee30d0-9755-11e4-b981-feb7b39d48d6
6efda038-9755-11e4-b981-feb7b39d48d6
6f9641bf-9755-11e4-b981-feb7b39d48d6
758c3e3e-9755-11e4-b981-feb7b39d48d6
前两个UUID在时间上最接近。它们仅在第一个块的最后3个半字节中有所不同。这些是时间戳的最低有效位，这意味着在将其转换为可索引的字节数组时，我们希望将它们向右移动。例如，最后一个ID是最新的，但KCD的交换算法会将其放在第三个ID之前（从第一个块中的最后几个字节开始，3e在dc之前）。
索引的正确顺序应该是：
1e497556eec5eb6...
1e497556eec5f10...
1e497556eec8ddc...
1e497556eee30d0...
1e497556efda038...
1e497556f9641bf...
1e49755758c3e3e...
请参阅以下文章获取支持信息：http://mysql.rjweb.org/doc.php/uuid 请注意，我不会将版本 nibble 与时间戳的高 12 位拆分开来。这是您示例中的 D nibble。我只是将其放在前面。因此，我的二进制序列最终变成 DEFG9ABC 等等。这意味着所有索引的 UUID 都以相同的 nibble 开头。文章也是这样做的。
- bigh_29
2
1这是为了节省存储空间？还是为了使它们可以排序？ - MD004
1@MD004。它可以创建更好的排序索引。空格保持不变。 - bigh_29
5
- SleepyCal
3
我之前读过那篇文章。我觉得它非常有趣，但是如果我们想按二进制ID进行过滤，应该如何执行查询呢？我猜我们需要再次转换为十六进制，然后应用条件。这样很费力吗？为什么要存储二进制(16)（当然比varchar(36)好）而不是8字节的bigint？ - Maximus Decimus
3MariaDB有一篇更新的文章可以回答您的问题https://mariadb.com/kb/en/mariadb/guiduuid-performance/。 - SleepyCal
就此而言，UUIDv4是完全随机的，不需要分块。 - Mahmoud Al-Qudsi
2
我建议使用以下函数，因为@bigh_29提到的函数将我的GUID转换为了新的GUID（原因我不理解）。另外，在我对表格进行的测试中，这些函数稍微快一点。 https://gist.github.com/damienb/159151
DELIMITER |
CREATE FUNCTION uuid_from_bin(b BINARY(16))
RETURNS CHAR(36) DETERMINISTIC
BEGIN
DECLARE hex CHAR(32);
SET hex = HEX(b);
RETURN LOWER(CONCAT(LEFT(hex, 8), '-', MID(hex, 9,4), '-', MID(hex, 13,4), '-', MID(hex, 17,4), '-', RIGHT(hex, 12)));
END
|
CREATE FUNCTION uuid_to_bin(s CHAR(36))
RETURNS BINARY(16) DETERMINISTIC
RETURN UNHEX(CONCAT(LEFT(s, 8), MID(s, 10, 4), MID(s, 15, 4), MID(s, 20, 4), RIGHT(s, 12)))
|
DELIMITER ;
- Misbit
-4
如果您有一个格式为标准GUID的char/varchar值，您可以使用简单的CAST(MyString AS BINARY16)将其存储为BINARY(16)，而无需使用所有那些令人费解的CONCAT + SUBSTR序列。
BINARY(16)字段比字符串更快地进行比较/排序/索引，并且在数据库中占用的空间也少两倍。
BINARY(16)字段比字符串更快地进行比较/排序/索引，并且在数据库中占用的空间也少两倍。
- George Hazan
2
2运行此查询显示CAST将uuid字符串转换为ASCII字节：set @a = uuid(); select @a，hex（cast（@a AS BINARY（16）））; 我得到16f20d98-9760-11e4-b981-feb7b39d48d6：3136663230643938 2D 39373630 2D 3131（添加空格进行格式化）。 0x31 = ascii 1，0x36 = ascii 6。 我们甚至得到了0x2D，这是连字符。 这与仅将guid存储为字符串没有太大区别，只是您在第16个字符处截断了字符串，从而切掉了机器特定的ID部分。 - bigh_29
是的，这只是截断。
select CAST("hello world, this is as long as uiid" AS BINARY(16));产生结果：hello world, thi - MD004网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接