データから特定のパターンに一致する情報を迅速に抽出する能力は、データベース操作において非常に重要です。MySQLは8.
今回は、正規表現周りの演算子や関数について見ていきたいと思います。なお今回利用しているバージョンは8.
MySQLの正規表現を使った演算子
まず、MySQL 5.
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.
REGEXP_LIKE()
REGEXP_REGEXP_ の形式で記述します。
これに加えて、オプションとして以下のマッチタイプを利用することで、照合の実行方法を指定することができます。
| match_ |
説明 |
|---|---|
| c | 大文字小文字を区別する |
| i | 大文字小文字を区別しない |
| 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_^Secondでマッチさせることができます。match_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_
検索を開始する位置の指定
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_
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_
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.
今回の各関数については、公式ドキュメントの正規表現の項目に詳細に書かれています。こちらも参考に、MySQLでも正規表現を利用してみてください。