MySQL道普請便り

第215回MySQLで使える正規表現

データから特定のパターンに一致する情報を迅速に抽出する能力は、データベース操作において非常に重要です。MySQLは8.0のバージョンで、正規表現周りにも改善が加えられています。バージョン8.0.4より前では、Henry Spencerによる正規表現が実装されていましたが、それ以降のバージョンでInternational Components for Unicode (ICU) による実装に変更されました。Henry Spencerではマルチバイトセーフではありませんでしたが、ICUではユニコード対応をしておりマルチバイトセーフになっています。また、このバージョンアップに加えて正規表現を利用した関数も増えており、正規表現を使って検索や文字列の抽出できるようになっています。

今回は、正規表現周りの演算子や関数について見ていきたいと思います。なお今回利用しているバージョンは8.0.35になります。

MySQLの正規表現を使った演算子

まず、MySQL 5.7バージョンにもあったregexp, not_regexpを使った演算子を確認しましょう。

REGEXP(RLIKE)

REGEXPは文字列 REGEXP パターンの形式で入力することで、正規表現にマッチした場合1を返し、マッチしない場合は0を返します。もし対象の文字列がNULLの場合はREGEXPもNULLを返します。

mysql> SELECT randum_string, randum_string REGEXP '^[a-d]' FROM dummy limit 3;
+---------------+-------------------------------+
| randum_string | randum_string REGEXP '^[a-d]' |
+---------------+-------------------------------+
| noHRruRajM    |                             0 |
| IniMV7gXES    |                             0 |
| d7ZOsHBB2P    |                             1 |
+---------------+-------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT NULL REGEXP '^[a-d]';
+----------------------+
| NULL REGEXP '^[a-d]' |
+----------------------+
|                 NULL |
+----------------------+
1 row in set (0.00 sec)

RLIKEというシノニムも存在し、REGEXPと同様の形式で記述することで、同じように正規表現にマッチするかどうかを確認することができます。

mysql> SELECT randum_string, randum_string RLIKE '^[a-d]' FROM dummy limit 3;
+---------------+------------------------------+
| randum_string | randum_string RLIKE '^[a-d]' |
+---------------+------------------------------+
| noHRruRajM    |                            0 |
| IniMV7gXES    |                            0 |
| d7ZOsHBB2P    |                            1 |
+---------------+------------------------------+
3 rows in set (0.00 sec)

NOT REGEXP

NOT REGEXPはREGEXPとは逆にマッチしない行を確認することができます。正規表現にマッチしない場合は1を返し、マッチする場合は0を返します。REGEXPと同様に対象の文字列がNULLの場合は結果もNULLを返します。

先ほど紹介したシノニムRLIKEを使ってNOT RLIKEと記述することもできます。

mysql> SELECT randum_string, randum_string NOT REGEXP '^[a-d]' FROM dummy limit 3;
+---------------+-----------------------------------+
| randum_string | randum_string NOT REGEXP '^[a-d]' |
+---------------+-----------------------------------+
| noHRruRajM    |                                 1 |
| IniMV7gXES    |                                 1 |
| d7ZOsHBB2P    |                                 0 |
+---------------+-----------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT NULL NOT REGEXP '^[a-d]';
+--------------------------+
| NULL NOT REGEXP '^[a-d]' |
+--------------------------+
|                     NULL |
+--------------------------+
1 row in set (0.01 sec)

MySQL 8.0で追加された関数

MySQL 8.0では正規表現に関連する関数がいくつか追加されています。続いては追加された関数について見ていきましょう。

REGEXP_LIKE()

REGEXP_LIKE()関数はREGEXPと同様に文字列が正規表現とマッチした場合は1を返し、一致しない場合は0を返します。記述は REGEXP_LIKE(対象の文字列, 正規表現のパターン) の形式で記述します。

これに加えて、オプションとして以下のマッチタイプを利用することで、照合の実行方法を指定することができます。

match_type 説明
c 大文字小文字を区別する(Case sensitive)
i 大文字小文字を区別しない(Case insensitive)
m Multiple-line modeになり、1カラム内にある複数行の場合でも文字列内の行終了記号を認識する
n 改行を任意の文字列として判断し.で一致させることができる。
u Unix 専用の行末

たとえば、以下のようなデータと仮定します。

mysql> INSERT INTO regexp_test VALUES ('First\nSecond\nThird');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM regexp_test;
+--------------------+
| str                |
+--------------------+
| First
Second
Third |
+--------------------+
1 row in set (0.00 sec)

