Zenn
😊

UUID_TO_BIN in MariaDB is UNHEX(REPLACE(UUID(),'-',''))

2023/07/04に公開
MySQL
MariaDB
tech

TL;DR

UNHEX(REPLACE(UUID(),'-',''))

Environment

  • MariaDB mariadbd Ver 10.11.4-MariaDB for Linux

Background

I got an error when migrating databsae from MySQL to MariaDB including DEFAULT UUID_TO_BIN() column.

Function or expression '`UUID_TO_BIN`()' cannot be used in the DEFAULT clause of `inner_id`

Example

CREATE TABLE users (
  id BIGINT(20) NOT NULL PRIMARY KEY AUTO_INCREMENT
  , inner_id BINARY(16) NOT NULL DEFAULT UNHEX(REPLACE(UUID(),'-',''))
  , name VARCHAR(64)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Ref

MySQL/MariaDB BIN_TO_UUID and UUID_TO_BIN Polyfill

Discussion