テーブルの自動キー再割り当て

質問

以下sqlでテーブルhogeを作成し、idを自動キーにしています。
CREATE TABLE `hoge` (
`id` INT NOT NULL AUTO_INCREMENT,
........(省略)..........
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=sjis;

このテーブルのレコード番号4を削除するとテーブルのidは 1,2,3,5(最終)になり, 新しいデータを追加(INSERT)するとidは 1,2,3,5,6(最終)とid番号4が欠番になります。
テーブルのidを1,2,3,4,5と続き番号に再割り当てして、新しいレコードがid番号6の挿入されるようにするにはどのようにすればよいでしょうか?
sql文をご教示い頂きたくよろしくお願いします。

回答No.4

chukenkenkou

失礼。勘違いしていました。「空きの番号を再利用」でなく、番号を連番で付け直したいと言っていたのですね。

#3で書いたLIMIT句ですが、次のような使い方をします。

SELECT *
FROM t1
[WHERE 検索条件}
ORDER BY id
LIMIT m,n

LIMIT m,nで、「m+1行目からn行を得る」という意味になります。

お礼

LIMITの有用な使い方をありがとうございます。
プログラミング(PHP)側でSELECTレコードセットのidを配列に取得する方法や次のレコードにseekする方法も考えましたがもう一つぴったりしませんでした。
なるほどLIMITを使えばsql側で色々応用が可能ですね。参考にさせて頂きます。ありがとうございました。

回答No.3

chukenkenkou

削除自体というよりは、番号を再利用することが前提なのに、削除するというのは非効率的です。他にもいろいろ理由はあるのですが、今回の質問と直接関係しないので省略します。

>色々試みましたが、結局以下のようにidを削除した後、再びidを追加して
>連番を振り直しました。
>ご指摘のようにこれからは出来るだけレコードを削除しないようにしたい
>と思います。
>ALTER TABLE `hoge` DROP `id`;
>ALTER TABLE `hoge` ADD `id` INT UNSIGNED NOT NULL AUTO_INCREMENT
> PRIMARY KEY FIRST ;

マニュアルにも明記されていますが、MySQLのALTER TABLEでの列追加は、全行のコピー（INSERT）が発生しますよ？？？

>この様な必要が生じたのはidをインクリメント(デクリメントして)
>レコードを次々ページングする処理をしているため

idの付け直しとかでなく、SELECT文でLIMIT句を使えば解決するような話しにも思えますが？

回答No.2

yambejp

＞DELETE文で削除するのはよく行われると思う

そんなことはありません。
きちんとしたデータ管理をする場合は、削除というのはほとんど行いません。
削除したというフラグを立てておき、SELECTの際に表示しないというのが正しい
やり方です。
それはデータの履歴性の問題です。

もちろん運用の仕方によっては削除することもおかしいとはいえません。
ただ「連番」という意味のないことのためにいちいち各データの
項目を書き換える＝連番をつけなおすことは全うなやり方ではありません。

どうしてもやりたいなら連番専用の外部テーブルを作成しておき
プライマリキーをもとに付け直す処理が必要になります。

お礼

コメントありがとうございます。
色々試みましたが、結局以下のようにidを削除した後、再びidを追加して連番を振り直しました。
この様な必要が生じたのはidをインクリメント(デクリメントして)レコードを次々ページングする処理をしているためです。
ご指摘のようにこれからは出来るだけレコードを削除しないようにしたいと思います。

ALTER TABLE `hoge` DROP `id`;
ALTER TABLE `hoge` ADD `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ;

回答No.1

chukenkenkou

MySQLでのauto_incrementだけでは実装できないので、そういったSQLを作る必要があります。
MySQLのバージョンは、何でしょうか？
MySQL 4.0以前、4.1、5.0以降かによって、実装方法が大きく違ってきます。

なお、削除したり、空いている番号を探すというのは、大きなオーバヘッドになりますよ？

補足

レスありがとうございます。
MySqlのバージョンはMySQL Server 5.0 (for Windows)です。
また次の意味がよくわかりません。DELETE文で削除するのはよく行われると思うのですが、時間がかかるという意味でしょうか？
あわせて追加ご教示頂ければ幸いです。よろしくお願いします。

>なお、削除したり、空いている番号を探すというのは、大きなオーバヘッドになりますよ？