Zenn
🎉

MySQLでULIDをPRIMARY KEYとして自動採番する - データベースのID設計個人的まとめ

2025/01/05に公開
MySQL
MySQL 8
id設計
tech

前提

  • 趣味の開発でデータベーススキーマをどう設計するか考えた記録
    • なので、ユーザー情報の機密性が高すぎず、またデータベースのパフォーマンスも極端に最適化されたりしている必要がないと考えられるケースを想定している（個人開発プロダクト＋α、ぐらいのイメージ）
  • MySQLでのテーブル定義におけるプライマリキーの設計指針、に限定して考えている
    • 検証したバージョンは8.0.40だけど8.4系でも5.7系でもたぶん同じように動作する

TL;DR

ULIDをIDとする。TRIGGERを使ってMySQLに自動生成させることで、 AUTO INCREMENT した PRIMARY KEY みたいな感じで使える。

IDはどんな形式にするのがいいのか

PRIMARY KEY なんて AUTO INCREMENT しとけばいいのか？

PRIMARY KEY なんて AUTO INCREMENT しとけばいいじゃん、というレベルがスタート地点。

create_users.sql
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    -- ここから下はオマケ
    username VARCHAR(255) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6)
    updated_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
);

このようにテーブル設計をした場合、ユーザーのIDを用いたURL設計をしてしまうと、会員数の推測がしやすかったり、当該ユーザーがどの程度古株のユーザーなのかなど、本来は晒さなくていい情報が一目でわかってしまう。

というわけで、連番のIDってあんまり良くないよね、というところから、以下の選択肢を検討することになる。

代替案：UUID(v4/v7), ULID, またはそれ以外

ここで代替案として期待されることは、

  • なるべく意味のない文字列であり、ユーザーに晒すデメリットがない、または少ないこと
  • ソート可能であり、できれば高速なこと
  • データベースの容量をなるべく圧迫しないこと
  • AUTO INCREMENTと同等の運用可能性があること

あたり。候補としては一般的な選択肢がすでにあるため、ぼくのかんがえたさいきょうのランダム文字列などを採用しない限り、ほぼ以下の3択から選べば良いと思う。

どれも天文学的なレベルで[1]衝突確率の低いランダムな文字列であり、UUID v4を除けばソート可能なように設計されている（タイムスタンプを変換して組み込んでいるため時系列順に並ぶ構造になっている）。

種類 データ型 ソート可否 サンプル
UUID v4 CHAR(36) × b669ba8b-ec4c-4f7a-84c7-34e518aa070c RFC
UUID v7 CHAR(36) ⚪︎ 0194353a-e40c-7973-8e28-e0768b7cbe0a RFC
ULID CHAR(26) ⚪︎ 01JGTKRH652WFM58EQKR7DKA2M Spec

ちなみにこの観点では、こちらの記事の方が質が高いです

UUIDとULIDを理解していない方は見た方がいい記事

頑張ればTIMESTAMPわかるぐらい良くない？ なのかどうか

これは決めの問題で、自分はサービスの性質にもよるがぱっと見でわからなければいいんじゃないかと考えているので、普段使いのシステムではUUID v7またはULIDをIDとして晒してしまってもいいという考え方。

なので結論としては、 ULID を PRIMARY KEY として採用することにした。

よりストイックに考えるのであれば、こちらの記事のように、そもそもユーザーに晒す文字列としては完全にランダムな（それこそUUID v4のような）文字列を別カラムで保持しておいて、プライマリキーはAUTO INCREMENTする、でも良いと思う。

プライマリキーにUUID v7/ULIDを使うか問題について

記事内にもあるけどプライマリキーの検索の高速性はさすがに数値型が最速なので、2列データがあってだるい、間違って連番のIDを使ってしまうヒューマンエラーのリスクがある、とか以外に実際のところ大きなデメリットはない。きちんとやりたい場合は個人的にもこちらが推奨かも。

自動でULIDを採番させたい（アプリケーションで生成・管理したくない）

ということで、テーブル定義はこうなった。

create_users.sql
CREATE TABLE IF NOT EXISTS users (
    -- 必ずULIDが入る場合固定長なので、VARCHARではなくCHARでよい
    -- PRIMARY KEYはデフォルトでNOT NULL制約がつくので明記しなくてよい
    id CHAR(26) PRIMARY KEY,
    -- ここから下はオマケ
    username VARCHAR(255) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6)
    updated_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
);

しかし AUTO INCREMENT で得られていた大きな恩恵である自動採番が失われてしまったので、手作りする。

ULIDをMySQL内で作らせる

