PostgreSQLにおけるLIKEとILIKEの違いを検証してみた

こんにちは!kossyです!




今回はPostgreSQLにおけるLIKEとILIKEの違いを検証してみましたので、ブログに残してみたいと思います。




環境
PostgreSQL 12.5系
Rails 6.0.3



検証

まずはドキュメントを読んでみます。

www.postgresql.jp

9.7.1. LIKE

LIKE式は供給されたpatternにstringが一致すれば真を返します。 (想像される通り、NOT LIKE式はLIKE式が真を返す場合には偽を返し、その逆もまた同じです。 同等の式としてNOT (string LIKE pattern)とも表現できます。)

patternがパーセント記号もしくはアンダースコアを含んでいない場合patternは自身の文字列そのものです。この場合LIKE式は等号演算子のように振舞います。 patternの中にあるアンダースコア(_)は任意の一文字との一致を意味し、パーセント記号(%)は0文字以上の並びとの一致を意味します。

出典: https://www.postgresql.jp/document/12/html/functions-matching.html

ドキュメントに記載はありませんが、LIKEは大文字小文字を区別して検索を行います。

以下のようなレコードがあるとします。

 id: 1,
 first_name: "Kota",
 last_name: "Omura",
 first_name_kana: "コウタ",
 last_name_kana: "オムラ",


このレコードをLIKE句を使って取得します。

Employee.where('first_name LIKE ?', '%Kota%')
=>   Employee Load (3.4ms)  SELECT "employees".* FROM "employees" WHERE (first_name LIKE '%Kota%')
[#<Employee:0x000055b9718b3460
  id: 1,
  first_name: "Kota",
  last_name: "Omura",
  first_name_kana: "コウタ",
  last_name_kana: "オムラ">]

Kotaと検索することで取得できました。ではここで先頭のKを小文字にして検索してみます。

Employee.where('first_name LIKE ?', '%kota%')
=>   Employee Load (6.5ms)  SELECT "employees".* FROM "employees" WHERE (first_name LIKE '%kota%')
[]

取得できませんでした。LIKEは大文字小文字を区別して検索していることがわかりますね。

次にILIKEを使って、先頭のKは小文字のまま検索してみます。

Employee.where('first_name ILIKE ?', '%kota%')
=>   Employee Load (6.9ms)  SELECT "employees".* FROM "employees" WHERE (first_name ILIKE '%kota%')
[#<Employee:0x000055b971bf6488
  id: 1,
  first_name: "Kota",
  last_name: "Omura",
  first_name_kana: "コウタ",
  last_name_kana: "オムラ">]

ILIKEを使えば先頭のKが小文字でもレコードを取得することができました。

試しに「KOTA」で検索もしてみましょう。

Employee.where('first_name ILIKE ?', '%KOTA%')
=>   Employee Load (6.5ms)  SELECT "employees".* FROM "employees" WHERE (first_name ILIKE '%KOTA%')
[#<Employee:0x000055b971ddd328
  id: 1,
  first_name: "Kota",
  last_name: "Omura",
  first_name_kana: "コウタ",
  last_name_kana: "オムラ">]

取得できました。ILIKEは大文字小文字を区別せずに検索を行う挙動のようですね。

ドキュメントを読むと、ILIKE句はSQL標準機能ではなく、PostgreSQL拡張機能のようです。

現在のロケールに従って大文字小文字を区別しない一致を行うのであれば、LIKEの代わりにILIKEキーワードを使うことができます。
これは標準SQLではなく、PostgreSQLの拡張です。

出典:
https://www.postgresql.jp/document/12/html/functions-matching.html

ちなみにMySQLのLIKE句ではデフォルトで大文字小文字を区別しない(= PostgreSQLのILIKEと同じ挙動)ようです。

daybydaypg.com


まとめ

大文字小文字を区別して検索を行いたい場合はLIKE句を、区別したくない場合はILIKE句を使って検索を行うようにしましょう。