如何在MySQL/MariaDB中从二进制列格式化uuid字符串

Question

如何在MySQL/MariaDB中从二进制列格式化uuid字符串

50

在MySQL/MariaDB中，存储uuid最有效的方式是使用BINARY(16)列。然而，有时您希望将其作为格式化的uuid字符串获取。

给定以下表结构，如何以默认格式获取所有uuid？

CREATE TABLE foo (uuid BINARY(16));

- Lilleman

1

请看我的UUID博客。 - Rick James

7个回答

53

MySQL 8增加了两个新的UUID函数：

UUID_TO_BIN
BIN_TO_UUID - 这是你要找的

所以：

SELECT BIN_TO_UUID(uuid) FROM foo

- Oleg Mikheev

1

我在想你是否可以只使用 BIN_TO_UUID(<fieldName>)，这将确认它。虽然，如果你必须这样做，你可能已经传递了 true 标志到 UUID_TO_BIN，因此你还需要使用它来获取正确的 UUID。即 BIN_TO_UUID( <fieldName>, true )。 - Native Coder

1

我收到了“函数不存在”的错误信息，而且我已经使用了最新版本。 - Mihai Vlasceanu

如果您使用没有破折号的UUID，您也可以使用更短的十六进制字面量x'uuid'代替UUID_TO_BIN('uuid')。 - Synthesis

21

在早期版本中（8之前），您可以像以下示例一样在MySQL中创建函数：

CREATE
  FUNCTION uuid_of(uuid BINARY(16))
  RETURNS VARCHAR(36)
  RETURN 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)
));

然后在你的查询中简单地使用它：

SELECT
  uuid_of(id)
  name,
  age
FROM users

它会产生以下结果:

(c6f5703b-fec2-43fd-8f45-45f06583d450, 某个名字, 20)

- Andrii Abramov

很抱歉，但这个计算是错误的，它并没有反映出原始的UUID。当在MySQL Workbench中查询时，UUID“2e9660c2-1e51-4b9e-9a86-6db1a2770422”显示为“c260962e-511e-9e4b-9a86-6db1a2770422”。 - Alexz S.

@Alex.H 有没有任何复现？尝试执行以下语句：SELECT hex(uuid_column) FROM ${table}，然后再执行 SELECT uuid_of(uuid_column) FROM ${table}。 - Andrii Abramov

hex(myId)和uuid_of(uuid_column)实际上是一样的C260962E511E9E4B9A866DB1A2770422和c260962e-511e-9e4b-9a86-6db1a2770422，但我认为它并不是我的MVC应用程序生成并保存到数据库中的ID。它生成了2E9660C2-1E51-4B9E-9A86-6DB1A2770422，而保存为C260962E511E9E4B9A866DB1A2770422。这可能是因为我正在使用MySQL EF Adapter的问题，因为如果我使用从uuid_of获取的UUID查询任何内容，我就无法获取任何内容，但如果我使用我上面发布的函数获取UUID，则可以获取记录。 - Alexz S.

8

如果您想要相反的操作，即将字符串转换为二进制，可能是为了进行连接等操作，可以在这里找到相关内容：在Node中将UUID转换为/从二进制对我来说，这段在Mysql 5.7上运行的SQL语句帮助我巩固了这个概念：

SELECT
  LOWER(CONCAT(
    SUBSTR(HEX(UNHEX(REPLACE('43d597d7-2323-325a-90fc-21fa5947b9f3', '-', ''))), 1, 8), '-',
    SUBSTR(HEX(UNHEX(REPLACE('43d597d7-2323-325a-90fc-21fa5947b9f3', '-', ''))), 9, 4), '-',
    SUBSTR(HEX(UNHEX(REPLACE('43d597d7-2323-325a-90fc-21fa5947b9f3', '-', ''))), 13, 4), '-',
    SUBSTR(HEX(UNHEX(REPLACE('43d597d7-2323-325a-90fc-21fa5947b9f3', '-', ''))), 17, 4), '-',
    SUBSTR(HEX(UNHEX(REPLACE('43d597d7-2323-325a-90fc-21fa5947b9f3', '-', ''))), 21)
  ))

输出结果为43d597d7-2323-325a-90fc-21fa5947b9f3。

将UUID转换为二进制，使用UNHEX(REPLACE('43d597d7-2323-325a-90fc-21fa5947b9f3', '-', ''))在INSERT/UPDATE/JOIN/SELECT中进行操作。

将二进制转换为字符串。

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)
))

- zayquan

谢谢，老兄。我每周都要复制粘贴这个好几次 xaxa。 - Ivan Kaloyanov

2

正确的结果由以下脚本生成，其他脚本生成了UUID但不是正确的。最初的回答。

CONCAT(
    substr(hex(Id), 7, 2), substr(hex(Id), 5, 2), substr(hex(Id), 3, 2), substr(hex(Id), 1, 2), '-'
    , substr(hex(Id), 11, 2) , substr(hex(Id), 9, 2) , '-'
    , substr(hex(Id), 15, 2) , substr(hex(Id), 13, 2) , '-'
    , substr(hex(Id), 17, 4) , '-'
    , substr(hex(Id), 21, 12) 
    )

运行其他脚本生成的结果是错误的UUID，如下所示：

期望的UUID - 2e9660c2-1e51-4b9e-9a86-6db1a2770422
生成的UUID - c260962e-511e-9e4b-9a86-6db1a2770422

你可以看到它们是不同的。

最初的回答：

- Alexz S.

这对我也起作用了。我尝试使用MySQL 8方法BIN_TO_UUID或其他方法，但它不是正确的UUID。我有一个C#解决方案，GUID被转换为HEX并在base64中加密。 - Eduard

1

根据此Jira工单https://jira.mariadb.org/browse/MDEV-15854，UUID_TO_BIN和BIN_TO_UUID未包含在Mariadb Server 10.5版本中。如果您正在使用此版本或更低版本的Mariadb Server，则必须使用上述自定义实现。

- MATTHEW SILVEUS

1

这里提供一种使用concat_ws的替代方法

将原始uuid存储在变量@x中。

SELECT @x := hex(uuid)
FROM foo;

使用CONCAT_WS和SUBSTR解析可读的UUID

SELECT
  LOWER(CONCAT_WS('-',
    SUBSTR(@x, 1, 8),
    SUBSTR(@x, 9, 4),
    SUBSTR(@x, 13, 4),
    SUBSTR(@x, 17, 4),
    SUBSTR(@x, 21)
  )) AS uuid;

- toddsby

另一种可选方法在这里：https://dev59.com/YIXca4cB1Zd3GeqPHFuc - toddsby

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Lilleman · Accepted Answer

以下内容会得到我想要的结果：

SELECT
  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)
  ))
FROM foo;