欠番の取得方法

いつも参考にさせて頂いています。
MySQL(OS:Windows)でDBを作っていますが、ある問題で詰まってしまいましたので識者の方の意見を伺えればと考えました。

問題は、インデックス(1で開始され、あとは+1ずつ増加)をキーとしたデータが登録されているテーブルがあります。
このテーブルのデータは削除・追加が行われます。削除の際は当然そのインデックスが欠番となり、追加時は欠番があればそちらを使用したいのですが、欠番を求める巧いSQLが思い浮かびません(パッと考え付くのは欠番テーブルを作る事位です…)。

如何せんDB・SQL経験が浅いためDB設計の考え方がおかしいのかもしれません。この点も含め上記問題の解決方法のヒントを教えて頂ければ幸いです。

No.2 ベストアンサー 回答者： masayuki0511 回答日時： 2005/10/11 18:39

今、テーブルを t 、インデックスを i とします。

このとき、
次のSQLで、最小の欠番を取得できます。

SELECT MIN(T0.i) + 1 FROM t AS T0 LEFT JOIN t AS T1
ON T0.i + 1 = T1.i WHERE T1.i IS NULL;

これは、4.0でも動きます。

この回答へのお礼

masayuki0511さん遅くなってしまいましたが、回答ありがとうございます。
しかも丁寧なSQL、対応バージョンまで…ひたすら感謝です。
LEFT JOINは不慣れだったためパっと出てきませんでしたが、知人に聞いてどう動くのかが判りました。ちょっと変えると欠番の最大値も求められる便利なSQLですね。ありがとうございました。

お礼日時：2005/10/12 12:44

No.1 回答者： vivlet 回答日時： 2005/10/11 17:08

私がぱっと考えて一番単純なのは、削除時はDELETEでなく削除フラグを立てて、新規時に削除フラグの立っている小さい順にUPDATE か DELETE＆INSERT かな？

select min(インデックス) from table where 削除フラグ
レコードが取得できれば 取得したインデックスを使用。
レコードが取得できなければ MAX+1 で登録。

同じような処理をやってるとこも多そうなので、実際にやったことある人の意見を聞いた方が良いかもですね。

この回答へのお礼

vivletさん、回答ありがとうございます。

なるほど削除時にDELETEしない方法は気づきませんでした。発想の転換が必要だったことを思い知ります。MySQLで差集合をとる方法を必死で考えていた5分前の自分に教えてやりたい位です(^^;

お礼日時：2005/10/11 18:09