我应该如何在mysql表中存储guid?

当我询问如何为对象存储guid的最佳方法时,我的DBA问我为什么需要存储16个字节,而我可以用一个整数在4个字节中执行相同的操作。既然他向我提出了那个挑战,我想现在是时候提出来了。有人说…

如果要最大限度地利用存储空间,可以将guid存储为char(16)二进制文件。

我会把它储存成一个字符(36)。

除了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

char(36)是个不错的选择。也可以使用mysql的uuid()函数返回36个字符的文本格式(带连字符的十六进制),该格式可用于从数据库中检索此类ID。

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

您对存储大小/性能和易开发性有多关心?更重要的是-您是否生成了足够多的guid,或者足够频繁地获取它们,这很重要?

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

二进制(16)会很好,优于varchar(32)。

应该调整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的生成时间最接近。它们只在第一个块的最后3个字节中变化。这些是时间戳的最低有效位,这意味着当我们将其转换为可索引字节数组时,我们希望将它们向右推。作为一个反例,最后一个ID是最新的,但是KCD的交换算法会把它放在第三个ID之前(3e在DC之前,最后一个来自第一个块的字节)。

索引的正确顺序是:

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

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

***请注意,我不会从时间戳的高12位中拆分版本nibble。这是你的例子中的d字节。我只是把它扔在前面。所以我的二进制序列最终被定义为9abc等等。这意味着我所有的索引UUID都以相同的一个字节开始。这篇文章也做了同样的事情。

对于那些刚刚跌跌撞撞的人来说,根据珀科纳的研究,现在有了一个更好的选择。

它包括重新组织UUID块以优化索引,然后转换为二进制以减少存储。

阅读全文 here

我建议使用下面的函数,因为@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 ;

如果您有一个char/varchar值格式化为标准的guid,那么您可以使用简单的cast(mystring作为binary16)将其存储为binary(16),而不必使用concat+substr的令人难以置信的序列。

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