MUGIJIRU.JP

Webエンジニアの雑談ブログ

MySQL InnoDB Auto Increment

Auto Incrementは、Primary Keyのオプションです。
insertしたときに、自動的に増えていくアレです。

今回は、InnoDBにおけるこのAuto Incrementについて
認識しておいたほうがいいことをいくつか紹介します。

1. Auto Incrementは、原則1ずつ増える

id name
1 いちご
2 みかん

ここに、「りんご」をidの指定なしで追加するとidは自動的に3となります。

id name
1 いちご
2 みかん
3 りんご

※いくつずつ増えるかは、実は設定で変更できますが、めったに使わないのでここでは触れません。

2. Auto Incrementは、最大値 + 1ずつふえる

id name
1 いちご
2 みかん

ここに「ぶどう」をid = 5で追加すると、idは5になります。

id name
1 いちご
2 みかん
5 ぶどう

ここで、idの指定なしで「りんご」を追加すると、idは自動的に6となります。
3と4が空いていますが、ここを埋めるような動きはしません。

id name
1 いちご
2 みかん
5 ぶどう
6 りんご

3. Auto Incrementの最大値は、起動中は削除の影響を受けない。原則メモリが持っている。

id name
1 いちご
2 みかん
5 ぶどう

ここで、5のみかんを物理削除します。

id name
1 いちご
2 みかん

この状態で、idの指定なしで「りんご」を追加すると、idは自動的に6となります。
1,2しかありませんが、最大値はテーブル自体が持っているということです。

id name
1 いちご
2 みかん
6 りんご

4. テーブル自体が持っているAuto Incrementの値は、サーバの再起動でリセットされる!

これが要注意です!再起動が挟まると、3. の動きが変化します。

id name
1 いちご
2 みかん
5 ぶどう

ここで、5のみかんを物理削除します。

id name
1 いちご
2 みかん

ここで、サーバを再起動してから、idの指定なしで「りんご」を追加すると・・・
idは、6になってほしいところですが、なんと3になります。

id name
1 いちご
2 みかん
3 りんご

これはヤバい。(知らないと)
ユニークである必要があり、外部キーとして重要な意味を持つデータとして使われている場合は
予期しない動きを誘発することになるでしょう。

何この動き。

MySQL8.0で修正されたそうです。
5.7まではこの動きをします。要注意!

対策の候補

あまりありません。
・8.0にする
・外部キー制約を使う
レコードが物理削除されるテーブルではAuto Incrementに頼らない設計、実装にする

サーバの再起動のタイミングは厳密にコントロールできるものではありませんので
性質を理解して使う必要がありますね。要注意です!