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

How to format uuid string from binary column in MySQL/MariaDB

提问by Lilleman

In MySQL/MariaDB the most efficient way to store uuid is in a BINARY(16) column. However, sometimes you want to obtain it as a formatted uuid string.

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

Given the following table structure, how would I obtain all uuids in a default formatted way?

鉴于下表结构，我将如何以默认格式获取所有 uuid？

CREATE TABLE foo (uuid BINARY(16));

回答by Lilleman

The following would create the result I was after:

以下将创建我所追求的结果：

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;

回答by Oleg Mikheev

MySQL 8 adds two new UUID functions:

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

• UUID_TO_BIN
• BIN_TO_UUID- this is the one you're looking for

• UUID_TO_BIN
• BIN_TO_UUID- 这是你要找的

So:

所以：

SELECT BIN_TO_UUID(uuid) FROM foo

回答by Andrii Abramov

In earlier (prior to 8) versions you can create a functionin MySQL like the following:

在早期（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)
));

And then simply use it in your queries:

然后只需在您的查询中使用它：

SELECT
  uuid_of(id)
  name,
  age
FROM users

And it produces:

它产生：

(c6f5703b-fec2-43fd-8f45-45f06583d450, Some name, 20)

（c6f5703b-fec2-43fd-8f45-45f06583d450，一些名字，20）

回答by zayquan

If you are looking for the opposite, i.e., how to convert from string to binary, perhaps to do a join or something, this is covered here : Convert UUID to/from binary in Node

如果您正在寻找相反的内容，即如何从字符串转换为二进制，也许是为了进行连接或其他操作，请参见此处：Convert UUID to/from binary in Node

This piece of SQL run on Mysql 5.7 helped lock in the concept for me:

这段在 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)
  ))

The output is 43d597d7-2323-325a-90fc-21fa5947b9f3.

输出是43d597d7-2323-325a-90fc-21fa5947b9f3。

string -> binary

字符串 -> 二进制

So UNHEX(REPLACE('43d597d7-2323-325a-90fc-21fa5947b9f3', '-', ''))to convert a UUID to binary during an INSERT/ UPDATE/ JOIN/ SELECTwhatever, and

所以，UNHEX(REPLACE('43d597d7-2323-325a-90fc-21fa5947b9f3', '-', ''))到时的UUID转换为二进制的INSERT/ UPDATE/ JOIN/SELECT什么的，

binary -> string

二进制 -> 字符串

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

回答by toddsby

Here's an alternative using concat_ws

这是使用 concat_ws 的替代方法

Store raw uuid in a variable @x

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

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

Use CONCAT_WS and SUBSTR to parse human readable UUID

使用 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;

回答by Alexz

The correct result is generated by the script below, the other scrips generated a UUID however not the right one.

下面的脚本生成了正确的结果，其他脚本生成了一个 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) 
    )

Results running the other scripts generated wrong UUID as per below:

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

• Expected UUID - 2e9660c2-1e51-4b9e-9a86-6db1a2770422
• What was generated - c260962e-511e-9e4b-9a86-6db1a2770422

• 预期的 UUID - 2e9660c2-1e51-4b9e-9a86-6db1a2770422
• 产生了什么—— c260962e-511e-9e4b-9a86-6db1a2770422

As you can see they are different.

如您所见，它们是不同的。