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

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

本文翻译自 Lilleman 查看原文 2016/05/11 8488 mariadb/ mysql/ uuid

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?

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

CREATE TABLE foo (uuid BINARY(16));

4 个解决方案

#1

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;

#2

MySQL 8 adds two new UUID functions:

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

UUID_TO_BIN
UUID_TO_BIN
BIN_TO_UUID - this is the one you're looking for
BIN_TO_UUID - 这是你要找的那个

So:

所以：

SELECT BIN_TO_UUID(uuid) FROM foo

#3

In earlier (prior to 8) versions you can create a function in 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）

#4

Here's an alternative using 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;

智能推荐

注意！

本站翻译的文章，版权归属于本站，未经许可禁止转摘，转摘请注明本文地址：http://www.silva-art.net/blog/2016/05/11/39e9ea3cfb5b8e9db0b1ac2d7ccfad74.html。

猜您在找

在MySQL中存储二进制格式的字符串。 - Storing string in binary format in MySQL 如何在二进制代码中隐藏字符串? - How to hide a string in binary code? 如何在Teradata中将字符串转换为二进制？ - How to convert string to binary in Teradata? 如何在mysql数据库中找到类似的二进制字符串？ - How to find similar binary string in mysql database? 如何在Swift中格式化JSON字符串？ - How to format a JSON String in Swift?