插入并选择UUID作为二进制文件(16) [英] Inserting and selecting UUIDs as binary(16)

问题描述

我不明白为什么

SELECT UUID();

返回类似的内容

3f06af63-a93c-11e4-9797-00505690773f

但是，如果我使用例如BEFORE INSERT触发器将其插入Binary(16)字段(UUID()函数)中并运行选择，它将返回类似以下内容的信息:

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:

0782ef48-a439-11

请注意，这两个UUID不是相同的数据.

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?

将其存储为char(36)更好吗?我只需要它具有唯一性即可防止重复插入.永远不会选择它或将其用于联接.

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.

在触发之前就像:

BEGIN

if NEW.UUID IS NULL THEN

NEW.UUID = UUID();

END IF

END

推荐答案

因此，作为对评论的回应.将36个字符的UUID存储为binary(16)的正确方法是以如下方式执行插入操作:

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:

INSERT INTO sometable (UUID) VALUES
       (UNHEX(REPLACE("3f06af63-a93c-11e4-9797-00505690773f", "-","")))

UNHEX，因为UUID已经是十六进制值.我们对语句中的破折号进行修整(REPLACE)，以将长度减小到32个ASCII字符(我们的16个字节表示为HEX).显然，您可以在存储它之前随时进行此操作，因此它不必由数据库处理.

UNHEX because an UUID is already a hexed value. We trim (REPLACE) the dashes in the statement to bring the length down to 32 ASCII 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.

您可以像这样检索UUID:

You may retrieve the UUID like this:

SELECT HEX(UUID) FROM sometable;

以防万一有人遇到这个线程并且不确定它是如何工作的.

Just in case someone comes across this thread and is unsure how this works.

请记住:如果要使用UUID选择行，请在条件下使用UNHEX() :

And remember: If you're selecting a row using the UUID, use UNHEX() on the condition:

SELECT * FROM sometable WHERE UUID = UNHEX('3f06af63a93c11e4979700505690773f');

而不是该列上的HEX():

And not HEX()on the column:

SELECT * FROM sometable WHERE HEX(UUID) = '3f06af63a93c11e4979700505690773f';

第二个解决方案在起作用的同时，要求MySQL HEX定义所有UUID，然后才能确定匹配的行.效率很低.

The second solution, while it works, requires that MySQL HEXes all UUIDs before it can determine which rows match. It's very inefficient.

如果您使用的是MySQL 8，则应查看SlyDave答案中提到的UUID函数.这个答案仍然是正确的，但是它并没有优化可以使用这些函数本地完成的UUID索引.

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.

这篇关于插入并选择UUID作为二进制文件(16)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！