MySQLでauto_incrementを使う時には要注意

これもさっきと同じで、楽しようとして痛い目にあったと言うことで。

AUTO_INCREMENT の使用

AUTO_INCREMENT 属性を使用すると、新しく追加するレコードを識別するための一意な値を生成できます。

まあ、簡単にIDを生成できるんだーと軽いキモチで。

でも、これに最近気付いて。

注意: この場合（AUTO_INCREMENT カラムが複合インデックスの一部として使用されている）、グループ内で最大の AUTO_INCREMENT 値を持つレコードを削除すると、そのグループで同じ AUTO_INCREMENT 値が再使用されることになります。これは MyISAM テーブルの場合にも発生する可能性があります（通常は AUTO_INCREMENT が再使用されることはない）。

つまり、テーブル内では一意のIDであることは保証されても、そのテーブルを取り巻くシステムとしてみた場合、IDが同一の異なるインスタンスが存在することになる訳で。ユーザIDをイメージした場合、最新のIDを持っているユーザのレコードを削除した後に、今度は別のユーザが登録されると、その時点ではそのIDを有しているユーザは１人ですが、削除されたユーザと同一と見なされてしまうんですよね。って、分かりにくい説明ですね。別の人に同じIDを発行してしまう可能性があると言うことで。

で、厳密に一意なIDを取るには、Oracleで言う所のシーケンスが必要になる訳です。とは言え、ネイティブのMySQLでは、シーケンスはサポートされていない模様。となると、ID用の一カラム、一レコードのテーブル作って、それに対して、updateしてくのかー、って思った時に。

導入 - シーケンス

シーケンスは、データレコード用にユニークな ID を取得する手段です。 多くの業務を例えば MySQL で行っている場合、シーケンスは、 AUTO_INCREMENT を行う別の手段と考えであると考えて下さい。

シーケンス処理はかなり簡単で、最初に ID をリクエストし、 作成する新規レコードの ID フィールドに値を挿入します。 全てのテーブルに複数のシーケンスを有することが可能です。 ただし、特定のテーブルについては 同じシーケンスを常に使用する必要があることに注意して下さい。 このユニークな ID の値を取得するには、nextId() を使用してください。 もし、シーケンスが存在しない場合は、自動で作成されます。

上で行ったことをラップして、作成、削除、採番までやってくれると。やるな、PEAR::DB。