ASIPの徒然なるままに このページをアンテナに追加 RSSフィード

2007-09-15

ORの代わりに正規表現 on SQL 01:21 ORの代わりに正規表現 on SQLを含むブックマーク ORの代わりに正規表現 on SQLのブックマークコメント

SQL正規表現を使えるDBでは正規表現を使うとSQLがすっきりする。

「column='A' or column='B' or column='C'」を正規表現で書くと

「column ~ '^(A|B|C)$'」(A,B,Cには正規表現エスケープが必須)。

「column like '%A%' or column like '%B%' or column like '%C%'」を

正規表現で書くと

「column ~ 'A|B|C'」(A,B,Cには正規表現エスケープが必須)。

正規表現で「^」は前方一致、「$」は後方一致、「|」はORに相当。

プログラムSQLを組み立てるときも有用。

正規表現の部分を動的に組み立てるだけでよくなる。


「~」はPostgreSQLでの正規表現演算子


「column ~ '^(A|B|C)$'」はIN演算子で「column in ('A','B','C')」、

PostgreSQLでは前方一致の正規表現インデックスが効くので、

「column ~ '^(A|B|C)$'」、「column in ('A','B','C')」のどちらを

書いてもインデックスが効きます。

(改訂第5版PostgreSQL完全攻略ガイドP474に記述あり)

「column ~ 'A|B|C'」についてはPostgreSQLにおいてあいまい検索

インデックスは効かないもののそれなりに有用。


正規表現で「^」は前方一致、「$」は後方一致、「|」はORに相当。

』はわかりやすいかなと思ってこう書いています。


[追記:2008/08/16]

DB=PostgreSQLのあるシステムで例のA、B、Cに当たる部分が任意の

個数でなおかつ完全一致・前方一致・後方一致ではない条件で検索を

行わなくてはならない仕様の検索機能を実装したことがありました。

このとき、likeを使ったSQL文では本番のデータで検索結果の返りが

遅い(あるいは返ってこない)ケースがあり、likeではなく正規表現

使うようにSQL文を変更したところ、検索結果の返りに関する問題が

解消されました。

[追記:2010/04/10]

あいまい検索の場合、like を使ったSQL文では「orで連結した条件」

の数だけテーブルへのシーケンシャルアクセスが行われ、「orで連結

した条件」毎に集合が抽出され、最後にそれらの集合が連結されます。

同様の検索で、ORの替りに正規表現を使ったSQL文ではテーブルへの

シーケンシャルなアクセスが1回だけ行われ、正規表現に記述した

条件に適合する集合が抽出されます。

あいまい検索の場合、like を使ったSQL文より正規表現を使ったSQL

のほうがテーブルへのシーケンシャルなアクセスの回数が少ない分、

高速に動作する可能性が高いです。