新しく追加した列を条件に含めずに検索したい

Question

MySQL(Aurora) + PHP7 で簡単なwebアプリを作っています。

商品テーブル item が存在するのですが、今回そこに削除フラグとして以下のように deleted という列を追加することになりました。

ALTER TABLE item ADD deleted TINYINT UNSIGNED DEFAULT 0 -- 0:有効, 1:削除済

ここまでは何の問題もないのですが、この商品テーブルを参照するSQLがソース内に既に500以上存在します。（ORMやビューは未使用です）

ほとんどのケースにおいて、削除済み商品については基本的に処理をさせたくないため、原則的には WHERE deleted = 0 をSQLに追加していけばよいのですが、現時点で item テーブルにアクセスするSQLが各ソース内に合計で500以上存在します。

流石にこれらのSQLに対して1つ1つ WHERE条件を追加していくのは無駄な気がしてならないのですが、95%以上のSQLで deleted = 0 のみのデータを対象とすることから、「WHERE句に何も指定していない場合は deleted = 0 として扱われ、明示的に削除済みの商品も扱いたい場合は deleted = 1 ないし deleted IN (0,1) のように指定することで扱える」ようにするために、何か良い方法はないでしょうか？

なお、現時点では削除済みの商品はないものの、今後削除済みになる可能性はあるという状態です。
また、単純なSELECTのみでなく、UPDATE/INSERTやJOIN、サブクエリ等にも使用しています。

書き換え漏れや再テストの手間を考えて、既存のSQLやPHPソースの書き換えを最小限にできればと考えています。
最初からビューにしておけばいくらか手間が減った気はするのですが、今からSQLの参照先を全てビューに変更するのもどうかと思っています。

こういうケースは結構ありそうな気がするのですが、自分で検索しても良い検索結果が得られなかったため、妙案があればアドバイスいただければ幸いです。

Answer 1

WHERE句でdeleted列の判断が必要ならインデックスに追加することも検討した方が良いでしょう。

削除済みデータを参照する機会が少なければ、deleted列を追加するのではなく、削除済みデータを別テーブルに保存する方法も考えられます。

metal777さんが明日COVID-19を発症して亡くなっても後継者がシステムを維持できるように、きちんとシステムを管理しておくのがソフトウェア屋としてのモラルです。

商品テーブルを参照するSQLがソース内に既に500以上存在します。

であれば「簡単なwebアプリ」ではないです。

Comments

[m.ts10806]

>であれば「簡単なwebアプリ」ではないです。

同感です。
管理面もかなり複雑

Answer 2

where句になにも指定しないで条件分岐するのはむしろ危険なので
きちんとdelete=0とするか、思い切って物理削除して、別テーブルに
保持するなど抜本的な対応が必要でしょう

Comments

[metal777]

別テーブルも考えたのですが、このためだけにトランザクション更新処理が必要になってしまうことが多発してしまうため、今のところ想定から外しました。

主旨としては「SQL標準では厳しいと思われるので、MySQL特有の機能（関数やユーザ変数など）で何とかして delete=0 を省略できないか」ということになります。
ストアドプロシージャにしていれば比較的簡単だったのですが、素のSQLが大量に書かれている状態でして・・

[yambejp]

元テーブルで削除して、別テーブルに保存する場合、triggerでやれば一発ですね。

Answer 3

こういうケースは結構ありそうな気がするのですが

あるかもしれませんが、「やってはいけない」パターンかと思います。

今からSQLの参照先を全てビューに変更するのもどうかと思っています。

PHP側でORMが挟まっていてそのレイヤでどうにかできるのでなければ、いちばん手間の少ない方法としてはこれでしょう。

Answer 4

whereの対象にdeletedを使わなければいいだけじゃない
そこに懸念があったり実装が出来ない理由があるならそこまで質問に書いてないと分からない

Comments

[metal777]

deletedを指定しない場合、今後削除済みデータが発生した場合に不要なデータまで対象となってしまいます。
PHP側も直せばよいのは当然ですが、LIMITやORDER BY句も使用しているのでSQL内のみで完結させたいと考えています。

[hentaiman]

orderbyはともかくLimitはどうしようもない。既に方法については様々検討されてると思うし本来取るべき手法も理解はされてると思うので、後は技術以外の面で検討するしかないですね
レコード件数・修正箇所・そのシステムがどの程度生きるのか・今後拡張するのかを含めてコストに見合った方法を取るのがよろしいでしょうね

Answer 5

案を挙げますが、これをするくらいならphpを直した方がマシ、
と考えて頂けたら良いと思います。
・現状のitemテーブルをitem2という名前に変更してdeletedを追加する
・item2をdeleted = 0の条件で参照するitemというビューを作る

作業時はシステム停止が必要
itemテーブルの更新処理の仕様次第では修正が必要
deleted = 0ではない条件で参照する機能も修正が必要
あとでわかりにくい
JOINとかもしているということなので実行計画が狂うかも
etc...

リスキーかつ美しくないと思います。