GEEK JOB(ギークジョブ)でJavaを学習中の忘備録。
JSPとサーブレットの連携でMySQLにデータをinsertしたりupdateしたりdeleteしたり、そんなことを繰り返して色々と勉強してます。
そんな時にどうしても気になってしまう、AUTO_INCREMENTで主キーに指定した番号がdelete→insertを繰り返すごとに虫食い状態になっていく問題。
↓userID : 4 をdelete
↓再度insertすると 4 が欠ける
多くの人が同じ疑問を抱えていますね。
stack overflowに投稿された質問 : Auto Increment after delete in MySQL
teratailに投稿された質問 : 【MySQL】auto_incrementの罠
しかし、結論から言うとほとんどの場合この虫食い状態になるのは気にする必要はないようです。
主キーは「一意」であることに意味がある
他の行と重複がないこと(その行1つだけを特定できること)が大事なのであって、番号がキレイに並んでいることには機能的な意味はない、と覚えておけば良いようです。
以下、先ほど紹介したteratail(テラテイル)に投稿された質問の回答が分かりやすかったので引用します。
解決策は、「AUTO_INCREMENTを使うのをやめるか、飛び番を気にしない」ことです。
データベースの主キーは、単にレコードを識別するためのものであって、「それぞれの行で一意である」以上に意味をもたせるのは良くないことです。
「IDが連番でないと気持ち悪い」という気持ちもわからないではないですが・・・それを守るために発生する余計な手間・コストが大きく、実用的にはやるべきでない行為です（何かしらの処理が、「キーが連番であること」に依存しているなら、そちらを修正する必要があります）。
気になるAUTO_INCREMENTの上限
でも、deleteとinsertを繰り返していくと、どんどんその数字は増し続けていくことになります。
そこで気になったのが「頻繁に会員登録とか削除とかが繰り返され、ユーザーもものスゴイ大量にいるような場合」は上限を心配する必要はないの？ということ。
そういった膨大な数の番号が一意に必要になる場合は気をつける必要があります。
int型の場合、2147483647(約21億)が上限です。例えばFacebookの利用者数は20億を超えるそうなので、ユーザーに対し一意で振れるIDは底を尽きそうなことが分かりますね。
そんな時はbigint型にすると良いようです。最大値は9223372036854775807。デカすぎて初めてリアルで目にした「約922京」・・・。
まとめ
JSP + サーブレット + MySQLの組み合わせの初心者レベルで勉強しているとこの「AUTO INCREMENTで設定したら主キーの番号が虫食いになって気持ち悪い」現象を体験しやすいのではないかと思います。
でも、気にしない、ということで解決なので次に進みましょう！
GEEK JOB(ギークジョブ)：無料でJava, PHP, Rubyを学習してみる
