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

2022-01-15 00:00:00 uuid mariadb mysql

在 MySQL/MariaDB 中，存储 uuid 的最有效方式是在 BINARY(16) 列中.但是，有时您希望将其作为格式化的 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.

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

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

CREATE TABLE foo (uuid BINARY(16));

推荐答案

以下将创建我想要的结果:

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;

相关文章