この記事はHameeアドベントカレンダー2019 16日目の記事です。
物理削除と論理削除についての説明に関しては別で詳しい記事を書いてくれている人がいるのでそちらを見てください。
今回の扱うテーブルは例としてあげているだけなのでテーブル自体の設計に対してのツッコミは無しでお願いします。
実際に試した環境 mysql 5.6.22 innodb
今回取り扱う論理削除と物理削除のテーブル例
本テーブルと技術本テーブルがあり
本テーブルは物理削除
技術本テーブルは論理削除だったとする
先に技術本テーブルが存在し論理削で運用されており、他の種類の本などのテーブルを作成するために、全ての本に共通する項目を持つ本テーブルを新しく作成。論理削除する理由が特になかったため物理削除のテーブルとした。
book テーブル
|id |title |
|---|---|---|
|1 |PHPの本 |
|2 |Rubyの本 |
|3 |Pythonの本 |
|4 |Goの本|
technical_book テーブル
| id | book_id | del_flg |
|---|---|---|
| 1 | 1 | 0 |
| 2 | 2 | 0 |
| 3 | 3 | 0 |
| 4 | 4 | 0 |
GOの本を削除した場合
1 Goの本情報を物理削除
2 太郎が投稿した記事の削除フラグをTRUEに変更する必要がある。
book テーブル
|id |title |
|---|---|---|
|1 |PHPの本 |
|2 |Rubyの本|
|3 |Pythonの本|
technical_book テーブル
| id | book_id | del_flg |
|---|---|---|
| 1 | 1 | 0 |
| 2 | 2 | 0 |
| 3 | 3 | 0 |
| 4 | 4 | 1 |
これが正しい状態だったとします。
bookテーブルが削除されない期間が生まれてしまった時に、id=3のPythonの本を消した場合
book テーブル
|id |title |
|---|---|---|
|1 |PHPの本 |
|2 |Rubyの本|
|3 |Pythonの本|
|4 |Goの本|
technical_book テーブル
| id | book_id | del_flg |
|---|---|---|
| 1 | 1 | 0 |
| 2 | 2 | 0 |
| 3 | 3 | 0 |
| 4 | 4 | 1 |
本当であれば、bookテーブルのid = 4のGoの本のレコードは物理削除しなければいけない
bookテーブルに登録できる上限が決まっていて上限に行ってしまった時に、どのようにbookテーブルのレコードを削除するのが正しいでしょうか？
1番最初に思いつく方法としては、technical_bookとbookをJoinして削除フラグが立っている物にひもづくbookテーブルのレコードを削除すれば良さそう
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 テーブル
| id | book_id | del_flg |
|---|---|---|
| 1 | 1 | 0 |
| 2 | 2 | 0 |
| 3 | 3 | 0 |
| 4 | 4 | 1 |
しかしこの方法で消していくの方法では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 テーブル
| id | book_id | del_flg |
|---|---|---|
| 1 | 1 | 0 |
| 2 | 2 | 0 |
| 3 | 3 | 0 |
| 4 | 4 | 1 |
| 5 | 4 | 0 |
| 6 | 5 | 1 |
上記のSQLを実行してした場合、現在生存しているJavaの本も消えてしまう。
bookテーブル
|id |title |
|---|---|---|
|1 |PHPの本 |
|2 |Rubyの本|
|3 |Pythonの本|
technical_book テーブル
| id | book_id | del_flg |
|---|---|---|
| 1 | 1 | 0 |
| 2 | 2 | 0 |
| 3 | 3 | 0 |
| 4 | 4 | 1 |
| 5 | 4 | 0 |
| 6 | 5 | 1 |
つまり以下のSQLを実行して対応するレコードが存在する場合は、上記のSQLでの削除すると消えてはいけないレコードが消えてしまうことになる。
SELECT
id
FROM
`technical_book`
GROUP BY
`book_id`
HAVING
COUNT(`book_id`) > 1
AND MAX(`del_flg`) != MIN(`del_flg`);
何が問題だったのか
このテーブルで問題だったのは既存のテーブルが論理削除で運用されているのに新しいテーブルを物理削除で作成してしまったことが原因だと考える。
参考リンク
MySQL 5.7以前のAUTO_INCREMENTはサーバ再起動すると値が変わるがMySQL 8.0では変わらなくなった