今回取り扱う論理削除と物理削除のテーブル例

本テーブルと技術本テーブルがあり
本テーブルは物理削除
技術本テーブルは論理削除だったとする

先に技術本テーブルが存在し論理削で運用されており、他の種類の本などのテーブルを作成するために、全ての本に共通する項目を持つ本テーブルを新しく作成。論理削除する理由が特になかったため物理削除のテーブルとした。

book テーブル

|id |title |
|---|---|---|
|1 |PHPの本 |
|2 |Rubyの本 |
|3 |Pythonの本 |
|4 |Goの本|

technical_book テーブル

GOの本を削除した場合

1 Goの本情報を物理削除
2 太郎が投稿した記事の削除フラグをTRUEに変更する必要がある。

book テーブル

|id |title |
|---|---|---|
|1 |PHPの本 |
|2 |Rubyの本|
|3 |Pythonの本|

technical_book テーブル

これが正しい状態だったとします。

book テーブル

|id |title |
|---|---|---|
|1 |PHPの本 |
|2 |Rubyの本|
|3 |Pythonの本|
|4 |Goの本|

technical_book テーブル

本当であれば、bookテーブルのid = 4のGoの本のレコードは物理削除しなければいけない

bookテーブルに登録できる上限が決まっていて上限に行ってしまった時に、どのようにbookテーブルのレコードを削除するのが正しいでしょうか？

1番最初に思いつく方法としては、technical_bookとbookをJoinして削除フラグが立っている物にひもづくbookテーブルのレコードを削除すれば良さそう

delete.sql

DELETE `book` 
FROM 
  `book` 
  INNER JOIN `technical_book` ON `technical_book`.`book_id` = `book`.`id` 
WHERE 
  `technical_book`.`del_flg` = 1;

book テーブル

|id |title |
|---|---|---|
|1 |PHPの本 |
|2 |Rubyの本|
|3 |Pythonの本|

technical_book テーブル

しかしこの方法で消していくの方法ではMySQLのバージョンがMySQL 5.7以前の場合、まずいパターンが存在する

この状態でserver restartすると自動採番が再計算されてしまう。
auto_incrementの値は再計算されて4になる。

この状態で新しいJava本とPerlの本ができてPerlの本を削除した場合
bookテーブル

|id |title |
|---|---|---|
|1 |PHPの本 |
|2 |Rubyの本|
|3 |Pythonの本|
|4 |Javaの本|
|5 |Perlの本|

technical_book テーブル

上記のSQLを実行してした場合、現在生存しているJavaの本も消えてしまう。

bookテーブル

|id |title |
|---|---|---|
|1 |PHPの本 |
|2 |Rubyの本|
|3 |Pythonの本|

technical_book テーブル

つまり以下のSQLを実行して対応するレコードが存在する場合は、上記のSQLでの削除すると消えてはいけないレコードが消えてしまうことになる。

delete.sql

SELECT 
  id 
FROM 
  `technical_book` 
GROUP BY 
  `book_id` 
HAVING 
  COUNT(`book_id`) > 1 
  AND MAX(`del_flg`) != MIN(`del_flg`);

このテーブルで問題だったのは既存のテーブルが論理削除で運用されているのに新しいテーブルを物理削除で作成してしまったことが原因だと考える。