MySQL 如何将uuid存储为数字？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10950202/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to store uuid as number?
提问by Chamnap
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?
根据问题的答案，MySQL 中的 UUID 性能，回答者建议将 UUID 存储为数字而不是字符串。我不太确定如何做到这一点。任何人都可以给我建议吗？我的 ruby 代码如何处理？
回答by David Bélanger
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 吗？人们会说常规（整数）主键会更快，但还有另一种使用 MySQL 阴暗面的方法。事实上，当需要索引时，MySQL 使用二进制比其他任何东西都快。
Since UUID is 128 bits and is written as hexadecimal, it's very easy to speed up and store the UUID.
由于 UUID 是 128 位并以十六进制写入，因此非常容易加速和存储 UUID。
First, in your programming language remove the dashes
首先，在您的编程语言中删除破折号
From 110E8400-E29B-11D4-A716-446655440000to 110E8400E29B11D4A716446655440000.
从110E8400-E29B-11D4-A716-446655440000到110E8400E29B11D4A716446655440000。
Now it's 32 chars (like an MD5 hash, which this also works with).
现在它是 32 个字符（就像一个 MD5 哈希，这也适用）。
Since a single BINARYin MySQL is 8 bits in size, BINARY(16)is the size of a UUID (8*16 = 128).
由于BINARYMySQL 中的单个大小为 8 位，因此BINARY(16)是 UUID 的大小 (8*16 = 128)。
You can insert using:
您可以使用以下方法插入：
INSERT INTO Table (FieldBin) VALUES (UNHEX("110E8400E29B11D4A716446655440000"))
INSERT INTO Table (FieldBin) VALUES (UNHEX("110E8400E29B11D4A716446655440000"))
and query using:
并使用以下查询：
SELECT HEX(FieldBin) AS FieldBin FROM Table
SELECT HEX(FieldBin) AS FieldBin FROM Table
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.
现在在您的编程语言中，在位置 9、14、19 和 24 处重新插入破折号以匹配您的原始 UUID。如果位置总是不同，您可以将该信息存储在第二个字段中。
Full example :
完整示例：
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`
If you want to use this technique with any hex string, always do length / 2for the field length. So for a sha512, the field would be BINARY (64)since a sha512 encoding is 128 characters long.
如果您想对任何十六进制字符串使用此技术，请始终length / 2对字段长度进行操作。因此，对于 sha512，该字段将是BINARY (64)因为 sha512 编码长度为 128 个字符。
回答by dolmen
The Percona blog has an article (that includes benchmarks) that replies to your question: Store UUID in an optimized way.
Percona 博客有一篇文章（包括基准测试）回答了您的问题：以优化方式存储 UUID。
回答by magallanes
I don't think that its a good idea to use a binary.
我认为使用二进制文件不是一个好主意。
Let's say that you want to query some value:
假设您要查询某个值：
SELECT HEX(field_binary) AS field_binary FROM `test_table`
If we are returning several values then we are calling the HEX function several times.
如果我们返回多个值，那么我们将多次调用 HEX 函数。
However, the main problem is the next one:
但是，主要问题是下一个：
SELECT * FROM `test_table`
where field_binary=UNHEX('110E8400E29B11D4A716446655440000')
And using a function inside the where, simply ignores the index.
并在 where 中使用函数，只需忽略索引。
Also
还
SELECT * FROM `test_table`
where field_binary=x'skdsdfk5rtirfdcv@#*#(&#@'
Could leads to many problems.
可能会导致很多问题。
