SQLでULIDのIDを持つダミーレコードを大量に作る

TL; DR

delimiter //
create procedure insert_products(n int)
begin
  declare i int default 0;
  while i < n do
    insert into products (id, name) values (concat('DVMMYREC0RD00000', LPAD(i, 10, '0')), 'dummy_product');
    set i = i + 1;
  end while;
end
//
delimiter ;

call insert_products(1000);

はじめに

DBの負荷試験等で大量のデータが必要になった際、ダミーのレコードをまとめて挿入できると便利です。
しかし、IDにオートインクリメントが使えない場合、一意なIDを自前で作る必要があります。

テーブルの例

-- idはULID形式
mysql> desc `products`;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(26) | NO   |     | NULL    |       |
| name  | varchar(40) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

本記事では、ULIDのIDを持つレコードを一括で作成する方法を紹介します。

記事中ではMySQL 8.4.0の実行結果を紹介していますが、他SQLやULID以外の文字列でも応用可能です。

クエリ

プロシージャのwhile文でULIDを一括生成します。

-- `;` で評価が打ち切られないようデリミタを一時的に変更
delimiter //
create procedure insert_products(n int)
begin
  declare i int default 0;
  while i < n do
    -- i番目のレコード
    -- ダミーデータは `DVMMYREC0RD00000` ではじめる
    insert into products (id, name) values (concat('DVMMYREC0RD00000', LPAD(i, 10, '0')), 'dummy_product');
    set i = i + 1;
  end while;
end
//
delimiter ;

-- ダミーレコード1000件追加
call insert_products(1000);

ULIDを DVMMYREC0RD00000{10桁の連番} の形式にすることで、一意な値にしています。また、後でダミーレコードのみ削除できるよう先頭を DVMMYREC0RD という値にしています¹。

-- LPADで0埋め、concatで文字列結合
concat('DVMMYREC0RD00000', LPAD(i, 10, '0'))

最高位の桁は 7 までしか使えない（7ZZZZZZZZZZZZZZZZZZZZZZZZZ が最大）ため、偶然実際のULIDと衝突することもありません。

実装には以下の記事を参考にさせていただきました。

動作確認

想定通り、1000件のレコードが追加されていることが確認できます。

mysql> select count(*) from `products`;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

mysql> select * from `products` order by `id` desc limit 3;
+----------------------------+---------------+
| id                         | name          |
+----------------------------+---------------+
| DVMMYREC0RD000000000000999 | dummy_product |
| DVMMYREC0RD000000000000998 | dummy_product |
| DVMMYREC0RD000000000000997 | dummy_product |
+----------------------------+---------------+
3 rows in set (0.00 sec)

ダミーレコードを消す

続いてダミーレコードの後片付けについてです。元からあったレコードは残し、ダミーレコードのみ削除します。

検証のために、ダミーではない以下の値を挿入しておきます。

mysql> insert into `products` (id, name) values ("01J0FRY514D5PC021H9XN3Z4H8", "desk");
Query OK, 1 row affected (0.01 sec)

mysql> select count(*) from `products`;
+----------+
| count(*) |
+----------+
|     1001 |
+----------+
1 row in set (0.00 sec)

ダミーレコードのprefixに一致するものを削除します。

mysql> delete from `products` where `id` like "DVMMYREC0RD%";
Query OK, 1000 rows affected (0.03 sec)

ダミーレコードのみ削除されました。

mysql> select * from `products`;
+----------------------------+------+
| id                         | name |
+----------------------------+------+
| 01J0FRY514D5PC021H9XN3Z4H8 | desk |
+----------------------------+------+
1 row in set (0.00 sec)

1. ULIDでは I, L, O, U は使用できません。 ↩