SQLのlikeの使い方をサンプルつきでまとめました。
なお、MySQLのサンプルデータベースEmployeesを、SQL実行結果の表示にはphpMyAdminを使って解説しています。
likeを使ったワイルドカード指定の例
まずは、likeを使ったシンプルな例。社員情報が格納されている、employeesテーブルをワイルドカード指定で抽出してみました。
first_nameがAから始まる社員を抽出してみます。
SELECT * FROM employees WHERE first_name like 'A%'
結果はこうなります。
first_nameがAで始まるデータが抽出されました。
パターンマッチに使われる文字
パターンマッチに使われるワイルドカードは、以下の2つ。
- % 0文字以上の任意の文字列にマッチ
- _ 任意の一文字にマッチ
ワイルドカード文字のエスケープ
「%」や「_」そのものを比較する場合は、バックスラッシュでエスケープする必要があります。
例) like ‘100\%’
アプリケーションなどでユーザが入力したキーワードで、SQLを構成する場合「%」や「_」に大してエスケープ処理(サニタイズ)をおこなうべきでしょう。思いがけず、データベースに負荷をかけるクエリが発行されてしまう可能性があります。
なお、エスケープ文字を変えたい場合は、以下のようにします。
like '%A#_B%' ESCAPE '#'
エスケープ文字がデフォルトの「\」(バックスラッシュ)から、#に変更されます。
ワイルドカードの使用例
- ’A%’ 前方一致。Aで始まる文字列にマッチします。
- ’%A’ 後方一致。Aで終わる文字列にマッチします。
- ’%A%’ 中間一致。文字の最初、または途中、またさ最後にAが含まれる文字列にマッチします。
- ‘_%’ 2文字以上の文字列にマッチします。(アンダースコア2個に%1個)
- ‘__%’ 2文字以上の文字列にマッチします。(アンダースコア2個に%1個)
例えば、’A__ka%u’は以下の文字列にマッチします。
- A12kahu
- AAAkau
- Akakaaaaau
not like (マッチした文字列を含まない)の例
SELECT * FROM employees WHERE first_name like 'A%' and last_name not like 'S%'
first_nameがAで始まり、かつ、last_nameがSで始まらない社員のみ抽出できました。
大文字・小文字を区別する例
通常のlikeでは、大文字と小文字を区別しません。
SELECT * FROM `employees` WHERE first_name like '%geo%'
‘%geo’ が、 Geoにもマッチしてしまいます。
大文字と小文字を区別するには、like binaryを使います。
SELECT * FROM employees WHERE first_name like binary '%geo%'
小文字のgeoにのみマッチするようになりました。
SQL文のlikeの遅さを計測
SQL内で、likeを多用するとスピードが遅くなると言われます。どの程度遅くなるのか、計測してみました。
where句でワイルドカードを使わないlikeは、「=」と、意味は全く同じになります。
- where first_name = ‘George’
- where first_name like ‘George’
SELECT departments.dept_name, employees.first_name, employees.last_name FROM dept_emp left join departments ON departments.dept_no = dept_emp.dept_no left join employees ON dept_emp.emp_no = employees.emp_no WHERE to_date="9999-01-01" and first_name = 'George'
結果はこうなりました。
Showing rows 0 – 24 (185 total, Query took 0.0088 seconds.)
約30万件あるemployeesを結合して、以下の条件をつけた場合、0.0088秒かかったことになります。
first_name = 'George'
以下の条件を指定すると、
first_name like 'George'
Showing rows 0 – 24 (185 total, Query took 0.0091 seconds.)
結果は全く同じで、0.0091秒かかりました。
データベースやチューニング内容によって結果は変わってくるかも知れませんが、今回使ったMySQL Community Server 5.7では、大きな差は確認できませんでした。
likeで正規表現は使えない
likeで使えるのは、「%」と[_」(アンダースコア)を使った、簡易的なワイルドカード表現のみです。
しかし、各データベースでは独自に機能を拡張して正規表現を使えるようになっています。
MySQLの場合は、REGEXP演算子を使って正規表現が可能。
OracleやMicrosoft SQLServerにも、正規表現に対応する拡張がされています。
Oracleは、REGEXP_LIKE で正規表現が可能ですし、MicrosoftのSQLServerは、likeの文字列に[](大カッコ)を使うことで、簡易的な正規表現が使えるようになっています。
参考)LIKE (Transact-SQL) – SQL Server | Microsoft Docs
- likeで正規表現は使えない
- ただし、各データベースの拡張で、正規表現機能が用意されている
date型のカラムにlikeを使うには
date型のカラムにlikeでワイルドカード指定をする場合、MySQLだとこのように記述します。
SELECT * FROM employees WHERE DATE_FORMAT(hire_date,'%Y%m%d') like '1990%'
結果はこうなります。hire_dateが1990で始まるデータが抽出できました。
ただし、この記述方法はSQL標準ではありません。各データベースによって、記載方法がことなります。
Oracleの場合は、TO_CHAR、Microsoft SQL Serverの場合は、Convertを使います。
参考)CAST および CONVERT (Transact-SQL) – SQL Server | Microsoft Docs
ちなみに、MySQLの場合は、DATE_FORMATを使わずに、
WHERE hire_date like '1990%'
と書いても、動作します。
SQLのlike使い方まとめ
- 指定内容によっては、大きく速度が落ちる可能性あり
- 正規表現は、各データベースのSQL拡張。書式などが異なる
- date型に対するワイルドカード指定は、char型に変換を行えば可能
where句に=(イコール)を使用した場合と比べて、likeを使うと1.03倍時間がかかる。