phpMyadminのエクスポートでのAUTO_INCREMENTって何？

phpMyAdminでエクスポートする画面で選べるオプションの「AUTO CREMENT」とは何か気になったので調べてみました。

エクスポートで指定するオプションで迷う

phpMyAdminでエクスポートする際に表示される「AUTO CREMENT」というオプションがあります。これを指定すべきかどうか迷いました。

このオプションがチェックされたものとされてないもののSQLを比べてみると、以下のCREATE文の最後の行が違いました。

CREATE文が長くて全部は載せられないのですが、上はCREATE文の最後の部分です。チェックの有無で、 AUTO_INCREMENT=1177865 の有無が決まります。

テーブルに指定されてるAUTO_INCREMENTって？

CREATE文の最後にあることから、テーブル全体に指定されているようですが、調べてみるとAUTO_INCREMENTを指定できるカラムはテーブルにつき一つです。

MySQLのAUTO_INCREMENTについて

前述したテーブルだと「idvisit」というカラムにAUTO_INCREMENTが指定されています。 AUTO_INCREMENT=1177865 は、テーブル全体に指定したように見えてもこのidvisitに指定した数字ということです。実際、この時点のこのテーブルの最終行のidvisitの値は1177864となっています。

何を意味する数字かというと、次にINSERTされる際に「idvisit」が指定されていない場合、1177865が入るということです。

ちなみに、これが指定されていなくても、削除された途中の欠番が新たに割り振られることはありません。小さくても既存行の最大値より1大きい値が入ります。

エクスポートだから基本的に全カラムに数字が入っているはずでは？

とはいえ、エクスポートなので、CREATE文に続いてデータの中身となるINSERT文が続きます。そのINSERT文には当然全てのカラムの値が入っているので、 AUTO_INCREMENT=1177865 の指定は意味の無いように思えます。

色々調べてみると、以下のページでわかりましたが、何らかの理由で最終行が削除されている場合に違いが生まれるようです。

Meaning of auto increment and triggers for exporting

例えば、 最終行が1177864の時に、次は1177865になるはずです。しかし、この状態で1177864の行が削除されたとします。次にINSERTされる値はメモリー上に保持されているとのことです。なので、MySQLがずっと起動していれば問題ないのですが、例えばもしMySQLが再起動されてしまった場合、1177865ではなく1177864が挿入されてしまいます。再起動ではなくても、エクスポートするということは、別の環境のMySQLにインポートするということなのでメモリーの内容は維持されない状況のはずです。

それでも少なくとも重複してないんだから問題ないじゃん、と思うかもしれません。しかし、このキーを使って他のテーブルと結びつけている場合、他のテーブルは違う行と結びつけてしまうことになります。具体的には以下の記事で挙げられているような問題も起きかねません。

MySQLのAUTO_INCREMENTで値が再利用されるケースがある

簡単に思いつく事例としては、ユーザー削除です。最も最近に登録したユーザーを削除して、そのユーザーに紐づく情報が他のテーブルに残ってしまっている状態です。この状態で、当該オプションを指定せずにエクスポートし別の環境にインポートし、その環境で新しいユーザーを登録したら、他のテーブルに残ってしまっていた情報がその新しいユーザーに紐づいてしまうということです。

それに対して、オプションを指定したら、ユーザーを削除したとしてもメモリー上にある次に採番すべき値をエクスポート時に AUTO_INCREMENT=で指定してくれます。なので、インポート後に新しいユーザーを登録しても、他のテーブルに残った削除したユーザーの情報に紐づいてしまうことはありません。

つけておいた方が無難

削除なんて発生しないっていうシステムならこのオプションはつける必要はないでしょう。

また、気の効いたシステムなら、物理削除ではなく仮想削除しかしないようにしていたり、何かの行を削除したら別のテーブルにあるそれに紐づく行も削除してくれるので、意図しない紐付きが発生する可能性も低いです。

でもそこまで考えられていないシステムもあるので、つけておいて損はないでしょう。