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

あらすじ
=======

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

[^official_blog]: MySQL :: Mysql 8.0: UUID support (2021年12月5日閲覧) https://dev.mysql.com/blog-archive/mysql-8-0-uuid-support/
[^frontier]: MySQL < 8.xにUUID処理を追加する - Qiita (2021年12月5日閲覧) https://qiita.com/kazuhidet/items/bcfcc16428d2c8a697ff

情報整理
========

目的
----

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

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

前提
----

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

### ソース

&rarr; 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()`
---------------

### ソース

&rarr; https://gist.github.com/TkoolerLufar/7cd5f4e85ad2936a6b053256c5918056#file-bin_to_uuid-sql

### 見どころ

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

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

`IS_UUID()`
-----------

### ソース

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

参考文献
--------

脚注にまとめました。