本記事を読むその前に

本記事は事象を分かりやすくお伝えするため、サンプルアプリを題材として執筆しております。
実際は大規模システムにおいて大量の初期データを準備する必要があり、
その際に直面した問題から本記事を書くに至りました。
（@jnchitoさん、ご指摘いただきありがとうございます🙇‍♂️）

結論

ユニーク制約のあるテーブルにバルクインサートを繰り返すと
ID（主キー）が飛び飛びになることがある。

⬆︎ mahjan_piesテーブルのIDが飛び飛びに、、、。
⬇︎ こんな結果を期待してたのに。

何をしたいのか

麻雀の座席をランダムに決めるための簡単なプログラムを考える。

麻雀では【東南西北中】の中から1つを選び、引いた牌通りの場所に座る。
【東南西北】は下図のように席に座り麻雀を打つことが出来るが、**中**は見学となる。
（1半荘休み）

そのため、序盤は**中が嫌悪されるが、後半は誰もが中**を懇願することとなる。
（徹麻による体力の限界）

ちなみにusersテーブルに登録する名前は俺の大学時代の友達の名前だ。
麻雀に明け暮れた結果、4人/5人が留年する結果となった🙋‍♂️🀄️🎊

やってみる

前提

・Rails6系でアプリを作ってある
・バルクインサートにはinsert_allメソッドを使う
・activerecord-importというgemを導入してある

簡単なテーブル構造

今回登場するテーブルは2つ。
usersテーブル💁‍♂️とmahjan_piesテーブル🀄️だ。

mahjan_piesテーブルはuser_idを外部キーとして持っている。
また、nameカラムとuser_idカラムはどちらもユニーク制約が設定されている。

usersテーブル
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | bigint       | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | YES  |     | NULL    |                |
| created_at | datetime(6)  | NO   |     | NULL    |                |
| updated_at | datetime(6)  | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+


mahjan_piesテーブル
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | bigint       | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | YES  | UNI | NULL    |                |
| user_id    | bigint       | YES  | UNI | NULL    |                |
| created_at | datetime(6)  | NO   |     | NULL    |                |
| updated_at | datetime(6)  | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

Seedファイルにロジックを書く

席決めのプログラムはseedファイルに記述し、初期データとして登録させる。

これは説明するより見た方が早い。
コードは以下の通り。

db/seeds.rb

def pies
  %w(東 南 西 北 中) - MahjanPie.pluck(:name)
end

def user_ids
  [*1..5] - MahjanPie.pluck(:user_id)
end

def just_now
  Time.zone.now
end

def seat_position
  { name:       pies.sample,
    user_id:    user_ids.sample,
    created_at: just_now,
    updated_at: just_now }
end

User.insert_all([
  { name: 'わたなべ', created_at: just_now, updated_at: just_now },
  { name: 'さかい',   created_at: just_now, updated_at: just_now },
  { name: 'とし',     created_at: just_now, updated_at: just_now },
  { name: 'つとむ',   created_at: just_now, updated_at: just_now },
  { name: 'じゅーり',  created_at: just_now, updated_at: just_now }
])

while MahjanPie.count < 5 do
  MahjanPie.insert_all(
    (5 - MahjanPie.count).times.map { seat_position }
  )

  puts "====== バルクインサート終了... ======"
  puts "座席、もしくは見学が決まっているのは#{MahjanPie.count}人だ"

  return puts "さて、今夜飛ぶのは誰かな？" if MahjanPie.count >= 5

  puts "残る牌は【#{pies.join(' ')}】のみ！"

  sleep(1.5) && puts('...')
  sleep(1.5) && puts('..')
  sleep(1.5) && puts('.')
end

いざ実行

seedファイルを実行するとこのような実行結果が得られた。

terminal

$ bundle exec rails db:seed

terminal

====== バルクインサート終了... ======
座席、もしくは見学が決まっているのは2人だ
残る牌は【東 南 中】のみ！
...
..
.
====== バルクインサート終了... ======
座席、もしくは見学が決まっているのは3人だ
残る牌は【東 南】のみ！
...
..
.
====== バルクインサート終了... ======
座席、もしくは見学が決まっているのは4人だ
残る牌は【東】のみ！
...
..
.
====== バルクインサート終了... ======
座席、もしくは見学が決まっているのは5人だ
さて、今夜飛ぶのは誰かな？

