AUTO_INCREMENTの動作について

質問

mysqlデータベースのAUTO INCREMENT値に関する質問です。

一時的なテーブルにデータを格納し、必要な操作が終わったらほかのテーブルに移し、一時的なテーブルの中のエントリを削除する設定なんですが、AUTO_INCREMENTは30まで登りましたが、29以下のエントリは既に削除しています。そうすると、新しいエントリを挿入すると、信じられないことに、31を識別子として挿入してくれなくて、15挿入されてしまいました！

データを移すときに、その番号を識別子として使いますので大問題になっています。それはAUTO_INCREMENTの正常な動作ですか？？避けることはできるのでしょうか？

詳しい方がいらっしゃいましたら、ご回答をよろしくお願いいたします。

回答No.1

yambejp · ベストアンサー

＞信じられないことに、31を識別子として挿入してくれなくて、15挿入されてしまいました！

auto_incrementはデータを削除しても戻らないのはわかりますね？
また、auto_incrementは競合しなければ数値を指定して挿入できます。
当然auto_incrementのクリアもできます
それを踏まえてこんな感じでわかりますか？

(1)create table hoge(id int not null primary key auto_increment,data varchar(10));
insert into hoge (data) value('a'),('b'),('c'),('d');
select * from hoge;

(2)delete from hoge;
insert into hoge (data) value('e'),('f'),('g'),('h');
select * from hoge;

(3)delete from hoge;
insert into hoge (id,data) value(1,'i'),(2,'j'),(null,'k'),(null,'l');
select * from hoge;

(4)delete from hoge;
alter table hoge auto_increment=1;
insert into hoge (data) value('m'),('n'),('o'),('p');
select * from hoge;

お礼

ご回答ありがとうございます。
やっぱり戻らないはずですね。エラーの原因は、たぶんDELETE文が正しく実行されなかったんじゃないかと思います。

回答No.2

tomaju

削除はどのように行っていますか?
TRUNCATE でデータを削除すると、AUTO_INCREMENT の値がリセットされるとのことです。
DELETE ではリセットされません。

http://www.dbonline.jp/mysql/insert/index12.html

手元の MySQL 5.0 ではそのような動きになりました。

お礼

ご回答ありがとうございます。
TRUNCATEは実行していませんが、今度TRUNCATEを利用すればこれも考える必要がありますね。