裏MySQL 歯抜けになったプライマリキーを採番しなおす。

まずはサンプルのテーブルの定義

CREATE TABLE `item` (
`item_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`item_name` VARCHAR( 255 ) NOT NULL
) ENGINE = IONNODB ;

INSERT INTO item (item_name) VALUES 
('みかん'),
('リンゴ'),
('バナナ'),
('トマト'),
('オレンジ');

データを削除する

DELETE FROM item WHERE item_id=4;

採番しなおす

SET @N:=0;
UPDATE item SET item_id=@N:=@N+1 
ORDER BY item_id ASC

ユーザー変数を使う場合は、並び順が重要になってくる、ORDER BY item_id ASC が肝
指定しなくても、成功する場合もあるが、実行内容が保障されない。

SET @MAX:=(SELECT MAX(item_id) FROM item)+1;
SET @SQL=CONCAT('ALTER TABLE item AUTO_INCREMENT = ',@MAX);

PREPARE init_auto_inc FROM @SQL;
EXECUTE init_auto_inc ;

オートインクリメントの内容は修正されない為、MAX値+1に修正する。
ALTER文はDDLの為プリペアで動的に実行

尚、以下のようにプライマリキーのカラムを削除して作り直しても1から順に歯抜けなく採番されます。ただし、一度プライマリキーのカラムの内容が削除される為、rowid(MySQLでは直接操作・閲覧不可)順に採番される。レコードを削除した後にレコードの追加をしなければプライマリキーの順で並んでいると思われる為問題ないかと思いますが、大抵の場合汚くなったマスターデータを綺麗にしたいという場合に行うかと思いますので、前者の方法が無難です。

ALTER TABLE  `item` DROP  `item_id`; 
ALTER TABLE  ADD  `item_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY  FIRST;