そしてテーブルの中身は以下の通り

terminal

usersテーブル
+----+--------------+----------------------------+----------------------------+
| id | name         | created_at                 | updated_at                 |
+----+--------------+----------------------------+----------------------------+
|  1 | わたなべ       | 2021-03-04 07:37:56.005154 | 2021-03-04 07:37:56.005172 |
|  2 | さかい         | 2021-03-04 07:37:56.005176 | 2021-03-04 07:37:56.005179 |
|  3 | とし           | 2021-03-04 07:37:56.005181 | 2021-03-04 07:37:56.005183 |
|  4 | つとむ         | 2021-03-04 07:37:56.005186 | 2021-03-04 07:37:56.005187 |
|  5 | じゅーり        | 2021-03-04 07:37:56.005190 | 2021-03-04 07:37:56.005191 |
+----+--------------+----------------------------+----------------------------+


mahjan_piesテーブル
+----+------+---------+----------------------------+----------------------------+
| id | name | user_id | created_at                 | updated_at                 |
+----+------+---------+----------------------------+----------------------------+
|  1 | 北   |       2 | 2021-03-04 07:37:56.018787 | 2021-03-04 07:37:56.018797 |
|  2 | 西   |       1 | 2021-03-04 07:37:56.019929 | 2021-03-04 07:37:56.019937 |
|  6 | 中   |       5 | 2021-03-04 07:38:00.541059 | 2021-03-04 07:38:00.541073 |
|  9 | 南   |       4 | 2021-03-04 07:38:05.092544 | 2021-03-04 07:38:05.092582 |
| 11 | 東   |       3 | 2021-03-04 07:38:09.618758 | 2021-03-04 07:38:09.618771 |
+----+------+---------+----------------------------+----------------------------+

つまりこのような席順になった。
（じゅーり、ドンマイ🙏）

これでいよいよ麻雀を打てるわけだが、ちょっと待ってほしい。

テーブルに登録されたレコードをもう一度よく見てると、
バルクインサートをしたmahjan_piesテーブルのidが連続していない🤔
これは一体、、、🤔🤔🤔？？？

発行されたクエリを確認する（SQL）

Railsの開発環境でのログは log/development.log に記載されているので確認する。

log/development.log


INSERT INTO
  `users` (`name`, `created_at`, `updated_at`)
VALUES
  ('わたなべ', '2021-03-04 07:36:05.037216', '2021-03-04 07:36:05.037305'),
  ('さかい', '2021-03-04 07:36:05.037323', '2021-03-04 07:36:05.037334'),
  ('とし', '2021-03-04 07:36:05.037343', '2021-03-04 07:36:05.037351'),
  ('つとむ', '2021-03-04 07:36:05.037359', '2021-03-04 07:36:05.037366'),
  ('じゅーり', '2021-03-04 07:36:05.037374', '2021-03-04 07:36:05.037382')
ON DUPLICATE KEY UPDATE
  `name` = `name`

これはusersテーブルにデータを登録する際のクエリだ。
ちゃんと登録もされている。

次は問題のmahjan_piesテーブルに対するクエリを確認する。

log/development.log


INSERT INTO
  `mahjan_pies` (`name`, `user_id`, `created_at`, `updated_at`)
VALUES
  ('北', 2, '2021-03-04 07:37:56.018787', '2021-03-04 07:37:56.018797'),
  ('西', 1, '2021-03-04 07:37:56.019929', '2021-03-04 07:37:56.019937'),
  ('北', 1, '2021-03-04 07:37:56.020953', '2021-03-04 07:37:56.020958'),
  ('南', 1, '2021-03-04 07:37:56.021981', '2021-03-04 07:37:56.021987'),
  ('東', 2, '2021-03-04 07:37:56.023049', '2021-03-04 07:37:56.023053') 
ON DUPLICATE KEY UPDATE
  `name` = `name`


INTO
  `mahjan_pies` (`name`, `user_id`, `created_at`, `updated_at`)
VALUES
  ('中', 5, '2021-03-04 07:38:00.541059', '2021-03-04 07:38:00.541073'),
  ('中', 5, '2021-03-04 07:38:00.543138', '2021-03-04 07:38:00.543150'),
  ('南', 5, '2021-03-04 07:38:00.546174', '2021-03-04 07:38:00.546210')
