Как форматировать строку uuid из двоичного столбца в MySQL/MariaDB

В MySQL/MariaDB наиболее эффективный способ хранения uuid находится в столбце BINARY (16). Однако иногда вы хотите получить его как форматированную строку uuid.

Учитывая следующую структуру таблицы, как бы получить все uuids по умолчанию?

CREATE TABLE foo (uuid BINARY(16));

Ответ 1

Ниже будет создан результат, который я получил после:

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 добавляет две новые функции UUID:

UUID_TO_BIN
BIN_TO_UUID - это тот, который вы ищете

Так:

SELECT BIN_TO_UUID(uuid) FROM foo

Ответ 3

В более ранних версиях (до 8) вы можете создать function в 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)

Ответ 4

Здесь альтернатива, использующая 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;

Ответ 5

Правильный результат генерируется приведенным ниже сценарием, другие скрипты генерируют 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
Что было сгенерировано - c260962e-511e-9e4b-9a86-6db1a2770422

Как видите, они разные.

Ответ 6

Если вы ищете противоположное, то есть, как преобразовать строку в двоичный файл, возможно, сделать соединение или что-то еще, это описано здесь: Преобразование UUID в двоичный файл/из двоичного кода в узле

Этот фрагмент SQL, запущенный на Mysql 5.7, помог мне зафиксировать концепцию:

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.

строка → двоичная

Поэтому UNHEX(REPLACE('43d597d7-2323-325a-90fc-21fa5947b9f3', '-', '')) для преобразования UUID в двоичный файл во время 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)
))