LoginSignup
1
1

More than 1 year has passed since last update.

@wonder-boooy

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

Last updated at Posted at 2021-03-04

本記事を読むその前に

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

結論

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

スクリーンショット 2021-03-04 16.42.31.png

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

スクリーンショット 2021-03-04 18.25.46.png

何をしたいのか

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

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

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

スクリーンショット 2021-03-04 14.51.43.png

ちなみに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 |
+----+------+---------+----------------------------+----------------------------+

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

スクリーンショット 2021-03-04 16.45.37.png

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

テーブルに登録されたレコードをもう一度よく見てると、
バルクインサートをした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は増やしますぜ旦那、と。
※詳しくはリンク先に飛んで読んでほしいが、該当箇所は以下の通り。

スクリーンショット_2021-03-04_17_39_46.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

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

次のクエリは2つのレコードを登録しようとしている。
最初のレコードは登録できた。これがID 9。
残り1つのレコードは登録出来ず、ID 10も使えなくなった。
スクリーンショット_2021-03-04_17_24_47.png

そして最後に登録されたレコードが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が飛び飛びにならずにバルクインサートできる方法を知っている方！
もしいたらぜひ教えてください😭

1
1
4

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1