ON DUPLICATE KEY UPDATE
  `name` = `name`


INSERT INTO
  `mahjan_pies` (`name`, `user_id`, `created_at`, `updated_at`)
VALUES
  ('南', 4, '2021-03-04 07:38:05.092544', '2021-03-04 07:38:05.092582'),
  ('南', 3, '2021-03-04 07:38:05.094148', '2021-03-04 07:38:05.094162')
ON DUPLICATE KEY UPDATE
  `name` = `name`


INSERT INTO
  `mahjan_pies` (`name`, `user_id`, `created_at`, `updated_at`)
VALUES
  ('東', 3, '2021-03-04 07:38:09.618758', '2021-03-04 07:38:09.618771')
ON DUPLICATE KEY UPDATE
  `name` = `name`

合計で4回のクエリが発行されていることが分かる。
ここでも結論を先に述べてしまうが、 ON DUPLICATE KEY UPDATE によりIDに抜けが出来てしまっているのだ。

ON DUPLICATE KEY UPDATE構文（SQL）

MySQLの公式ドキュメントにしっかりと記載されていた。
ユニーク制約に引っ掛かるデータは登録されないが、IDは増やしますぜ旦那、と。
※詳しくはリンク先に飛んで読んでほしいが、該当箇所は以下の通り。

自動インクリメントカラム、つまりID（主キー）だ。。

ON DUPLICATE KEY UPDATE 構文を使えばユニーク制約が破られることはないが、
ユニーク制約に引っかかったデータの分だけIDが加算されてしまうらしい。。
何てこったい。

つまりこういうこと

各クエリごとに切り分けて見ていく。
最初のクエリでは5つのレコードを登録しようとしている。

ただし、nameカラムとuser_idカラムにユニーク制約がかかっているため、
登録出来たのは最初の2つのレコードだけ。ID 1, 2が振られる。

残り3つのレコードは登録出来ず、かつIDも加算されてしまっている。
そのためID 3, 4, 5は使えなくなった。

次のクエリは3つのレコードを登録しようとしている。
最初のレコードはユニーク制約を受けず登録出来た。これがID 6になる。
残り2つのレコードは登録出来ず、かつID 7, 8も使えなくなった。

次のクエリは2つのレコードを登録しようとしている。
最初のレコードは登録できた。これがID 9。
残り1つのレコードは登録出来ず、ID 10も使えなくなった。

そして最後に登録されたレコードがID 11。
先ほども示したが、その結果がこれになるわけだ。なるほど理解できた。

terminal

mahjan_piesテーブル
+----+------+---------+----------------------------+----------------------------+
| id | name | user_id | created_at                 | updated_at                 |
+----+------+---------+----------------------------+----------------------------+
|  1 | 北   |       2 | 2021-03-04 07:37:56.018787 | 2021-03-04 07:37:56.018797 |
|  2 | 西   |       1 | 2021-03-04 07:37:56.019929 | 2021-03-04 07:37:56.019937 |
|  6 | 中   |       5 | 2021-03-04 07:38:00.541059 | 2021-03-04 07:38:00.541073 |
|  9 | 南   |       4 | 2021-03-04 07:38:05.092544 | 2021-03-04 07:38:05.092582 |
| 11 | 東   |       3 | 2021-03-04 07:38:09.618758 | 2021-03-04 07:38:09.618771 |
+----+------+---------+----------------------------+----------------------------+

activerecord-import も同様

Rails5系で主流だったgem、activerecord-import でも同様の事象が発生してしまう。
参考までに activerecord-import を使う場合、コードはこのようになる。

db/seeds.rb


while MahjanPie.count < 5 do
- MahjanPie.insert_all(
-     (5 - MahjanPie.count).times.map { seat_position }
+ MahjanPie.import(     
+     (5 - MahjanPie.count).times.map { seat_position },
+     on_duplicate_key_ignore: true
  )

さいごに

そもそもユニーク制約に引っ掛かるようなクエリ発行するのがアカンやろ！というご指摘はもっともです。
が！！IDが飛び飛びにならずにバルクインサートできる方法を知っている方！
もしいたらぜひ教えてください😭