MySQLでもULIDを発行した〜い！ので検証してみた

こちらの記事でMySQLの stored function を使ってULIDを生成する方法を紹介してくれているので、これを採用してIDとして自動で入力されるようにする。
※ここから先はパフォーマンスがいいかどうかは検証できていないので、パフォーマンス効率などが求められるプロダクトで採用していいかは各検証して判断してもらえたらと。

まずはULIDを生成する stored function を登録する[2]。

function_gen_ulid.sql
DELIMITER //
CREATE FUNCTION to_crockford_b32 (src BIGINT, encoded_len INT)
RETURNS TEXT DETERMINISTIC
BEGIN
  DECLARE result TEXT DEFAULT '';
  DECLARE b32char CHAR(32) DEFAULT '0123456789ABCDEFGHJKMNPQRSTVWXYZ';
  DECLARE i INT DEFAULT 0;

  ENCODE: LOOP
    SET i = i + 1;
    SET result = CONCAT(SUBSTRING(b32char, (src MOD 32)+1, 1), result);
    SET src = src DIV 32;
    IF i < encoded_len THEN
      ITERATE ENCODE;
    END IF;
    LEAVE ENCODE;
  END LOOP ENCODE;

  RETURN result;
END; //

CREATE FUNCTION gen_ulid ()
RETURNS CHAR(26) NOT DETERMINISTIC
BEGIN
  DECLARE msec_ts BIGINT DEFAULT FLOOR(UNIX_TIMESTAMP(CURRENT_TIMESTAMP(4)) * 1000);
  DECLARE rand CHAR(20) DEFAULT HEX(RANDOM_BYTES(10));
  DECLARE rand_first BIGINT DEFAULT CONV(SUBSTRING(rand, 1, 10), 16, 10);
  DECLARE rand_last BIGINT DEFAULT CONV(SUBSTRING(rand, 11, 10), 16, 10);
  RETURN CONCAT(
    to_crockford_b32(msec_ts, 10),
    to_crockford_b32(rand_first, 8),
    to_crockford_b32(rand_last, 8)
  );
END; //

DELIMITER ;

登録が完了すると以下のように生成できるようになる。

mysql > gen_ulid();
01JGS8Q21SC2VX15MQPK7VXJ9T

Dockerの検証環境で stored function を使いたい人のための補足

今回 docker compose で検証環境を立ち上げているが、以下のように MYSQL_LOG_BIN_TRUST_FUNCTION_CREATORS: "1" を指定する必要があった。

compose.yaml
services:
  mysql:
    image: mysql:8.0.40
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: sandbox
      MYSQL_USER: fuji
      MYSQL_PASSWORD: password
      # この指定を入れた上で、rootユーザーで接続しないとstored functionは作れない
      MYSQL_LOG_BIN_TRUST_FUNCTION_CREATORS: "1"
    ports:
      - "3306:3306"
    volumes:
      - sandbox_mysql_docker_data:/var/lib/mysql
volumes:
  sandbox_mysql_docker_data:

TRIGGERを使ってULIDをIDに自動的に書き込ませる

テーブル定義のDEFAULTにそのまま使えないかと思ったが、そういうわけにはいかないらしいので、MySQLのTRIGGERという機能を使う。

TRIGGERはINSERT/UPDATE/DELETEを契機に処理を噛ませられる機能だ。

今回は以下のような指定を入れる。

trigger_before_insert_users.sql
DELIMITER //
CREATE TRIGGER before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  IF NEW.id IS NULL THEN
    SET NEW.id = gen_ulid();
  END IF;
END; //
DELIMITER ;

こうすることで、データ生成時に自動的にULIDを入れてくれるようになる。

なお、TRIGGERには開発者から実装が見えにくくなるなどのデメリットがあり、今回の書き方だとテーブルごとにこの記述を書かないといけないので必ずしも手間がゼロになっているわけではないことを注意してほしい。

参考: MySQL Triggerのデメリットが大きいので使わないで欲しい

まあ一回設定してしまえば AUTO INCREMENT の PRIMARY KEY と使い勝手はほぼ一緒だと個人的には思ったのと、テーブル定義文にTRIGGERのことをコメントでつけておけば見失うことはないかな、と思ったので採用しても良いと判断した。

脚注

  1. 天体同士は衝突しまくってるのでこの例えは筋が悪いかも。真面目に衝突確率を知りたい人はこの辺の記事が参考になります→UUIDを重複させるにはどれだけ時間がかかるのか試してみた ↩︎

  2. 中身の説明はMySQLでもULIDを発行した〜い！ので検証してみたにあるのでご覧ください！ ↩︎

Discussion