MySQL < 8.xにUUID処理を追加する #iOS

スマートフォン、特にiOS向けのAPIサーバーなどを構築しているとMySQLのデータベースにUUIDを格納しなければならないケースが多々ありますが、MySQL 8からは色々とUUIDを操作するための関数が増えていて便利に使えるのですが、残念なことにMySQL 8より以前のバージョンでは新しいUUIDを生成するためにUUID()とUUID_SHORT()くらいしか使えません。
しかし、普通はUUIDをMySQLで生成しなければならないケースはそれほど多くはなく、UUIDを扱う際に(たぶん)もっとも使いたいのは無駄なくUUIDをデータベースに格納するための機能と、それを取り出すための機能なのではないでしょうか？
そこで、UUIDを効率よく16バイトのバイナリ属性カラムへと格納するための方法を考えてみました。

uuidを格納するためには16バイトの領域が必要

データを格納するテーブルを作成

mysql> CREATE TABLE uuid_table (uuid BINARY(16));

カラムuuidに文字列からバイナリに変換してデータを格納 (この方法ならハイフンの有無、a-fA-Fの大文字小文字に無関係で扱えます)

文字列で受け取ったUUIDを16バイトのバイナリデータとして格納

mysql> INSERT INTO uuid_table (UUID) VALUES (UNHEX(REPLACE("29c71680-825c-45cd-8389-1e335e84fa57", "-","")));

カラムuuidのデータを文字列に変換して表示

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 uuid_table;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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))) |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 29c71680-825c-45cd-8389-1e335e84fa57                                                                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

カラムuuidのバイナリデータから文字列で指定したuuidを検索 (この方法ならハイフンの有無、a-fA-Fの大文字小文字に無関係で扱えます)

mysql> 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 uuid_table WHERE uuid=UNHEX(REPLACE("29c71680-825c-45cd-8389-1e335e84fa57", "-",""));
+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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))) |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 29c71680-825c-45cd-8389-1e335e84fa57                                                                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

このような方法でuuidをデータベースに格納すれば文字列だとハイフンありで36文字(最低でも37バイト)、ハイフンなしでも25バイトの領域が必要だったものが16バイトの領域に格納できます。

ちなみに、以下のように英文字を小文字にするLOWERを省略すればa-fは大文字のA-Fで表示されます。 (MySQLのHEXがA-Fは大文字で出力するので)

mysql> SELECT 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 uuid_table;
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)) |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| 29C71680-825C-45CD-8389-1E335E84FA57                                                                                                                    |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 rows in set (0.00 sec)

また、単純にHEXだけで変換すればハイフンなしの書式でデータを取り出せます。

mysql> SELECT HEX(uuid) FROM uuid_table;
+----------------------------------+
| HEX(uuid)                        |
+----------------------------------+
| 29C71680825C45CD83891E335E84FA57 |
+----------------------------------+
1 rows in set (0.00 sec)

ついでに、上記の処理を行なうためのUUID_TO_BINとBIN_TO_UUIDをストアド・プロシージャにしてみました。
本当ならMySQL 8互換で実装したかったのですが、残念ながらMySQLのストアド・プロシージャでは引数の省略ができないので、2番目の引数でのtime-lowとtime-highの入れ替え機能は実装していません。
※ _functionを1にすればtime-lowとtime-highの入れ替えを行なう様に機能だけは実装してあるので、必要であればこれを利用できます(^-^;)

以下のような感じで使えます。

mysql> SET @uuid = '6ccd780c-baba-1026-9564-5b8c656024db';

mysql> SELECT HEX(UUID_TO_BIN(@uuid));
+----------------------------------+
| HEX(UUID_TO_BIN(@uuid))          |
+----------------------------------+
| 6CCD780CBABA102695645B8C656024DB |
+----------------------------------+

mysql> SELECT BIN_TO_UUID(UUID_TO_BIN(@uuid));
+--------------------------------------+
| BIN_TO_UUID(UUID_TO_BIN(@uuid))      |
+--------------------------------------+
| 6ccd780c-baba-1026-9564-5b8c656024db |
+--------------------------------------+

実装はこちら

uuid_extension.sql

DROP FUNCTION IF EXISTS `BIN_TO_UUID`;
DELIMITER //
CREATE FUNCTION `BIN_TO_UUID` (
    _uuid BINARY(16)
)
RETURNS VARCHAR(36)
DETERMINISTIC NO SQL
COMMENT 'BIN_TO_UUID(uuid) => 29c71680-825c-45cd-8389-1e335e84fa57'
BEGIN
  DECLARE _uuid_string VARCHAR(36) DEFAULT NULL;
  DECLARE _function INT(1) DEFAULT 0;
  CASE _function
    WHEN 0 THEN
      SET _uuid_string = 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)));
    WHEN 1 THEN
      SET _uuid_string = LOWER(CONCAT(SUBSTR(HEX(_uuid), 9, 4), SUBSTR(HEX(_uuid), 13, 4), '-', SUBSTR(HEX(_uuid), 5, 4), '-', SUBSTR(HEX(_uuid), 1, 4), '-', SUBSTR(HEX(_uuid), 17, 4), '-', SUBSTR(HEX(_uuid), 21)));
  END CASE;
  RETURN _uuid_string;
END;
//
DELIMITER ;

DROP FUNCTION IF EXISTS `UUID_TO_BIN`;
DELIMITER //
CREATE FUNCTION `UUID_TO_BIN` (
    _uuid_string VARCHAR(36)
)
RETURNS BINARY(16)
DETERMINISTIC NO SQL
COMMENT 'UUID_TO_BIN(\'29c71680-825c-45cd-8389-1e335e84fa57\') => 29c71680-825c-45cd-8389-1e335e84fa57'
BEGIN
  DECLARE _uuid BINARY(36) DEFAULT 0;
  DECLARE _orig_hex_string VARCHAR(32) DEFAULT NULL;
  DECLARE _swap_hex_strung VARCHAR(32) DEFAULT NULL;
  DECLARE _function INT(1) DEFAULT 0;
  CASE _function
    WHEN 0 THEN
      SET _uuid = UNHEX(REPLACE(_uuid_string,"-",""));
    WHEN 1 THEN
      SET _orig_hex_string = REPLACE(_uuid_string,"-","");
      SET _swap_hex_strung = CONCAT(SUBSTR(_orig_hex_string, 13, 4), SUBSTR(_orig_hex_string, 9, 4), SUBSTR(_orig_hex_string, 1, 8), SUBSTR(_orig_hex_string, 17, 16));
      SET _uuid = UNHEX(_swap_hex_strung);
  END CASE;
  RETURN _uuid;
END;
//
DELIMITER ;