MySQLでDELETEを行った後のINSERT

Question

###前提・実現したいこと
phpとMySQL1を利用して簡易掲示板を作っています。
入力データをMySQLに保存し、そこからデータを取得・表示させる流れです。

その中で、削除機能、編集機能を実装しようとしているのですが、そのうち削除機能に問題がありまして、質問させていただきます。

###発生している問題・エラーメッセージ
DELETEを行ってから新しく投稿をINSERTで入力すると、DELETEした配列部分に入ってしまいます。
例えば

1,内容
2,内容
3,内容

となっているうちの2を削除してから新規投稿すると

1,内容
4,内容
3,内容

となってしまいます。その後は3の後ろに5、6、…と続きますが、削除を繰り返すともっとずれてしまいます。
これを修正したいです。

###該当のソースコード
周辺がごちゃごちゃしていて見にくくなってしまうので、該当部分だけ記載します。

//投稿部分
$sql='INSERT INTO kadai(name,comment,date,pass) VALUES(:name,:comment,now(),:pass)';

$stmt=$pdo->prepare($sql);

stmt->bindParam(":name",$name,PDO::PARAM_STR);
$stmt->bindParam(":comment",$comment,PDO::PARAM_STR);
$stmt->bindParam(":pass",$pass,PDO::PARAM_STR);

$name=$_POST["name"];
$comment=$_POST["comment"];
$pass=$_POST["password"];

$stmt->execute();

…

//削除部分
$sql="DELETE FROM kadai WHERE id=:id";

$stmt=$pdo->prepare($sql);

$stmt->bindParam(":id",$id,PDO::PARAM_INT);

$id=$_POST["deletenumber"];

$stmt->execute();

投稿部分、削除部分をhtmlに設置したそれぞれのボタンを押したときで条件分岐させています。

###試したこと
ずれないようにするために、以下のことを考えました。
・UPDATEで内容を「削除済み」に書き換え、表示する際の条件分岐で「削除済み」となっているものをはじく
⇒これだと、番号が残ってしまっているため編集ボタンと干渉してしまうということと、万が一「削除済み」というコメントを投稿した際に意図せずはじかれる可能性があるかな…と感じました。
・UPDATEで番号を上書きし、表示の条件分岐ではじく
⇒具体的には、番号を0にUPDATEすればいいかなと考えていますが、送信された番号をWHEREを使って指定するやり方がよくわからないので実装できていません。

自分でも深く理解できていないため、わかりにくい文章になってしまい申し訳ありません。よろしくお願いいたします。

Answer 1

まず順番の話はPHPでSELECTして取り出した後の話をしてますか？

MySQLに限らないと思うんですが（適当）ORDERで指定しない限りSELECTの順番は保証されないと言う前提があります。
（要するに新しく追加したレコードが最後、もしくは最初に取得される事は保証されない）

ですのでDELETEしてからINSERTするのであれば例えばですが、AUTOINCREMENTでID列を作っておいてからSELECT時にORDER BY　IDとかやるなどの順番が保証される処置が必要だと思います。
コレだと質問で言うと2を削除してからINSERTすると必ず4が追加されるのでORDER BYすれば必ずそのじゅんばんになります。

もしくはDATETIMEやTIMESTAMPの列を準備して追加・更新時に時間を更新するとか（この場合は時間順と言うことになりますが）並び替えしたい順番を保証する列を作成するのが常道かと思います。

何にせよ自分の中の前提（悪い言い方をすれば決めつけ）を破棄して、多少面倒でも間違いないやり方をしていく方が後からトラブルは少なくなり、修正しやすくなると思います。

Comments

[metamonsan]

SELECT ORDER BYで取得することで無事解決しました。

「DELETE後のINSERTに問題がある！」と決めつけて情報を集めていたために、まったくヒットしなかったようでした。単純に取得の並び替えの問題だったのですね。
状況の言いかえなど、もっと広い視点で検索をかけていこうと思います。

ありがとうございました。

Answer 2

勝手にIDが増えていくのはIDフィールドにAUTO_INCREMENT属性
が付与されているからと思われます。

AUTO_INCREMENT属性がついているIDの場合、テーブル自体に次に振られるべきIDが記録されており、insertされる度に1増えていき、レコードが削除されても減ることはありません。

なのでたとえば、
3件追加→3件削除→1件追加　とした場合、テーブルに残るのはID４のレコードになります。

よくある解決方法としては、
0. IDに欠番が発生することを気にしない
下手にIDを再利用すると色々面倒なこと（例えば、このIDを他のテーブルでのデータ連結に使用している場合）が起こる可能性が増えるので、IDに欠番が発生しても気にしないという方法はよくあります。（個人的には特に理由がない限りは一番お勧めです）
0. 論理削除する。
例えば、is_deleteフィールドを設けて、削除したことにしたいレコードについてis_deleteフィールドに1を入れてupdateする。といった方法です。これは今回考えられている方法に近いと思います。これもよくある実装かと思います。
0. 自力でIDを採番する
deleteは現状のまま行い、insertするタイミングで空いているIDを自力で探して、IDを指定してinsertします。1番目のお勧め理由によって個人的には嫌いな方法です。
0. deleteしたタイミングでIDを詰める。途中のIDを削除した場合に、それより後ろのIDをすべて-1してupdateします。普通はやらないです。

あたりかなと思います。

Comments

[metamonsan]

ありがとうございます。
聞き方がわかりにくくて大変申し訳なかったのですが、とにかく番号の若い順から表示されるようにしたかったのです。

とはいえ、どこかで詰めずに削除されたというメッセージだけ表示させられないか…とは考えておりました。初心者であるために2以降は非常にややこしそうに見えますね…素直にこのまま詰めていきたいと思います。