match_typeがmの場合は、データ内の改行を認識して^Secondでマッチさせることができます。match_typeがnの場合では、改行文字も任意の文字列として認識するのでFirst.でマッチします。

mysql> SELECT REGEXP_LIKE(str, '^Second'), REGEXP_LIKE(str, '^Second', 'm') FROM regexp_test;
+-----------------------------+----------------------------------+
| REGEXP_LIKE(str, '^Second') | REGEXP_LIKE(str, '^Second', 'm') |
+-----------------------------+----------------------------------+
|                           0 |                                1 |
+-----------------------------+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_LIKE(str, 'First.'), REGEXP_LIKE(str, 'First.', 'n') FROM regexp_test;
+----------------------------+---------------------------------+
| REGEXP_LIKE(str, 'First.') | REGEXP_LIKE(str, 'First.', 'n') |
+----------------------------+---------------------------------+
|                          0 |                               1 |
+----------------------------+---------------------------------+
1 row in set (0.01 sec)

REGEXP_INSTR()

REGEXP_INSTR()は、指定された正規表現パターンに最初に一致する文字列内の位置を返します。この関数は、文字列内で正規表現パターンが最初に現れる場所を見つけるのに便利です。もしパターンが見つからない場合、この関数は0を返します。

検索を開始する位置の指定(pos)や、指定されたパターンが出現する何回目の位置を返すか(occurence)を指定することもできます。

mysql> SELECT REGEXP_INSTR('A regular expression is a powerful way of specifying a pattern for a complex search', 'p[a-z]+') as idx;
+-----+
| idx |
+-----+
|  13 |
+-----+
1 row in set (0.00 sec)

mysql> SELECT REGEXP_INSTR('A regular expression is a powerful way of specifying a pattern for a complex search', 'p[a-z]+', 20) as idx;
+-----+
| idx |
+-----+
|  27 |
+-----+
1 row in set (0.01 sec)

REGEXP_SUBSTR()

REGEXP_SUBSTR()は正規表現にマッチする文字列の結果を返します。一致しない場合はNULLを返します。

mysql> SELECT REGEXP_SUBSTR('fukamachi', '^f[a-z]{3}');
+------------------------------------------+
| REGEXP_SUBSTR('fukamachi', '^f[a-z]{3}') |
+------------------------------------------+
| fuka                                     |
+------------------------------------------+
1 row in set (0.00 sec)

REGEXP_REPLACE()

REGEXP_REPLACE()は正規表現にマッチする文字列を置き換えて結果を返します。一致しない場合はそのまま文字列を返します。

mysql> SELECT REGEXP_REPLACE('fukamachi', '^f[a-z]{3}', 'XXX');
+--------------------------------------------------+
| REGEXP_REPLACE('fukamachi', '^f[a-z]{3}', 'XXX') |
+--------------------------------------------------+
| XXXmachi                                         |
+--------------------------------------------------+
1 row in set (0.01 sec)

正規表現関連のシステム変数

正規表現に関連するシステム変数は2つあります。これらを調整することで、正規表現に利用されるリソースを制御することができます。

regexp_stack_limit

正規表現に使用する照合エンジンの使用可能な最大メモリーをバイト単位で制御します。デフォルトは8000000です。

regexp_time_limit

照合エンジンによって実行されるステップの最大数の制御になります。値はステップ数であるため、実行時間には間接的に影響することになります。

mysql> show variables like 'regexp%';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| regexp_stack_limit | 8000000 |
| regexp_time_limit  | 32      |
+--------------------+---------+
2 rows in set (0.01 sec)

まとめ

今回は正規表現に関する演算子や関数の紹介をしました。では、これらの正規表現の関数は、どういったところで利用すればよいでしょうか。単純に文字列を正規表現にマッチさせて検索することも可能ですが、MySQL 8.0からはCHECK制約が追加されたので、CHECK制約と組み合わせて正規表現にマッチする場合のみデータに格納するといったことも可能です。また、REGEXP_SUBSTR()やREGEXP_REPLACE()を利用することで、既存のデータを加工して抽出することもできます。これにより、データの加工や修正作業にも利用できるかもしれません。

今回の各関数については、公式ドキュメントの正規表現の項目に詳細に書かれています。こちらも参考に、MySQLでも正規表現を利用してみてください。

おすすめ記事

記事・ニュース一覧