MySQL では、標準の SQL パターンマッチングに加え、vi、grep、sed などの Unix ユーティリティーで使用されるものに似た拡張正規表現に基づくパターンマッチング形式が提供されています。
SQL のパターンマッチングを使用すると、「_
」 で任意の単一の文字、「%
」 で任意の数の文字 (ゼロ文字を含む) に一致させることができます。MySQL のデフォルトでは、SQL パターンでは大文字と小文字が区別されません。次にいくつかの例を示します。SQL パターンを使用するときには、=
や <>
は使用しません。代わりに、LIKE
または NOT LIKE
比較演算子を使用します。
「b
」で始まる名前を探すには:
mysql> SELECT * FROM pet WHERE name LIKE 'b%';
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
「fy
」で終わる名前を探すには:
mysql> SELECT * FROM pet WHERE name LIKE '%fy';
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+
「w
」を含む名前を探すには:
mysql> SELECT * FROM pet WHERE name LIKE '%w%';
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+
ちょうど 5 文字の名前を探すには、「_
」パターン文字の 5 つのインスタンスを使用します。
mysql> SELECT * FROM pet WHERE name LIKE '_____';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
MySQL で提供されているもう 1 種類のパターンマッチングは、拡張正規表現を使用します。このタイプのパターンについて一致をテストする場合は、REGEXP
演算子と NOT REGEXP
演算子 (またはシノニムである RLIKE
と NOT RLIKE
) を使用します。
次の表に、拡張正規表現の特徴の一部を示します。
「
.
」は任意の 1 文字に一致します。文字クラス「
[...]
」は、括弧内のいずれかの文字に一致します。たとえば、「[abc]
」は「a
」、「b
」、または「c
」に一致します。文字の範囲を指定するには、ダッシュを使用します。「[a-z]
」は任意の英字に一致し、「[0-9]
」は任意の数字に一致します。「
*
」は直前の文字の 0 個以上のインスタンスに一致します。たとえば、「x*
」は任意の数の「x
」文字に一致し、「[0-9]*
」は任意の数の数字に一致し、「.*
」は任意の数の任意の文字に一致します。REGEXP
パターンマッチングは、テストする値のいずれかの部分にパターンが一致すれば成功です。(これとは異なり、LIKE
パターンマッチングは、パターンが値全体に一致する場合のみ成功です。)テストする値の先頭または末尾にパターンが一致するように指定するには、パターンの先頭に「
^
」またはパターンの末尾に「$
」を使用します。
拡張正規表現動作の仕組みを確認するために、前出の LIKE
クエリーを REGEXP
で書き直したものを次に示します。
「b
」で始まる名前を探すには、「^
」を使用して名前の先頭に一致するように指定します。
mysql> SELECT * FROM pet WHERE name REGEXP '^b';
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
REGEXP
比較で大文字と小文字を区別するには、BINARY
キーワードを使用して、文字列の 1 つをバイナリ文字列にします。次のクエリーは、名前の先頭にある小文字の「b
」だけに一致します。
mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';
「fy
」で終わる名前を探すには、「$
」を使用して名前の末尾に一致するように指定します。
mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+
「w
」を含む名前を探すには、次のクエリーを使用します。
mysql> SELECT * FROM pet WHERE name REGEXP 'w';
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+
正規表現パターンは値のいずれかの部分に見つかれば一致するため、このクエリーでは、SQL パターンを使用する場合のようにパターンの両側にワイルドカードを付加してパターンを値全体と一致させる必要はありません。
ちょうど 5 文字の名前を探すには、「^
」と「$
」を使用して名前の先頭と末尾に一致するように指定し、間に「.
」を 5 つ使用します。
mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
このクエリーは、{
(「n
}n
回繰り返し」) 演算子を使用して記述することもできます。
mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
セクション12.5.2「正規表現」で、正規表現の構文の詳細について説明しています。