MySQLでレコード削除後自動的に連番を詰めたい

Question

###前提・実現したいこと
php, mysqlを使用しています。
以下のようなidとは別にnoで表示順を制御するテーブルを使っていますが、レコードを削除した際にnoカラムに自動的に詰めた連番を振りたいのですが、どうしてもうまい方法が見つかりません。

CREATE TABLE `hoge_item` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `fuga_id` int(10) unsigned NOT NULL DEFAULT '0',
  `no` int(10) unsigned NOT NULL DEFAULT '0',
  `name` varchar(128) DEFAULT NULL,
  `active_flag` tinyint(4) NOT NULL DEFAULT '0',
  `reg_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `upd_date` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fuga_id` (`fuga_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

###レコード削除までは実行できるが、その前後の処理方法が不明

$sql = "DELETE FROM hoge_item ";
$sql .= "WHERE id = " . $_GET['fuga_id'];
$rs = mysql_query($sql);
if($rs){
 $msg = "hogeアイテムの削除に成功しました";
} else {
 $msg = "hogeアイテムの削除に失敗しました";
}

fuga_idがKEYなので
0. fuga_idが同じレコードを取得して削除したレコードのnoを取得
0. 1で取得したno以下のnoを全て-1にすれば良い

のですがどのようにすれば良いのかわからず困惑しております。。。。。

###補足情報(言語/FW/ツール等のバージョンなど)
以下のバージョンを使用しています。

PHP 5.6.30
mysqld  Ver 5.7.17 for Linux on x86_64 (MySQL Community Server (GPL))

宜しくお願い申し上げます。

Comments

[shiroyuki]

AUTO_INCREMENTであるカラムの値を削除ごとに変更するのは良くないと思われるのですが、idの枯渇対策とかでしょうか？

[kphex]

この場合、idではなく　`no` int(10) unsigned NOT NULL DEFAULT '0'　カラムなので問題無いと思いますがその場合でも良く無いのでしょうか？

[shiroyuki]

大変申し訳ございません。読み間違えました。

Answer 1

noを手動で入れ替えたり設定しているのであれば、削除対象のnoより大きいものを全て-1するのが手っ取り早いと思います。
注意点としてupdateからdeleteまでトランザクションを使用してデータに不整合が起きないようにしてください。

※削除前に実行する

UPDATE hoge_item SET no = no-1 WHERE fuga_id = xxx AND no > (SELECT no FROM hoge_item WHERE id = xxx);

もしnoが単純にデータ投入順なだけであれば、連番の振り直しという手段もあります。

SET @i := 0;
UPDATE hoge_item SET no = (@i := @i +1) WHERE fuga_id = xxx ORDER BY id;

あと別件になりますが、mysql_queryはPHP 5.5.0で非推奨になり、PHP 7で削除されていますので業務で使用するのであれば別関数への移行をおすすめします。

Comments

[kphex]

有難う御座いました。1つ目の仕様でしたのでそちらのコードを参照させていただきました！

ただし、ERROR:1093が出てしまったので

UPDATE hoge_item SET no = no-1
WHERE fuga_id = XXX
AND no > (
SELECT no FROM
(SELECT no FROM hoge_item WHERE id = xxxxxx) as temp);

とする事で実現できました。

[kphex]

mysql_queryに関してはそうですね…早めに移行いたします T^T
有難う御座いました。