バルクインサートでユニーク制約に引っ掛かるとIDが飛び飛びになる問題

# 本記事を読むその前に

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

# 結論

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

![スクリーンショット 2021-03-04 16.42.31.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89868/f3e6a11b-3175-7d6e-df5c-acfef29dd20b.png)

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

![スクリーンショット 2021-03-04 18.25.46.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89868/000200df-c9d4-18d8-f905-ce47a8cab12d.png)


# 何をしたいのか

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

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

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

![スクリーンショット 2021-03-04 14.51.43.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89868/8ad9de4c-bd80-ec7c-582b-036b85360584.png)

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


# やってみる
## 前提

・Rails6系でアプリを作ってある
・バルクインサートには[`insert_all`メソッド](https://railsdoc.com/page/insert_all)を使う
・[activerecord-import](https://github.com/zdennis/activerecord-import)というgemを導入してある

## 簡単なテーブル構造

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

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

```terminal
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:terminal
$ bundle exec rails db:seed
```

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

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

```terminal: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 |
+----+------+---------+----------------------------+----------------------------+
```

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

![スクリーンショット 2021-03-04 16.45.37.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89868/1a8165a2-b977-6055-a7ea-dafc680860d3.png)

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

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

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

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

```sql: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テーブルに対するクエリを確認する。

```sql: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の公式ドキュメント](https://dev.mysql.com/doc/refman/5.6/ja/insert-on-duplicate.html)にしっかりと記載されていた。
ユニーク制約に引っ掛かるデータは登録されないが、IDは増やしますぜ旦那、と。
※詳しくはリンク先に飛んで読んでほしいが、該当箇所は以下の通り。

![スクリーンショット_2021-03-04_17_39_46.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89868/e553600f-1444-ceca-4456-cbbe223d428e.png)

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

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

## つまりこういうこと

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

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

残り3つのレコードは登録出来ず、かつIDも加算されてしまっている。
そのためID 3, 4, 5は使えなくなった。
![スクリーンショット_2021-03-04_17_24_21.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89868/5d4bee5c-36d8-08ff-6cd5-fd8d2a8b0e59.png)

次のクエリは3つのレコードを登録しようとしている。
最初のレコードはユニーク制約を受けず登録出来た。これが**ID 6**になる。
残り2つのレコードは登録出来ず、かつID 7, 8も使えなくなった。
![スクリーンショット_2021-03-04_17_24_35.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89868/60fdd0cf-167a-b961-c436-1333a2855680.png)


次のクエリは2つのレコードを登録しようとしている。
最初のレコードは登録できた。これが**ID 9**。
残り1つのレコードは登録出来ず、ID 10も使えなくなった。
![スクリーンショット_2021-03-04_17_24_47.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/89868/533f1e80-2828-d1a6-8522-968997522b21.png)

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

```terminal: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](https://github.com/zdennis/activerecord-import) も同様

Rails5系で主流だったgem、[activerecord-import](https://github.com/zdennis/activerecord-import) でも同様の事象が発生してしまう。
参考までに [activerecord-import](https://github.com/zdennis/activerecord-import) を使う場合、コードはこのようになる。

```diff: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が飛び飛びにならずにバルクインサートできる方法を知っている方！
もしいたらぜひ教えてください😭