AUTO_INCREMENTで設定した番号を削除後も連番にしたい

Question

例えば

1.田中
2.佐藤
3.鈴木
4.山中
5.橋本

とあり、3.鈴木　を消した場合

1.田中
2.佐藤
4.山中
5.橋本

となってしまうところを

1.田中
2.佐藤
3.山中
4.橋本

と、連番で表示されるようにしたいです。いい方法はないかと調べると
以下のサイトを見つけました。
how to renumber primary index

このサイトによると

SET @i=0;
UPDATE table_name SET column_name=(@i:=@i+1);

このコマンドが有効らしですが、しかしこのコマンドをphp文に組み込む際どのようにしてよいかわかりません。
例えば、

if($pass==$delete_pass){
			$sql="DELETE FROM $tb_name WHERE number=$num";
			$stmt=$pdo->prepare($sql);
			$stmt->execute();
			
			$reset_num="SET @i:=0; UPDATE $tb_name SET number=(@i:=@i+1)";
			$stmt=$pdo->prepare($reset_num);
			$stmt->execute();
			
		}else{
			echo "パスワードまたは削除番号が間違っています";
			exit();
		}

などと書いてみましたが
【エラー】

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE keijiban2 SET number=(@i:=@i+1)' at line 1

このように、構文ミスのようです…

Answer 1

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

####ROW_NUMBER()

Number of current row within its partition

mysql 8 以降の機能を使いましょう

Answer 2

お使いの環境（Windows or Mac or Linux およびディストリビューションやバージョン、PHPやMariaDBのバージョン等）を追記してください。

明らかな間違いは下記です。

$reset_num="SET @i:=0; 以下略

正しくは下記で、SETの場合は:=ではなく = を使います。参照元の記事もそうなっています。

$reset_num="SET @i=0; 以下略

参考: MySQL :: MySQL 5.6 リファレンスマニュアル :: 9.4 ユーザー定義変数

ただ、手元の環境（MariaDB 10.1.41）で試したところ、SET @i:=0 でも動きました。MariaDBのバージョン等によっては:=でも動くのかもしれません。

Comments

[退会済みユーザー]

ちなみに MySQL 8 マニュアルの一文

Previous releases of MySQL made it possible to assign a value to a user variable in statements other than SET. This functionality is supported in MySQL 8.0 for backward compatibility but is subject to removal in a future release of MySQL.

[ockeghem]

ありがとうございます。deprecatedなのはともかく、@i:=@i+1 という逐次処理が著しくSQL的でない記述で、「たまたま動いているだけじゃないの?」という感じはしますね

[chimo]

すごく勉強になりました！ご指摘いただいた部分で解決できました！ありがとうございます！

Answer 3

AUTO_INCREMENTを使ってもROLLBACKしたり、DELETEを実行すると欠番が生じます。

ROLLBACKは採番テーブルを使うことで避けられますが、AUTO_INCREMENTより負荷が増え、システムの規模が大きくなるとOracleでこの無駄な負荷がボトルネックになった実例をみたことがあります。

データベース設計の基礎としてPRIMARY KEYの値は変更しない、って掟があります。DELETEとそれ以降の番号を振り直すことができますか？外部キーを設定していると子テーブルまで番号を振り直さなければなりません。

実際に10個くらいテーブルを用意して、ROLLBACKやDELETEを実行して番号を振り直してどれだけ面倒な作業か実感されることをお薦めします。

Comments

[退会済みユーザー]

MySQL の場合はそれほどじゃなかったりするぞ（おすすめしないが）
ボトルネックが発生するので

1. 外部キー制約で 以下を付与
2. 対象を削除する
3. 対象以上を1減算させる
4. オートインクリメントの設定を再設定する

オラクルはすこぶる面倒だったはず

[Orlofsky]

それだけでも十分面倒だから、もっと建設的な方向に脳ミソを使いましょう。

[退会済みユーザー]

うんｗ　なんで回答は建設的な方ｗ

Answer 4

連番で表示されるようにしたいです。

そもそも、そのようなことをAUTO_INCREMENTの番号に求めるべきではありません。

どのような理由で厳格な連番が必要かによって、取るべき対応は異なってきます。

Comments

[chimo]

では削除することを想定した場合は最初から番号の設定をAUTO_INCREMENTにしない方が良いということでしょうか

[maisumakun]

何のために確実に連番にしたいのか、その理由から考えないといけません。

状況によっては「データベースから引く時点で、AUTO_INCREMENTによるIDとは別個に、そのタイミングで存在するデータに連番を振る」ような処理のほうが適切かもしれません。

[maisumakun]

> では削除することを想定した場合は最初から番号の設定をAUTO_INCREMENTにしない方が良いということでしょうか

「AUTO_INCREMENTで与えられる番号が連番であるのを期待しない」のが正解です。削除しなくても飛ぶこともありうる、とのことです。

[退会済みユーザー]

ちなみに heroku/cleardb は オートインクリメントの増分は 10ずつ・・・

Answer 5

単にデータ表示するときの見た目だけの話なら、ループで表示する際にデータ関係なく別途手動で連番作って表示させれば良いだけではないでしょうか。

残っているデータの主キーを変更するのは悪手でしかありません。