UUID_TO_BIN() 関数と BIN_TO_UUID() 関数をMySQL5.6で使えるようにするpolyfillを書いてみた

あらすじ

MySQL に念願(?)の UUID 関数が実装された¹話を聞いて調べてみたら「MySQL < 8.xにUUID処理を追加する²」という記事を見つけました。
くだんの記事では BEGIN/END 文で複数の文を書いて段階的に加工していますが、そもそも MySQL は関数型プログラミング的な側面があるから RETURN 文1個で書けるんじゃない? と思ったのと、単純に自分で作ってみるの面白そう、と思い関数の自作に挑むことにしました。

情報整理

目的

MySQL 8 で公式が導入した¹関数 UUID_TO_BIN() / BIN_TO_UUID() と同等の機能を持つストアドファンクションを MySQL 8未満 で書く。

ただ先駆者²と全く同じ機能では面白みに欠けるので、第2引数 swap_flag 付きで実装いたします。

前提

1. UUID としては以下の形式を想定する。
   1. 12345678-abcd-1234-abcd-123456789abc
   2. {12345678-abcd-1234-abcd-123456789abc}
   3. 12345678abcd1234abcd123456789abc
2. UUID_TO_BIN() にこれ以外のUUIDが入力されたら NULL を返す。
   (MySQL 8 公式組み込み関数ではエラー 1424 になりますが、文の途中では残念ながらエラーを起こせなさそうなので NULL 返却で妥協)
3. BIN_TO_UUID() が返す UUID は上記 1 番目のフォーマット。
4. 元々128ビットのデータ(を文字列に落とし込んだもの)なので、"1バイト=8ビット"の環境では16バイトのバイナリデータでも表現できる。
5. そんな UUID を MySQL に格納する場合(特にインデックス張った列に入れる場合)、 BINARY(16) 型で入れた方がコンパクトに収まる。
6. これを実現するための UUID_TO_BIN() / BIN_TO_UUID()

UUID_TO_BIN() 関数

UUID 文字列を16バイトのバイナリデータに変換します。

• 戻り値: VARBINARY(16) 与えられた UUID のバイナリ表現
• 引数:
  • string_uuid VARCHAR: バイナリ符号化したい UUID の文字列表現
  • swap_flag BOOLEAN: 一部バイト列を入れ替える。UUID v1 の昇順が時刻の昇順に等しくなるようにするための機能らしく、インデックス値にこれを使うと処理効率が最適化されるそうです。

BIN_TO_UUID() 関数

16バイトのバイナリデータ UUID 文字列に変換します。

• 戻り値: VARCHAR(36) 与えられたバイナリの UUID 形式文字列
• 引数:
  • binary_uuid VARBINARY: バイナリ符号化済みの UUID
  • swap_flag BOOLEAN: 一部バイト列を入れ替える。 UUID_TO_BIN() の swap_flag ON時入れ替えの逆変換です。

IS_UUID() 関数

ある文字列が UUID_TO_BIN() の対応する書式のUUIDになっているかどうかを判定します。

• 戻り値: BOOLEAN string_uuid がUUIDの書式に沿っているかどうか
• 引数:
  • string_uuid VARCHAR(16383): 判定対象の文字列

出来上がったコード

UUID_TO_BIN()

ソース

→ https://gist.github.com/TkoolerLufar/7cd5f4e85ad2936a6b053256c5918056#file-uuid_to_bin-sql

見どころ

まずほとんどの行の頭に謎のコメントが入っていますが、あれは MySQL 8.0.0 以降でのみ普通のコードとして解釈される部分です。
中には1行コメントの印 -- が入っているので、 MySQL 8 以降では全文がコメントアウトされる仕掛けになっています。
これで原文ママ MySQL 8 ホストに流しても大丈夫!

また、 string_uuid はサブクエリで一旦シンプルな16進数字列に変換しています。
これにより swap_flag が FALSE のときの式が非常にシンプルになっています。

あとは swap_flag に関係なく必要なキャストを CASE 文全体にかけてることでしょうか。…いるのかあれ?

BIN_TO_UUID()

ソース

→ https://gist.github.com/TkoolerLufar/7cd5f4e85ad2936a6b053256c5918056#file-bin_to_uuid-sql

見どころ

ロジック自体の工夫はありませんが、文字列にハイフンを挿入する式の swap_flag で分岐する箇所の非対称性が興味深いものでした。
差分が SUBSTR のオフセット値のみしかありません。

逆に言えば UUID v1 はその箇所を入れ替えるだけで冒頭のランダムくささを向上させていることになります。
面白いですね。

IS_UUID()

ソース

→ https://gist.github.com/TkoolerLufar/7cd5f4e85ad2936a6b053256c5918056#file-is_uuid-sql

見どころ

なんてことはありません。正規表現でゴリゴリ書きました。

あとはほぼ任意長の文字列を受け容れるために引数のサイズを長めにとったくらいでしょうか。
引数の長さを例えば 39 文字にすると、これを超える文字列を渡した際にエラーになってしまいまして…このため引数の文字列長を長めにしています。
65535kB まで行いけるらしいので、このサイズを utf8mb4 1文字分の最大バイト数 4 で割って 16383 文字まで受け入れるようにいたしました。

TEXT とどちらが好いかは検討の余地がある…?

まとめ

サブクエリで2段階に分けて加工しているので、これを読みやすいと感じるかどうかは読み手によって分かれそうです。
でも RETURN 文1個でコーディングするという目標は達成できました。ご参考になれば幸いです。

あと MySQL における UUID (特に時刻由来の UUID v1) がインデックス最適化の観点で特別扱いされているあたりに、 MySQL の徹底した最適化精神を感じました。 これが DBMS の意地か…

ちなみに個人的には UUID などを扱う際の文字コード&照合順序として CHARACTER SET 'latin1' COLLATE 'latin1_bin' がイチオシなので関数に書き込もうと思ったのですが、 MySQL 8 組み込み関数の方がその場の文字コードで処理していたので止めました。
まあ BINARY(16) で保存すればそんな配慮は無用ですね。

参考文献

脚注にまとめました。

1. MySQL :: Mysql 8.0: UUID support (2021年12月5日閲覧) https://dev.mysql.com/blog-archive/mysql-8-0-uuid-support/ ↩ ↩²

2. MySQL < 8.xにUUID処理を追加する - Qiita (2021年12月5日閲覧) https://qiita.com/kazuhidet/items/bcfcc16428d2c8a697ff ↩ ↩²