【MySQL】auto_incrementの罠

Question

以下のテーブルを作り

CREATE TABLE IF NOT EXISTS users(
	user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_name VARCHAR(30) NOT NULL
);

以下の情報を追加しました。もちろん、auto_incrementをつけてるので問題なく1～4のidがuser_idカラムに追加されましたが、

INSERT INTO users(user_name) VALUES('name01');
INSERT INTO users(user_name) VALUES('name02');
INSERT INTO users(user_name) VALUES('name03');
INSERT INTO users(user_name) VALUES('name04');

例えば、name04を消し、

DELETE FROM users WHERE user_id=4;

新たに情報を追加したときに、

INSERT INTO users(user_name) VALUES('name05');

結果がuser_idに4追加されたと思いきや、5になってしまってます。

○質問
上記のようにuser_idの情報が5にとぶのはなぜなのか、また解決策があれば、ご教授お願いします。
※idが5にとぶのではなく、最初みたいに1,2,3,4みたいに割り当てて欲しいです。

Answer 1

解決策は、「AUTO_INCREMENTを使うのをやめるか、飛び番を気にしない」ことです。

データベースの主キーは、単にレコードを識別するためのものであって、「それぞれの行で一意である」以上に意味をもたせるのは良くないことです。

そして、MySQLの場合、データを削除しなくても値が飛ぶことがあります。

「IDが連番でないと気持ち悪い」という気持ちもわからないではないですが、「SQLアンチパターン」の第21章に挙げられているように、それを守るために発生する余計な手間・コストが大きく、実用的にはやるべきでない行為です（何かしらの処理が、「キーが連番であること」に依存しているなら、そちらを修正する必要があります）。

Comments

[退会済みユーザー]

そうなると、おそらくidを直接指定する形になりますね。ありがとうございます。

Answer 2

上記のようにuser_idの情報が5にとぶのはなぜなのか

それがauto_incrementの仕様だからです。
auto_incrementは現時点のデータとは別に、次に追加されるIDを記憶しています。
参考

なぜそういう実装になっているかの歴史的経緯は知りませんが、
例えばuser_idの場合、削除して次に挿入したら同じIDになると問題が出る可能性があります。
例えば別のテーブルで、user_idと紐づける形で住所を保存していたりした場合、他人のデータと結びつくことになってしまう可能性があります。

また解決策があれば、ご教授お願いします。

Auto_incrementの値は指定することが可能
ですが、これを日常的に使うくらいなら、
auto_incrementを指定せずに、
テーブルの中で一番大きなIDの値を取得して、それに1を足して指定するべきIDを生成してデータを挿入する
という形にするのが一般的な方法かと思います。

Comments

[tanat]

とは言え、上記の様な問題が発生する可能性があるので、
お勧めの解決方法としては
maisumakunさんの仰る通り
> 解決策は、「AUTO_INCREMENTを使うのをやめるか、飛び番を気にしない」ことです。
です。

[退会済みユーザー]

わかりました。ありがとうございます。

Answer 3

手前味噌になりますが、以前このような記事を書きました
DBの自動連番がロールバックしても戻らない理由

Comments

[退会済みユーザー]

ありがとうございます。参考にさせていただきます。

Answer 4

参考情報：

• MySQLでInnoDBエンジンを使うとAUTO INCREMENTの番号が飛ぶ現象 http://monochrome-design.jp/195

Comments

[退会済みユーザー]

ストレージエンジンのデフォルト確かmyisamでしたよね？
ストレージエンジン切り替えは行ってなかったです。
innoDBならありだと思ってたんですが、やはり同じでしたか…
ご回答ありがとうございます。