3.3.4.7 パターンマッチング

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「正規表現」で、正規表現の構文の詳細について説明しています。