如何将uuid存储为数字?

2021-11-20 00:00:00 uuid mysql

根据问题的答案，MySQL 中的 UUID 性能，答案建议将 UUID 存储为数字而不是字符串.我不太确定如何做到这一点.任何人都可以给我建议吗?我的 ruby​​ 代码如何处理?

Based on the answer of question, UUID performance in MySQL, the person who answers suggest to store UUID as a number and not as a string. I'm not so sure how it can be done. Anyone could suggest me something? How my ruby code deal with that?

推荐答案

如果我理解正确，您在主列中使用了 UUID?人们会说常规(整数)主键会更快，但还有另一种使用 MySQL 阴暗面的方法.事实上，当需要索引时，MySQL 使用二进制比其他任何方法都快.

If I understand correctly, you're using UUIDs in your primary column? People will say that a regular (integer) primary key will be faster , but there's another way using MySQL's dark side. In fact, MySQL is faster using binary than anything else when indexes are required.

由于 UUID 是 128 位并以十六进制写入，因此很容易加速和存储 UUID.

Since UUID is 128 bits and is written as hexadecimal, it's very easy to speed up and store the UUID.

首先，在你的编程语言中删除破折号

First, in your programming language remove the dashes

从110E8400-E29B-11D4-A716-446655440000到110E8400E29B11D4A716446655440000.

现在是 32 个字符(就像 MD5 哈希，这也适用).

Now it's 32 chars (like an MD5 hash, which this also works with).

由于 MySQL 中单个 BINARY 的大小为 8 位，BINARY(16) 是一个 UUID 的大小 (8*16 = 128).

Since a single BINARY in MySQL is 8 bits in size, BINARY(16) is the size of a UUID (8*16 = 128).

您可以插入使用:

INSERT INTO Table (FieldBin) VALUES (UNHEX("110E8400E29B11D4A716446655440000"))

和查询使用:

SELECT HEX(FieldBin) AS FieldBin FROM Table

现在在您的编程语言中，在位置 9、14、19 和 24 处重新插入破折号以匹配您的原始 UUID.如果位置总是不同，您可以将该信息存储在第二个字段中.

Now in your programming language, re-insert the dashes at the positions 9, 14, 19 and 24 to match your original UUID. If the positions are always different you could store that info in a second field.

完整示例:

CREATE TABLE  `test_table` (
    `field_binary` BINARY( 16 ) NULL ,
    PRIMARY KEY (  `field_binary` )
) ENGINE = INNODB ;

INSERT INTO  `test_table` (
    `field_binary`
)
VALUES (
    UNHEX(  '110E8400E29B11D4A716446655440000' )
);

SELECT HEX(field_binary) AS field_binary FROM `test_table`

如果您想对任何十六进制字符串使用此技术，请始终对字段长度执行 length/2.因此，对于 sha512，该字段将是 BINARY (64)，因为 sha512 编码长度为 128 个字符.

If you want to use this technique with any hex string, always do length / 2 for the field length. So for a sha512, the field would be BINARY (64) since a sha512 encoding is 128 characters long.

相关文章