空きのID番号を取得する方法

質問

いつもお世話になっております。
create table AAA(
ID int(6) primary key not null auto_increment,
DATA varchar(30),
)engine=MyISAM;
というテーブルにおいて、
insertをすると、IDが1から順に割り振られていきますが、
途中のレコードをdeleteすると、そこが空きのID番号となってしまい、
次にinsertすると、空き番号が残ってしまいます。

やりたいことは、その空き番号を有効活用するために、
auto_incrementを使用せずに、空き番号を適当に取得して、
insertをしたいのです。

何か効率的な良い方法をご存知の方がいらっしゃいましたら、
ご教授いただけると幸いです。
よろしくお願いいたします。

回答No.4

chukenkenkou · ベストアンサー

#2回答者です。

IDの通番を格納した表を使って、差分検索しない場合、どういうSQLで得られるかというと、以下のようなSQLになります。

＜空き番号の内、最小値を求める（その１）＞
select max(ID)+1 as empty_ID
from
(select 0 as ID
union
select ID from AAA) as z
where ID<(
select
min(ID) as ID
from(
select
(select count(*) from AAA where ID<=x.ID) as rownum,
ID
from AAA as x
) as y
where rownum<>ID
);

何をやっているかというと、次のようなことを行っています。

(1)行に通番を付ける
(2)通番とIDが不一致になる最小のIDを求める
(3)IDの最小値未満のデータをunionでくっつけ、最大のID+1を得る

IDの通番を格納した表を使用する方法では、次のようなSQLになります。通番は、ID_tbl表に入っているものとします。

＜空き番号の内、最小値を求める（その２）＞
select min(ID) as empty_ID
from ID_tbl as x
where not exists(select * from AAA where ID=x.ID)
;

差分検索するための通番管理表を作成するのが、面倒とも思えるでしょうが、空き番号を得るSQLが、かなり簡単になります。
また、MySQL 5.0以降では、ストアドプロシジャを使えますから、通番管理表を簡単に作ることも可能です。

(1)管理表の定義
drop table if exists ID_tbl;
create table ID_tbl
(ID int primary key);

(2)通番を格納するストアドプロシジャの定義
drop procedure if exists ID_gen; -- 存在したら削除
delimiter // -- 終端記号の変更
create procedure ID_gen
(in p1stVal int, -- 初期値
in pEndVal int, -- 終了値
in pIncVal int, -- 増分
in pMsgItv int, -- 何行毎に経過メッセージを出力
out pRowNum int
)
-- -------------------------------------------
-- 　多量データ生成
-- -------------------------------------------
begin
declare RowNum int; -- 格納行カウンタ
declare wVal int;
declare fAscDesc smallint; -- 昇順／降順 0：昇順、1：降順
declare cAsc smallint default 0;
declare cDesc smallint default 1;

-- 昇順か降順か
if p1stVal<=pEndVal and pIncVal>=0 then
set fAscDesc=cAsc;
else
set fAscDesc=cDesc;
end if;
--
set RowNum=0;
set wVal=p1stVal;
--
while (fAscDesc=cAsc and wVal<=pEndval) or (fAscDesc=cDesc and wVal>=pEndval) do
insert into ID_tbl
values(wVal);
set RowNum=RowNum+1;
if pMsgItv>0 then
if RowNum%pMsgItv=0 then
select
concat(cast(RowNum as char(5)),
' row insert,wVal=',
cast(wVal as char(5))
) as msg;
end if;
end if;
set wVal=wVal+pIncVal;
end while;
set pRowNum=RowNum;
end;
//
delimiter ; -- 終端記号を元に戻す

(3)ストアドプロシジャの実行
call ID_gen(1, -- 初期値
5000, -- 終了値
1, -- 増分
100, -- 経過メッセージ間隔（行数）
@rownum);
select @rownum;

お礼

chukenkenkou様
複数回にわたり、大変にご親切なご回答をいただきまして、誠にありがとうございました。大変に恐縮しております。
やりたいことも実現できそうです。
ありがとうごうございました。

回答No.6

chukenkenkou

#5さんの言う「実際には削除せず、削除フラグを設ける」というのは、
更新の方が削除より、インデクスなどの影響範囲が狭まるためで、高トラフィック環境等でよく行われる方法です。
しかし、削除フラグを立てただけのデータがいつまでも存在するのも問題で、例えば5年後、10年後にはそういったデータが多量件数になっているかも知れません。インデクスの段数も多くなってしまい、通常の検索の性能も劣化してしまうかも知れません。バックアップやリストアの時間も、長くなってしまいます。
これらを許容でき、よほどディスクスペースに余裕があるのでないなら、いずれ夜間バッチやメンテ日で、削除フラグを立てたデータを実際に消すといった作業が必要になります。

お礼

chukenkenkou様

何度も丁寧なご回答いただきまして、大変にありがとうございました。
大変に勉強になりました。

ありがとうございました。

回答No.5

yambejp

そもそもデータは削除する物ではありません。
削除フラグだけたてて表示しないようにするのが賢明。
つまり「空き番号」は発生しません。

お礼

yambejp様
ご回答をありがとうございました。
ご指摘、ごもっともですね。
別のテーブルでは、削除フラグを立てる方法も利用しようと思います。
ありがとうございました。

回答No.3

Willyt

>このようなことをする設定か何かあると良いなと思うのですが、
いかがなものでしょうか

ですから、その回答をしたつもりです。シリアルファイルではそれは不可能です。その番号までを別のファイルに読み込み、データを入れてからまたその次から読むという格好にしなければなりません。それをしないためにはファイルをランダムファイルにしなければならないのです。

お礼

Willyt様
ご回答をありがとうございました。
RDBというのはランダムアクセスファイルになっているのかと思っているのですが、少しハイレベルな感じで今回はよく理解できませんでした。後学のためにも時間を見つけて勉強しておきます。
ありがとうございました。

回答No.2

chukenkenkou

MySQLのバージョンは何ですか？

空いている番号の内、最小値を使いたいのでしょうか？

一番簡単で安定した性能を出せるのは、1～nの連番を格納した表を作り、その表とAAA表で差分検索する方法です。

補足

chukenkenkou様
ご回答ありがとうございます。
MySQLのバージョンは5.0です。

＞空いている番号の内、最小値を使いたいのでしょうか？
その通りです。

＞一番簡単で安定した性能を出せるのは、1～nの連番を格納した表を作り、その表とAAA表で差分検索する方法です。
なるほどです。MySQL自体にはそういった空き番号の最小値を取得する機能はなさそうな感じですね。こういったニーズはあまり無いものなのでしょうか？

他に何か方法がありそうでしたら、またご教授いただければ幸いです。

回答No.1

Willyt

通常のファイルはシリアルアクセスになっていますから、その大きさは入れたデータの量になります。任意の番号に入れたいならランダムアクセスファイル宣言をすればキーを指定して任意の場所に入力できます。しかし、そのためには例えば１０００番まで割り振るなら予め１０００個分の空き領域を占有することになりますからＨＤがオーバーフローしないように注意して下さい。

補足

Willyt様
早速のご回答、ありがとうございました。
少し私の質問が分かりにくかったかもしれません。
補足させてください。

10件のレコードをinsertすると、
IDは1から順に10まで割り振られます。
例えば3番目のレコードを削除した場合、
次にinsertした場合は、auto_incrementのために、
ID=11のレコードとして記録されますが、
3番がすでに空き番号となっているので、
ID=3のレコードとして記録されて欲しいのです。

このようなことをする設定か何かあると良いなと思うのですが、
いかがなものでしょうか？
よろしくお願いいたします。