如何在 MySQL 表中存储 GUID？

我使用 varchar (36)还是有更好的方法？

175849 次浏览
小开

我将它存储为字符(36)。

小开
最佳答案

当我询问存储对象 GUID 的最佳方法时，DBA 问我为什么我需要存储16个字节，而我可以用 Integer 以4个字节做同样的事情。既然他向我提出了这个挑战，我认为现在是提出来的好时机。话虽如此。

如果希望最佳地利用存储空间，可以将 guid 存储为 CHAR (16)二进制文件。

小开

Char (36)将是一个不错的选择。此外，还可以使用 MySQL 的 UUID ()函数，该函数返回36个字符的文本格式(带连字符的十六进制) ，可用于从数据库中检索此类 ID。

小开

添加 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
小开

二进制(16)比 varchar (32)更好。

小开

“更好”取决于你在优化什么。

您在多大程度上关心存储大小/性能与易于开发之间的关系？更重要的是，您是否生成了足够多的 GUID，或者是否足够频繁地获取它们，这很重要吗？

如果答案是“否”，那么 char(36)就足够好了，它使存储/获取 GUID 变得非常简单。否则，binary(16)是合理的，但是您必须依赖 MySQL 和/或您选择的编程语言来从通常的字符串表示来回转换。

小开

如果将 char/varchar 值格式化为标准 GUID，那么可以使用简单的 CAST (MyString AS BINARY16)将其存储为 BINARY (16) ，而不必使用那些令人难以置信的 CONCAT + SUBSTR 序列。

BINARY (16)字段的比较/排序/索引速度比字符串快得多，而且在数据库中占用的空间也少两倍

小开

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，因为 big endian 已经产生了最佳的二进制索引字节顺序。但是，您确实希望将最重要的字节移到较低的字节前面。因此，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 生成的时间最接近。它们只在第一块的最后三个部分有所不同。这些是时间戳中最不重要的位，这意味着当我们将它们转换为可索引的字节数组时，我们希望将它们向右推。作为一个计数器示例，最后一个 ID 是最新的，但 KCD 的交换算法将它放在第3个 ID 之前(dc 之前的3e，第一个块的最后一个字节)。

编制索引的正确顺序是:

1e497556eec5eb6...
1e497556eec5f10...
1e497556eec8ddc...
1e497556eee30d0...
1e497556efda038...
1e497556f9641bf...
1e49755758c3e3e...

有关支持信息，请参阅本文: http://mysql.rjweb.org/doc.php/uuid

注意，我没有把版本从时间戳的最高12位分割出来。这是您的示例中的 D 部分。我只是把它扔在前面。所以我的二进制序列最终是 DEFG9ABC 等等。这意味着我所有索引的 UUID 都是从同一个小部件开始的。这篇文章也是这么写的。

小开

对于那些只是偶然发现这一点的人来说，根据 Percona 的研究，现在有一个更好的选择。

它包括重新组织 UUID 块以进行最佳索引，然后转换为二进制以减少存储。

阅读完整的文章 给你

小开

我建议使用下面的函数，因为@bigh _ 29提到的那些函数可以将我的指南转换成新的(原因我不明白)。而且，这些在我的桌子上做的测试中要快一些。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 ;

提交答案