MySQL 插入和选择 UUID 作为二进制 (16)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28251144/
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
Inserting and selecting UUIDs as binary(16)
提问by nickdnk
I don't understand why
我不明白为什么
SELECT UUID();
Returns something like:
返回类似：
3f06af63-a93c-11e4-9797-00505690773f
But if I insert it into a binary(16) field (the UUID() function) with for instance a BEFORE INSERT trigger and run a select, it returns something like:
但是，如果我将它插入到一个 binary(16) 字段（UUID() 函数）中，例如一个 BEFORE INSERT 触发器并运行一个选择，它会返回如下内容：
0782ef48-a439-11
Note that these two UUIDs are not the same data.
请注意，这两个 UUID 不是相同的数据。
I realize binary and an UUID string doesn't look identical, but shouldn't the selected data at least be just as long? Otherwise how can it possibly be equally likely to be unique?
我意识到二进制和 UUID 字符串看起来并不相同，但所选数据不应该至少一样长吗？否则它怎么可能同样是独一无二的？
Is it better to store it as char(36)? I just need it to be unique to prevent duplicate inserts. It is never selected or used for joins.
将其存储为 char(36) 是否更好？我只需要它是独一无二的，以防止重复插入。它永远不会被选择或用于连接。
EDIT:
编辑：
before trigger would be like:
在触发器之前会像：
BEGIN
if NEW.UUID IS NULL THEN
NEW.UUID = UUID();
END IF
END
回答by nickdnk
So, as a response to comments. The correct way of storing a 36-char UUID as binary(16) is to perform the insert in a manner like:
所以，作为对评论的回应。将 36 字符 UUID 存储为 binary(16) 的正确方法是以如下方式执行插入：
INSERT INTO sometable (UUID) VALUES
(UNHEX(REPLACE("3f06af63-a93c-11e4-9797-00505690773f", "-","")))
UNHEXbecause an UUID is already a hexed value. We trim (REPLACE) the dashes in the statement to bring the length down to 32 characters (our 16 bytes represented as HEX). You can do this at any point before storing it, obviously, so it doesn't have to be handled by the database.
UNHEX因为 UUID 已经是一个十六进制值。我们修剪 ( REPLACE) 语句中的破折号以将长度减少到 32 个字符（我们的 16 个字节表示为HEX）。显然，您可以在存储之前随时执行此操作，因此不必由数据库处理。
You may retrieve the UUID like this:
您可以像这样检索 UUID：
SELECT HEX(UUID) FROM sometable;
Just in case someone comes across this thread and is unsure how this works.
以防万一有人遇到这个线程并且不确定它是如何工作的。
And remember: If you're selecting a row using the UUID, use UNHEX()on the condition:
请记住：如果您使用 UUID 选择一行，请使用UNHEX()条件：
SELECT * FROM sometable WHERE UUID = UNHEX('3f06af63a93c11e4979700505690773f');
And not HEX()on the column:
而不是HEX()在列上：
SELECT * FROM sometable WHERE HEX(UUID) = '3f06af63a93c11e4979700505690773f';
The second solution, while it works, requires that MySQL HEXes all UUIDs before it can determine which rows match. It's very inefficient.
第二种解决方案虽然有效，但要求 MySQL HEXes 所有 UUID，然后才能确定哪些行匹配。这是非常低效的。
Edit: If you're using MySQL 8 you should have a look at the UUID functions as mentioned in SlyDave's answer. This answer is still correct, but it doesn't optimise the UUID indexes which can be done natively using those functions.
编辑：如果您使用的是 MySQL 8，您应该查看 SlyDave 的回答中提到的 UUID 函数。这个答案仍然是正确的，但它没有优化可以使用这些函数本机完成的 UUID 索引。
回答by SlyDave
As of MySQL 8 you can use two new UUID functions:
从 MySQL 8 开始，您可以使用两个新的UUID 函数：
BIN_TO_UUID
SELECT BIN_TO_UUID(uuid, true) AS uuid FROM foo; -- 3f06af63-a93c-11e4-9797-00505690773fUUID_TO_BIN
INSERT INTO foo (uuid) VALUES (UUID_TO_BIN('3f06af63-a93c-11e4-9797-00505690773f', true));
BIN_TO_UUID
SELECT BIN_TO_UUID(uuid, true) AS uuid FROM foo; -- 3f06af63-a93c-11e4-9797-00505690773fUUID_TO_BIN
INSERT INTO foo (uuid) VALUES (UUID_TO_BIN('3f06af63-a93c-11e4-9797-00505690773f', true));
This method also supports rearranging the time component of the uuid to enhance indexing performance (by ordering it chronologically), simply set the second argument to true - this only works for UUID1.
此方法还支持重新排列 uuid 的时间组件以增强索引性能（通过按时间顺序排列），只需将第二个参数设置为 true - 这仅适用于 UUID1。
If you are using the trueon UUID_TO_BINflag for indexing performance (recommended), you must also set it on BIN_TO_UUIDotherwise it won't convert back properly.
如果您使用trueonUUID_TO_BIN标志来提高索引性能（推荐），您还必须将其设置为 on，BIN_TO_UUID否则它将无法正确转换回来。
See the documentation for further details.
有关更多详细信息，请参阅文档。
回答by Devon
Polyfill for BIN_TO_UUID and UUID_TO_BIN for MySQL 5 with the swap_flag parameter.
使用 swap_flag 参数填充 MySQL 5 的 BIN_TO_UUID 和 UUID_TO_BIN。
DELIMITER $$
CREATE FUNCTION BIN_TO_UUID(b BINARY(16), f BOOLEAN)
RETURNS CHAR(36)
DETERMINISTIC
BEGIN
DECLARE hexStr CHAR(32);
SET hexStr = HEX(b);
RETURN LOWER(CONCAT(
IF(f,SUBSTR(hexStr, 9, 8),SUBSTR(hexStr, 1, 8)), '-',
IF(f,SUBSTR(hexStr, 5, 4),SUBSTR(hexStr, 9, 4)), '-',
IF(f,SUBSTR(hexStr, 1, 4),SUBSTR(hexStr, 13, 4)), '-',
SUBSTR(hexStr, 17, 4), '-',
SUBSTR(hexStr, 21)
));
END$$
CREATE FUNCTION UUID_TO_BIN(uuid CHAR(36), f BOOLEAN)
RETURNS BINARY(16)
DETERMINISTIC
BEGIN
RETURN UNHEX(CONCAT(
IF(f,SUBSTRING(uuid, 15, 4),SUBSTRING(uuid, 1, 8)),
SUBSTRING(uuid, 10, 4),
IF(f,SUBSTRING(uuid, 1, 8),SUBSTRING(uuid, 15, 4)),
SUBSTRING(uuid, 20, 4),
SUBSTRING(uuid, 25))
);
END$$
DELIMITER ;
SET @uuid = '6ccd780c-baba-1026-9564-5b8c656024db';
-- SELECTS with the actual values followed by expected value:
SELECT HEX(UUID_TO_BIN(@uuid, 0)), '6CCD780CBABA102695645B8C656024DB';
SELECT HEX(UUID_TO_BIN(@uuid, 1)), '1026BABA6CCD780C95645B8C656024DB';
SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid,0),0), '6ccd780c-baba-1026-9564-5b8c656024db';
SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid,1),1), '6ccd780c-baba-1026-9564-5b8c656024db';
Included are the SELECT samples from https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_uuid-to-binthat demonstrate that the above code returns the exact same results as the 8.0 function. These functions are considered DETERMINISTIC as they always produce the same output for a given input. See https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html
包括来自https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_uuid-to-bin的 SELECT 示例，这些示例表明上述代码返回与 8.0 函数完全相同的结果. 这些函数被认为是确定性的，因为它们总是为给定的输入产生相同的输出。见https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html
回答by Alain Tiemblo
I am using MariaDB so BIN_TO_UUIDfunctions family do not exist. I managed to get the corresponding values anyway.
我正在使用 MariaDB，因此BIN_TO_UUID函数系列不存在。无论如何，我设法获得了相应的值。
bin -> hex
bin -> hex
Here, uuidis the binary(16) value of an uuid; you'll use the value below to SELECT a readable version of it.
这里，uuid是 uuid 的 binary(16) 值；您将使用下面的值来选择它的可读版本。
LOWER(CONCAT(
SUBSTR(HEX(uuid), 1, 8), '-',
SUBSTR(HEX(uuid), 9, 4), '-',
SUBSTR(HEX(uuid), 13, 4), '-',
SUBSTR(HEX(uuid), 17, 4), '-',
SUBSTR(HEX(uuid), 21)
))
hex -> bin
hex -> bin
Here, cc6e6d97-5501-11e7-b2cb-ceedca613421is a readable version of an UUID, and you'll use the value below in a WHERE clause to seek for it.
这cc6e6d97-5501-11e7-b2cb-ceedca613421是一个 UUID 的可读版本，您将在 WHERE 子句中使用下面的值来查找它。
UNHEX(REPLACE('cc6e6d97-5501-11e7-b2cb-ceedca613421', '-', ''))
Cheers
干杯
回答by Alexis Wilke
The other answers are correct. The UUID()function returns a 36 character string and that needs to be converted using the shown functions (UNHEX()or, on newer platforms, UUID_TO_BIN()).
其他答案都是正确的。该UUID()函数返回一个 36 个字符的字符串，需要使用显示的函数（UNHEX()或者，在较新的平台上，UUID_TO_BIN()）进行转换。
However, if you use your own software to create your UUIDs, then you can use the Hexadecimal Literalnotationinstead.
但是，如果您使用自己的软件创建 UUID，则可以改用十六进制文字表示法。
So I would use the following with the MySQL UUID()function:
所以我会在 MySQLUUID()函数中使用以下内容：
INSERT INTO sometable (id) VALUES (UNHEX(REPLACE(UUID(), '-', ''))); -- all versions
INSERT INTO sometable (id) VALUES (UUID_TO_BIN(UUID()); -- since v8.0
But use this in case I generate my own UUIDs;
但是如果我生成自己的 UUID，请使用它；
INSERT INTO sometable (id) VALUES 0x3f06af63a93c11e4979700505690773f;
Similarly, you can use Hexadecimal Literals in your WHEREclauses:
同样，您可以在WHERE子句中使用十六进制文字：
SELECT * FROM sometable WHERE id = 0x3f06af63a93c11e4979700505690773f;
This will be faster if you do not have to convert your data to a UUID string each time.
如果您不必每次都将数据转换为 UUID 字符串，这会更快。
Note: the 'x'in '0xaBcis case sensitive. The hexadecimal digits are not, however.
注意：'x'in'0xaBc区分大小写。然而，十六进制数字不是。
