MySQL カラム中の文字列を正規表現で抽出する戦略 x3

背景

あるカラムに XML, HTML が突っ込んである MySQL のテーブルに対して、XML, HTML 中の要素を条件に検索・集計・分析することを仕事で要求された。そういう分析が発生するケースを想定して、テーブルを設計いただきたいものだが。「とりあえずなんとかならんか」と言われて対応するために机上検討したノート。

幸い、対象が MySQL 8 には正規表現が使える関数 REGEXP_SUBSTR(), REGEXP_REPLACE() がある（MySQL がいつから正規表現関数をサポートしているのか私は知らない）。SUBSTR_INDEX() でごりごりやらずに済ませたい（植木算は小学生の時から第の苦手。）

お急ぎの方は、記事の下の方から読まれた方が良いかも。

検証環境

このノートの検証は、以下の環境で実施した。

項目	値	注
OS	CentOS Linux release 7.9.2009 (Core)	VirtualBox/Vagrant 上
MySQL	Ver 8.0.34 for Linux on x86_64 (MySQL Community Server - GPL)	2023-08-06 にインストールしたらこのバージョンになっただけ

検証方法

• /usr/bin/mysql で 以下のように定義されたユーザー定義変数 @url から3つのURLパラメータ　'xxx', 'yyy', 'blahblah' を正規表現関数 REGEXP_SUBSTR(), REGEXP_REPLACE() を使って抽出することを試みる。

mysql> select @url;
+-----------------------------------------------------------------+
| @url                                                            |
+-----------------------------------------------------------------+
| https://websitenowhere/path/?item1=xxx&item2=yyy&item3=blahblah |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL 8.0 正規表現関数のドキュメント

• MySQL 8.0 ドキュメント 12.8.2 正規表現 REGEXP_SUBSTR()
• MySQL 8.0 ドキュメント 12.8.2 正規表現 REGEXP_REPLACE()

文字列抽出の戦略と検証結果

以下の3つの方法を(ネットで見つけ|思いつき)、検証してみた。

1. 後読み・先読みサンドイッチ型

• 抽出対象の前後のパターンを「後読み（lookbehind）」「先読み（lookahead）」で定義し、抽出対象のみを REGEXP_SUBSTR() で抜き出す。
• XML, HTML のタグを lookbehind/lookahead してタグ要素を抜き出すのに親和性が良い。
• この手法を紹介する記事は多かった。
  例 stack overflow: Can MySQL return the portion of the string matching a regular expression?

mysql> select
    ->   regexp_substr(@url, '(?<=item1=).+?(?=&)') as item1
    ->  ,regexp_substr(@url, '(?<=item2=).+?(?=&)') as item2
    ->  ,regexp_substr(@url, '(?<=item3=).+?$')     as item3
    -> ;
+-------+-------+----------+
| item1 | item2 | item3    |
+-------+-------+----------+
| xxx   | yyy   | blahblah |
+-------+-------+----------+
1 row in set (0.00 sec)

• このアプローチはエレガントなのだが、後読み・先読みの正規表現に greedy な量的指定子 +, * を使うととたんに、以下のエラーをくらった。"Many regular expression libraries do only allow strict expressions to be used in look behind assertions" (この記事から引用) ということらしい。

ERROR 3695 (HY000): The look-behind assertion exceeds the limit in regular expression.

2. 対象前後消去型

• 抽出対象のみを残すよう、その前後を消してしまう。カラム（全体文字列）の最初から抽出対象までの正規表現と、抽出対象の直後からカラムの最後までの正規表現を REGEXP_REPLACEを使って空文字列 '' に置き換える。
• 自分で思いついた二番目の方法。荒っぽいがけっこうコンパクトに書ける。

mysql> select
    ->   regexp_replace(@url, '^https://.+?item1=|&item2=.+$', '') as item1
    ->  ,regexp_replace(@url, '^https://.+&item2=|&item3=.+$', '') as item2
    ->  ,regexp_replace(@url, '^https://.+&item3=',            '') as item3
    -> ;
+-------+-------+----------+
| item1 | item2 | item3    |
+-------+-------+----------+
| xxx   | yyy   | blahblah |
+-------+-------+----------+
1 row in set (0.00 sec)

3. キャプチャグループ抽出型

• カラム全体とマッチする正規表現中に、抽出対象のキャプチャグループを定義し（カッコでくくる）、REGEXP_REPLACE を使って、後方参照したキャプチャグループでカラム全体を置き換える。
• キャプチャグループは $1,$2,... で後方参照できる。
• 真っ先に思いついた方法だが、MySQL の正規表現での後方参照（キャプチャグループの参照方法）を本家のドキュメントで見つけられず、結局、いつもの stack overflow のお世話になった。
• descriptive な記述ができるので、私はこの方法を一番好んでいる。
• 参照した記事。stack overflow: How do I refer to capture groups in a MySQL regex?

mysql> select
    ->   regexp_replace(@url, '^https://.+?item1=(.+?)&item2=(.+?)&item3=(.+)$', '$1') as item1
    ->  ,regexp_replace(@url, '^https://.+?item1=(.+?)&item2=(.+?)&item3=(.+)$', '$2') as item2
    ->  ,regexp_replace(@url, '^https://.+?item1=(.+?)&item2=(.+?)&item3=(.+)$', '$3') as item3
    -> ;
+-------+-------+----------+
| item1 | item2 | item3    |
+-------+-------+----------+
| xxx   | yyy   | blahblah |
+-------+-------+----------+
1 row in set (0.00 sec)

• 名前付きキャプチャグループも使えた。

mysql> select
    ->   regexp_replace(@url, '^.+?item1=(?<item1>.+?)&.+$', '${item1}') as item1
    ->  ,regexp_replace(@url, '^.+?item2=(?<item2>.+?)&.+$', '${item2}') as item2
    ->  ,regexp_replace(@url, '^.+?item3=(?<item3>.+)$',     '${item3}') as item3
    -> ;
+-------+-------+----------+
| item1 | item2 | item3    |
+-------+-------+----------+
| xxx   | yyy   | blahblah |
+-------+-------+----------+
1 row in set (0.00 sec)

考察

• キャプチャグループ抽出型 がいちばん汎用性があるように思う。名前付きキャプチャグループに Ruby, Python で慣れ切ってしまっているだけかもしれないが。
• 後読み・先読みサンドイッチ型 で使える正規表現に制約があるのは不便だが、システムにかける負荷を考えるとさもありなん、というところか。固定長のタグ文字列で囲まれた要素を抜き出す用途に限定していても使える場面は多いだろう。（ <em>..</em> の要素を抜き出すとか）。
• 本日行った検証は、MySQL の文法的に実行が可能かどうかを検証しただけのものである。正規表現を使ったクエリ記述方法の参考とされる方は、これらの記法のクエリを実行した場合に、データベースにかかる実際の負荷 についてじゅうぶん事前調査の上使用されたい。