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

Question

いつもお世話になっております。
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をしたいのです。

何か効率的な良い方法をご存知の方がいらっしゃいましたら、
ご教授いただけると幸いです。
よろしくお願いいたします。

chukenkenkou · Accepted Answer

#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 · Answer

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

yambejp · Answer

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

Willyt · Answer

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

chukenkenkou · Answer

MySQLのバージョンは何ですか？

空いている番号の内、最小値を使いたいのでしょうか？

一番簡単で安定した性能を出せるのは、1～nの連番を格納した表を作り、その表とAAA表で差分検索する方法です。

Willyt · Answer

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

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

#2回答者です。

#5さんの言う「実際には削除せず、削除フラグを設ける」というのは、

そもそもデータは削除する物ではありません。

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

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

この回答への補足

通常のファイルはシリアルアクセスになっていますから、その大きさは入れたデータの量になります。

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング