SQLでNano IDを生成したい

結論

WITH RECURSIVE cte AS (
    SELECT 1 AS n,
        SUBSTRING('0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_-',
                  MOD(ORD(RANDOM_BYTES(1)), 64) + 1, 1) AS ch
    UNION ALL
    SELECT n + 1,
        SUBSTRING('0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_-',
                  MOD(ORD(RANDOM_BYTES(1)), 64) + 1, 1)
    FROM cte
    WHERE n < 21
)

SELECT GROUP_CONCAT(ch ORDER BY n SEPARATOR '') AS nanoid
FROM cte

Nano ID

Nano ID は、URLセーフな文字だけで構成された21文字のランダムな文字列です

取り扱いやすさと対衝突性に優れており、弊社ではデータベースにおいて一部のテーブルの主キーやユニークキーに採用しています。

なぜSQLでNano IDの生成の必要が？

弊社でデータベースに何らかのデータを更新する際、いくつか方法がありますが私がよく使うのは次の2つです。

• 社内で作成しているAPIサーバを経由してデータベースを更新する
• BaseMachinaを通してデータベースを更新する

BaseMachinaはローコードで管理画面を作ることができるサービスで、値の入力用のフォームと、その値を使ってどのようにデータベースを操作するかのSQLをセットにしてアクションとして保存しておけます
※ SQL以外にもjavascriptなどの実行も可能です

データベースの更新に際して新しいNano IDが必要になれば、APIサーバは内部でそれを生成して使用することが可能です。

一方でBaseMachina経由で新しいNano IDが必要になった場合、以下の方法のどちらかを取る必要があります

• javascriptなどの実行を前段で行い、Nano IDを生成する
• SQLだけでNano IDを生成しきる

前者でも全く問題ないですが、今回はSQLだけで作業を終えたかったので、SQLでNano IDを生成するようにします

Nano IDの一般的な生成方法

Nano IDの生成自体はシンプルです

https://github.com/ai/nanoid/blob/main/nanoid.js を見に行くと以下のコードで書かれています

let a="useandom-26T198340PX75pxJACKVERYMINDBUSHWOLF_GQZbfghjklqvwyzrict";export let nanoid=(e=21)=>{let t="",r=crypto.getRandomValues(new Uint8Array(e));for(let n=0;n<e;n++)t+=a[63&r[n]];return t};

やっていることは、使用可能な文字列からランダムで1文字選ぶのを21回繰り返しています

Nano IDのSQLでの生成方法

ここまでで Nano ID を SQL だけで生成するための結論のコード例を示しました。
ここからは、実際にこの SQL がどのような流れで組み立てられているのか、各部分がどのように動作しているのかを解説していきます。

使用可能な文字の定義

これは簡単です。数字+大文字小文字のアルファベット+-と_ の64文字です

'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_-'

ランダムに1文字選ぶ

一般的な生成方法と同じように、ここからランダムに1文字選び出すことを考えます

SUBSTRING('0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_-',
          MOD(ORD(RANDOM_BYTES(1)), 64) + 1, 1)

• RANDOM_BYTES(1) これによって1バイトのランダムなビット列が生成されます
• ORD(...) これによってビット列が数値に変換されます
• MOD(..., 64) + 1 これによって、0~63の範囲に数値が落とし込まれます
• SUBSTRING(..., 1) 1文字だけ切り出します

乱数として RAND() を使用することもできますが、RANDOM_BYTES() は可能な限り安全な乱数を生成できるためこちらを利用しています

1バイトは ORD() によって0から255の中のどれかに変更されますが、これは64で割っても余りが偏らないのでちょうど良いです

21回繰り返す

ランダムに1文字選ぶことができるようになったので、あとはそれを21回繰り返すだけです。
繰り返し処理は再帰的に処理することで行います。21行のレコードを作るイメージです

再帰的な共通テーブル式に関してはこちらをご覧ください
https://dev.mysql.com/doc/refman/8.0/ja/with.html#common-table-expressions-recursive

WITH RECURSIVE cte AS (
    -- 1文字目
    SELECT 1 AS n, {ランダムな1文字}
    UNION ALL
    -- 2文字目以降
    SELECT n + 1, {ランダムな1文字}
    FROM cte
    WHERE n < 21
)

1つの文字列にまとめる

ランダムな1文字を持った21行のレコードができたので、最後にそれを1つにまとめれば Nano ID の完成です

SELECT GROUP_CONCAT(ch ORDER BY n SEPARATOR '') AS nanoid
FROM cte

これにより、SQLだけで Nano ID が生成できるようになりました

まとめ

SQLでNano IDを作るために、再帰CTEで21行のランダム文字列レコードを作り、それをgroup concatしました