以二进制形式插入和选择 UUID(16) [英] Inserting and selecting UUIDs as binary(16)

问题描述

我不明白为什么

SELECT UUID();

返回类似:

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

但是，如果我将它插入到二进制 (16) 字段(UUID() 函数)中，例如使用 BEFORE INSERT 触发器并运行选择，它会返回如下内容:

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 存储为二进制 (16) 的正确方法是以如下方式执行插入:

So, as a response to comments. The correct way to store 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 个字符(我们的 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 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');

或 字面表示法(如 Alexis 所述威尔克):

or literal notation (as mentioned by Alexis Wilke):

SELECT * FROM sometable WHERE UUID = 0x3f06af63a93c11e4979700505690773f;

而不是 HEX()在列上:

And NOT HEX()on the column:

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

最后一个解决方案虽然有效，但需要 MySQL HEXes 所有 UUID，然后才能确定哪些行匹配.效率很低.

The last 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 索引，这些索引可以使用这些函数本地完成.如果你在 <MySQL 8 你可以实现 Devon 的 polyfill，它提供了与以前版本的 MySQL 相同的功能.

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. If you're on < MySQL 8 you can implement Devon's polyfill, which provides identical functionality on previous versions of MySQL.

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