MySQL 8.0.4の正規表現で更に遊ぶ（REGEXP_SUBSTR, REGEXP_INSTR）

See also, 日々の覚書: MySQL 8.0.4でMySQLの正規表現演算がだいぶマシになる

---

MySQL 8.0.4で新たに追加された関数として、 REGEXP_SUBSTR と REGEXP_INSTR がある。

REGEXP_REPLACE もあるけどこれはいいや（ mroonga_snippet 的なことができるかもなのでまた別で遊ぶかも）

検索する正規表現にマッチした文字列を返してくれるREGEXP_SUBSTRとその文字列が現れるオフセットを返してくれるREGEXP_INSTR。

特にREGEXP_SUBSTRは面白そうなんだけど、引数が (expr, pat[, pos[, occurrence[, match_type]]]) になっている時点で複数回マッチしたものを配列で受けるなんてやり方はできない。残念…。

という訳で、折角MySQL 8.0なのでCTEを使ってこれを受け取れるんじゃないかテスト。

mysql80 19> SET @pattern := 'MySQL\\s*\\d[\.\\d]+';
Query OK, 0 rows affected (0.00 sec)

mysql80 19> WITH RECURSIVE cte (step, tweet_id, text, str, pos) AS (
    -> SELECT 1 AS step, tweet_id, text, CAST(REGEXP_SUBSTR(text, @pattern, 1, 1) AS char(1024)) AS str, REGEXP_INSTR(text, @pattern, 1, 1) FROM t1 WHERE REGEXP_INSTR(text, @pattern, 1, 1)   
    -> UNION ALL   
    -> SELECT step + 1, tweet_id, text, CAST(REGEXP_SUBSTR(text, @pattern, 1, step + 1) AS char(1024)), REGEXP_INSTR(text, @pattern, 1, step + 1) FROM cte WHERE REGEXP_INSTR(text, @pattern, 1, step + 1)  )
    -> 
    -> SELECT tweet_id, ANY_VALUE(text), GROUP_CONCAT(str), GROUP_CONCAT(pos) FROM cte GROUP BY tweet_id HAVING COUNT(*) > 1 ORDER BY tweet_id LIMIT 5\G
*************************** 1. row ***************************
         tweet_id: 219335904735657984
  ANY_VALUE(text): RT @sh2nd: MySQL 5.1はInnoDBの管理スレッドがos_thread_sleep(1000000);していて、MySQL 5.5はos_event_wait_time_low(srv_timeout_event, 1000000, sig_count) ...
GROUP_CONCAT(str): MySQL 5.1,MySQL 5.5
GROUP_CONCAT(pos): 12,66
*************************** 2. row ***************************
         tweet_id: 255543243696136192
  ANY_VALUE(text): 最近Vadimさん、MySQL5.6にお熱で嬉しい。

MySQL 5.6.7-RC in tpcc-mysql benchmark http://t.co/aBUKFgaL
GROUP_CONCAT(str): MySQL5.6,MySQL 5.6.7
GROUP_CONCAT(pos): 11,30
*************************** 3. row ***************************
         tweet_id: 294125457853513729
  ANY_VALUE(text): RT @i_rethi: ぐぐったらあんまりtcmallocをMySQL5.5で使うケースが日本語では見当たらなかったので書いてみた / MySQL5.5でtcmallocを使用する http://t.co/GuatemFS
GROUP_CONCAT(str): MySQL5.5,MySQL5.5
GROUP_CONCAT(pos): 32,70
*************************** 4. row ***************************
         tweet_id: 298969186275844097
  ANY_VALUE(text): RT @nippondanji: ブログ書きました:  MySQL 5.6正式リリース！！ #mysql56 http://t.co/zGSh0ArK
GROUP_CONCAT(str): MySQL 5.6,mysql56
GROUP_CONCAT(pos): 29,48
*************************** 5. row ***************************
         tweet_id: 301220291202412544
  ANY_VALUE(text): ひょひょっと調べたので書いてみた。

日々の覚書: MySQL5.6のマスターにMySQL5.5(とそれ以前)のスレーブをぶら下げるとエラる http://t.co/kIjOmBdd
GROUP_CONCAT(str): MySQL5.6,MySQL5.5
GROUP_CONCAT(pos): 28,42
5 rows in set (0.17 sec)

textが MySQL\\s*\\d[\.\\d]+ にマッチするもの（MySQL バージョン番号っぽい文字列）のうち、2回以上マッチしそうなもの（ SELECT .. FROM cte .. HAVING COUNT(*) > 1 ）を引っこ抜いてみる。

WITH RECURSIVEで再帰CTEにして、引数の occurrence (=何番目のマッチ) をインクリメントさせている。

有用かどうかは置いておいてMySQLでもこういうことができるようになったかとちょっと感慨深い。

​