如何从 MySQL/MariaDB 中的二进制列格式化 uuid 字符串 [英] How to format uuid string from binary column in MySQL/MariaDB

问题描述

在 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;

这篇关于如何从 MySQL/MariaDB 中的二进制列格式化 uuid 字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！