欠番の取得方法

質問

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

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

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

回答No.2

masayuki0511 · ベストアンサー

今、テーブルを 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ですね。ありがとうございました。

回答No.1

vivlet

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

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

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

お礼

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

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