MochiuWiki : SUSE, EC, PCB

MySQL - 正規表現

2026年2月15日 (日) 20:55時点におけるWiki (トーク | 投稿記録)による版 (ページの作成:「== 概要 == MySQLは、バージョン3.23.6以降で正規表現をサポートしている。<br> 正規表現は、文字列パターンマッチングのための強力な機能を提供する。<br> <br> MySQL 5.x系では、Henry Spencer氏の正規表現ライブラリが使用されていた。<br> MySQL 8.0以降では、ICU (International Components for UNICODE) ライブラリに移行し、完全なUNICODE対応と高度な正規表現機能を実現し…」)
(差分) ← 古い版 | 最新版 (差分) | 新しい版 → (差分)
📢 Webサイト閉鎖と移転のお知らせ
このWebサイトは2026年9月に閉鎖いたします。
新しい記事は移転先で追加しております。（旧サイトでは記事を追加しておりません）

概要

MySQLは、バージョン3.23.6以降で正規表現をサポートしている。
正規表現は、文字列パターンマッチングのための強力な機能を提供する。

MySQL 5.x系では、Henry Spencer氏の正規表現ライブラリが使用されていた。
MySQL 8.0以降では、ICU (International Components for UNICODE) ライブラリに移行し、完全なUNICODE対応と高度な正規表現機能を実現している。

基本的な正規表現演算子として、REGEXP および RLIKE が提供されている。
これらの演算子は完全に同義であり、SQLのWHERE句やHAVING句で使用できる。

MySQL 8.0以降では、正規表現関数が大幅に拡張された。
REGEXP_LIKE、REGEXP_REPLACE、REGEXP_SUBSTR、REGEXP_INSTR の4つの関数が追加され、文字列操作の柔軟性が大幅に向上した。

バージョン8.0への移行時には、正規表現ライブラリの変更により動作が変わる可能性がある。
特に単語境界メタ文字 (Henry Spencer形式の[[:<:]]および[[:>:]]) は、ICU形式の\bに変更する必要がある。

MariaDBは独自にPCRE (Perl互換正規表現) ライブラリを採用しているため、MySQLとは正規表現の動作が異なる点に注意が必要である。


REGEXP / RLIKE演算子

REGEXP 演算子 および RLIKE 演算子は、文字列が正規表現パターンにマッチするかを判定する。
これら2つの演算子は完全に同義であり、どちらを使用しても同じ結果が得られる。

基本構文を以下に示す。

expr REGEXP pat
expr RLIKE pat


戻り値は以下のとおりである。

  • マッチした場合
    1を返す
  • マッチしなかった場合
    0を返す
  • exprまたはpatにNULLが含まれる場合
    NULLを返す


否定形式として、NOT REGEXPおよびNOT RLIKEも使用できる。

expr NOT REGEXP pat
expr NOT RLIKE pat


MySQL 8.0では、REGEXP 演算子は内部的に REGEXP_LIKE 関数のシノニムとして実装されている。

大文字小文字の区別は、照合順序 (collation) に依存する。
デフォルトの照合順序 (utf8mb4_0900_ai_ci) では、大文字小文字を区別しない。
大文字・小文字を明示的に区別したい場合は、match_typeパラメータで制御する必要がある。

使用例を以下に示す。

 -- 'a'で始まる文字列
 SELECT * FROM users WHERE name REGEXP '^a';
 
 -- 数字を含む文字列
 SELECT * FROM products WHERE code RLIKE '[0-9]';
 
 -- 'test'を含まない文字列
 SELECT * FROM logs WHERE message NOT REGEXP 'test';



正規表現パターン構文

MySQLの正規表現パターンは、複数のメタ文字と構文要素で構成される。

メタ文字一覧

正規表現で特別な意味を持つメタ文字を以下に示す。

メタ文字とその意味
メタ文字 意味 使用例
. 任意の1文字 (デフォルトでは改行以外) a.c → abc、a1c、a_c
^ 文字列の先頭 (または行頭) ^abc → abcで始まる
$ 文字列の末尾 (または行末) abc$ → abcで終わる
* 0回以上の繰り返し ab*c → ac、abc、abbc
+ 1回以上の繰り返し ab+c → abc、abbc
? 0回または1回 ab?c → ac、abc
| 論理和 (OR) abc|def → abcまたはdef
() グループ化とキャプチャ (ab)+ → ab、abab、ababab
[] 文字クラス [abc] → a、b、c
{} 繰り返し回数指定 a{2,4} → aa、aaa、aaaa
\ エスケープ文字 \. → ドット文字そのもの


メタ文字をリテラル文字として使用する場合は、バックスラッシュでエスケープする必要がある。
例えば、ドット文字そのものを検索する場合は、\.と記述する。

文字クラス

文字クラスは、複数の文字のうちいずれか1文字にマッチする。

基本的な文字クラスの構文を以下に示す。

  • [abc]
    a、b、cのいずれか1文字にマッチ
  • [a-z]
    aからzまでの小文字1文字にマッチ
  • [A-Za-z]
    英大文字または英小文字1文字にマッチ
  • [0-9]
    数字1文字にマッチ
  • [^abc]
    a、b、c以外の1文字にマッチ (否定文字クラス)
  • [^0-9]
    数字以外の1文字にマッチ


文字クラス内では、ほとんどのメタ文字が特別な意味を失う。
ただし、ハイフン (-)、キャレット (^)、バックスラッシュ (\) は特別な意味を持つ。

POSIX文字クラス

MySQLは、POSIX準拠の文字クラスをサポートしている。
POSIX文字クラスは、[: 名前 :]の形式で記述する。

POSIX文字クラス一覧
POSIX文字クラス 意味 等価な表現
[:alpha:] 英字 [A-Za-z]
[:digit:] 数字 [0-9]
[:alnum:] 英数字 [A-Za-z0-9]
[:space:] 空白文字 [ \t\r\n\f]
[:upper:] 英大文字 [A-Z]
[:lower:] 英小文字 [a-z]
[:punct:] 句読点 記号文字
[:xdigit:] 16進数字 [0-9A-Fa-f]
[:blank:] スペースとタブ [ \t]
[:print:] 印字可能文字 表示可能文字
[:graph:] 印字可能文字 (空白除く) [:alnum:]と[:punct:]
[:cntrl:] 制御文字 ASCIIコード0-31、127


POSIX文字クラスは、文字クラス内で使用する。
例: alpha:は英字1文字にマッチする。

量指定子

量指定子は、直前のパターンの繰り返し回数を指定する。

量指定子の一覧を以下に示す。

  • *
    0回以上の繰り返し (貪欲)
  • +
    1回以上の繰り返し (貪欲)
  • ?
    0回または1回
  • {n}
    正確にn回
  • {n,}
    n回以上
  • {n,m}
    n回以上、m回以下


デフォルトでは、量指定子は貪欲 (greedy) に動作する。
貪欲モードでは、可能な限り長くマッチする。

非貪欲 (non-greedy) モードにするには、量指定子の後ろに?を付ける。

  • *?
    0回以上の繰り返し (非貪欲)
  • +?
    1回以上の繰り返し (非貪欲)
  • ??
    0回または1回 (非貪欲)
  • {n,}?
    n回以上 (非貪欲)
  • {n,m}?
    n回以上、m回以下 (非貪欲)


エスケープシーケンス

MySQL 8.0以降では、ICUライブラリによる拡張エスケープシーケンスがサポートされている。

エスケープシーケンス一覧 (MySQL 8.0+)
エスケープ 意味 等価な表現
\d 数字 [0-9]
\D 数字以外 [^0-9]
\w 単語文字 (英数字とアンダースコア) [A-Za-z0-9_]
\W 単語文字以外 [^A-Za-z0-9_]
\s 空白文字 [ \t\r\n\f]
\S 空白文字以外 [^ \t\r\n\f]
\b 単語境界 (単語文字と非単語文字の境界)
\B 単語境界以外 (単語境界でない位置)


MySQL 5.xでは、単語境界は[[:<:]]および[[:>:]]で表現されていた。
MySQL 8.0への移行時には、これらを\bに置き換える必要がある。

その他の一般的なエスケープシーケンスを以下に示す。

  • \n
    改行 (LF)
  • \r
    復帰 (CR)
  • \t
    タブ
  • \f
    フォームフィード
  • \v
    垂直タブ
  • \0
    NULL文字



REGEXP_LIKE関数

REGEXP_LIKE関数は、MySQL 8.0で導入された正規表現マッチング関数である。
REGEXP演算子との違いは、match_typeパラメータで詳細な動作制御ができる点である。

構文を以下に示す。

REGEXP_LIKE(expr, pat [, match_type])


パラメータの説明を以下に示す。

  • expr
    検索対象の文字列式
  • pat
    正規表現パターン
  • match_type (省略可能)
    マッチング動作を制御するフラグ文字列


戻り値は以下のとおりである。

  • マッチした場合
    1を返す
  • マッチしなかった場合
    0を返す
  • exprまたはpatにNULLが含まれる場合
    NULLを返す


使用例を以下に示す。

 -- 基本的な使用例
 SELECT REGEXP_LIKE('abc', '^a');
 -- 結果: 1
 
 -- 大文字小文字を区別
 SELECT REGEXP_LIKE('ABC', '^a', 'c');
 -- 結果: 0
 
 -- 大文字小文字を無視
 SELECT REGEXP_LIKE('ABC', '^a', 'i');
 -- 結果: 1
 
 -- 複数行モード
 SELECT REGEXP_LIKE('line1\nline2', '^line2', 'm');
 -- 結果: 1


match_typeパラメータの詳細は、「match_typeパラメータ」セクションを参照すること。


REGEXP_REPLACE関数

REGEXP_REPLACE関数は、MySQL 8.0で導入された正規表現置換関数である。
正規表現にマッチした部分を、指定した文字列で置換する。

構文を以下に示す。

REGEXP_REPLACE(expr, pat, repl [, pos [, occurrence [, match_type]]])


パラメータの説明
パラメータ 説明
expr 検索対象の文字列式
pat 正規表現パターン
repl 置換後の文字列
pos (省略可能、デフォルト: 1) 検索開始位置 (1から始まる)
occurrence (省略可能、デフォルト: 0) 置換対象の出現回数 (0は全て、1は最初、2は2番目...)
match_type (省略可能) マッチング動作を制御するフラグ文字列


後方参照を使用して、キャプチャグループの内容を置換文字列で参照できる。

後方参照
参照 説明
$0 マッチした文字列全体
$1 1番目のキャプチャグループ
$2 2番目のキャプチャグループ
$n n番目のキャプチャグループ


マッチしなかった場合は、元の文字列がそのまま返される。

使用例を以下に示す。

 -- HTMLタグを削除
 SELECT REGEXP_REPLACE('<p>Hello</p>', '<[^>]+>', '');
 -- 結果: 'Hello'
 
 -- 複数スペースを1つに正規化
 SELECT REGEXP_REPLACE('a  b   c', ' {2,}', ' ');
 -- 結果: 'a b c'
 
 -- 日付フォーマットを変換 (YYYY-MM-DD → DD/MM/YYYY)
 SELECT REGEXP_REPLACE('2025-02-15', '(\\d{4})-(\\d{2})-(\\d{2})', '$3/$2/$1');
 -- 結果: '15/02/2025'
 
 -- 最初のマッチのみ置換
 SELECT REGEXP_REPLACE('abc abc abc', 'abc', 'xyz', 1, 1);
 -- 結果: 'xyz abc abc'
 
 -- 2番目のマッチのみ置換
 SELECT REGEXP_REPLACE('abc abc abc', 'abc', 'xyz', 1, 2);
 -- 結果: 'abc xyz abc'


後方参照を使用する際は、バックスラッシュをエスケープする必要がある。
SQLクエリ内では、\\d{4}のように2重のバックスラッシュを使用する。


REGEXP_SUBSTR関数

REGEXP_SUBSTR 関数は、MySQL 8.0で導入された正規表現部分文字列抽出関数である。
正規表現にマッチした部分文字列を返す。

構文を以下に示す。

REGEXP_SUBSTR(expr, pat [, pos [, occurrence [, match_type]]])


パラメータの説明
パラメータ 説明
expr 検索対象の文字列式
pat 正規表現パターン
pos (省略可能、デフォルト: 1) 検索開始位置 (1から始まる)
occurrence (省略可能、デフォルト: 1) 抽出対象の出現回数 (1は最初、2は2番目...)
match_type (省略可能) マッチング動作を制御するフラグ文字列


戻り値
条件 戻り値
マッチした場合 マッチした部分文字列を返す
マッチしなかった場合 NULLを返す


使用例を以下に示す。

 -- 電話番号を抽出
 SELECT REGEXP_SUBSTR('Call 123-456-7890', '[0-9]{3}-[0-9]{3}-[0-9]{4}');
 -- 結果: '123-456-7890'
 
 -- メールアドレスを抽出
 SELECT REGEXP_SUBSTR('Contact: user@example.com', '[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}');
 -- 結果: 'user@example.com'
 
 -- 最初のURLを抽出
 SELECT REGEXP_SUBSTR('Visit https://example.com or https://test.com', 'https?://[^ ]+', 1, 1);
 -- 結果: 'https://example.com'
 
 -- 2番目のURLを抽出
 SELECT REGEXP_SUBSTR('Visit https://example.com or https://test.com', 'https?://[^ ]+', 1, 2);
 -- 結果: 'https://test.com'



REGEXP_INSTR関数

REGEXP_INSTR関数は、MySQL 8.0で導入された正規表現位置検索関数である。
正規表現にマッチした位置を返す。

構文を以下に示す。

REGEXP_INSTR(expr, pat [, pos [, occurrence [, return_option [, match_type]]]])


パラメータの説明
パラメータ 説明
expr 検索対象の文字列式
pat 正規表現パターン
pos (省略可能、デフォルト: 1) 検索開始位置 (1から始まる)
occurrence (省略可能、デフォルト: 1) 検索対象の出現回数 (1は最初、2は2番目...)
return_option (省略可能、デフォルト: 0) 0はマッチ開始位置、1はマッチ終了位置+1を返す
match_type (省略可能) マッチング動作を制御するフラグ文字列


戻り値
条件 戻り値
マッチした場合 マッチした位置 (1から始まる) を返す
マッチしなかった場合 0を返す


使用例を以下に示す。

 -- 'abc'の開始位置を取得
 SELECT REGEXP_INSTR('xyzabcdef', 'abc');
 -- 結果: 4
 
 -- 'abc'の終了位置+1を取得
 SELECT REGEXP_INSTR('xyzabcdef', 'abc', 1, 1, 1);
 -- 結果: 7
 
 -- 数字の開始位置を取得
 SELECT REGEXP_INSTR('abc123def', '[0-9]+');
 -- 結果: 4
 
 -- 2番目の数字グループの位置を取得
 SELECT REGEXP_INSTR('abc123def456ghi', '[0-9]+', 1, 2);
 -- 結果: 10


return_optionを1に設定すると、マッチした部分文字列の終了位置の次の位置が返される。
これは、マッチした部分を抽出する際に便利である。


match_typeパラメータ

match_type パラメータは、正規表現関数の動作を制御するフラグ文字列である。
MySQL 8.0の正規表現関数 (REGEXP_LIKE、REGEXP_REPLACE、REGEXP_SUBSTR、REGEXP_INSTR) で使用できる。

match_typeフラグ一覧
フラグ 意味 説明
c 大文字小文字を区別 (case-sensitive) 大文字と小文字を異なる文字として扱う
i 大文字小文字を無視 (case-insensitive) 大文字と小文字を同じ文字として扱う (デフォルト)
m 複数行モード (multi-line) ^と$が各行の先頭と末尾にマッチする
n ドットが改行にマッチ (newline) .メタ文字が改行文字にもマッチする
u Unix改行のみ (unix-lines) \nのみを改行として認識 (\r\nは改行として扱わない)


複数のフラグを組み合わせる場合は、フラグ文字を連結する。
例: 'im'は大文字小文字を無視し、かつ複数行モードを有効にする。

フラグcとiは相互排他的である。
両方を指定した場合は、cフラグが優先される。

使用例を以下に示す。

 -- 大文字小文字を区別
 SELECT REGEXP_LIKE('ABC', 'abc', 'c');
 -- 結果: 0
 
 -- 大文字小文字を無視
 SELECT REGEXP_LIKE('ABC', 'abc', 'i');
 -- 結果: 1
 
 -- 複数行モード
 SELECT REGEXP_LIKE('line1\nline2\nline3', '^line2', 'm');
 -- 結果: 1
 
 -- ドットが改行にマッチ
 SELECT REGEXP_LIKE('line1\nline2', 'line1.line2', 'n');
 -- 結果: 1
 
 -- 複数フラグの組み合わせ
 SELECT REGEXP_LIKE('Line1\nLINE2', '^line2', 'im');
 -- 結果: 1


デフォルトでは、iフラグが有効である。
ただし、照合順序がバイナリ (binary) の場合は、cフラグが暗黙的に有効になる。


UNICODE対応

MySQL 8.0以降では、ICUライブラリにより完全なUNICODE対応が実現されている。

ICUライブラリへの移行

MySQL 5.x系では、Henry Spencer氏の正規表現ライブラリが使用されていた。
MySQL 8.0.4以降では、ICU (International Components for UNICODE) ライブラリに移行した。

ICUライブラリの主なメリットを以下に示す。

  • 完全なUNICODE対応
    UNICODE文字プロパティの使用
  • 正規表現機能の拡張
    より高度な正規表現構文のサポート
  • マルチバイト文字の正確な処理
    文字単位での処理 (バイト単位ではない)


UNICODE文字クラス

MySQL 8.0では、UNICODE文字プロパティを使用した文字クラスがサポートされている。
\p{プロパティ名} の形式で記述する。

UNICODE文字プロパティ一覧
プロパティ 意味 例
\p{L} 文字 (Letter) 全ての文字 (アルファベット、漢字等)
\p{N} 数字 (Number) 全ての数字 (0-9、全角数字等)
\p{Z} セパレータ (Separator) 空白、改行等
\p{P} 句読点 (Punctuation) .,;:!?等
\p{S} シンボル (Symbol) 記号文字 (+, =, $等)
\p{M} マーク (Mark) 結合文字 (アクセント記号等)
\p{C} その他 (Other) 制御文字等


否定形式として、\P{プロパティ名} も使用できる。
例: \P{L}は文字以外にマッチする。

使用例を以下に示す。

 -- 文字のみを抽出
 SELECT REGEXP_REPLACE('Hello123世界', '\\P{L}', '');
 -- 結果: 'Hello世界'
 
 -- 数字のみを抽出
 SELECT REGEXP_SUBSTR('Price: 1234 yen', '\\p{N}+');
 -- 結果: '1234'
 
 -- 句読点を削除
 SELECT REGEXP_REPLACE('Hello, World!', '\\p{P}', '');
 -- 結果: 'Hello World'


照合順序との関係

正規表現の大文字小文字の区別は、照合順序 (collation) に依存する。

MySQL 8.0のデフォルト照合順序は、utf8mb4_0900_ai_ci である。

utf8mb4_0900_ai_ci の構成要素
要素 説明
utf8mb4 4バイトUNICODE文字セット (絵文字対応)
0900 UNICODE 9.0.0準拠
ai accent insensitive (アクセント記号を無視)
ci case insensitive (大文字小文字を無視)


大文字小文字を明示的に区別する場合は、match_typeパラメータでcフラグを指定する。

4バイト文字の扱い

MySQL 8.0では、絵文字等の4バイトUNICODE文字 (Supplementary Multilingual Plane) が正しく処理される。

ただし、文字列関数の位置指定では、4バイト文字が16ビット単位 (サロゲートペア) でカウントされる場合がある。
これは、MySQLの内部表現がUTF-16ベースであるためである。

使用例を以下に示す。

 -- 絵文字を含む文字列
 SELECT REGEXP_LIKE('Hello 😀 World', '😀');
 -- 結果: 1
 
 -- 絵文字を削除
 SELECT REGEXP_REPLACE('Test 🚀 Message 🎉', '[\\x{1F300}-\\x{1F9FF}]', '');
 -- 結果: 'Test  Message '



パフォーマンスと制限事項

インデックスの非使用

正規表現演算子および関数は、インデックスを使用しない。
そのため、大量の行に対して正規表現検索を行うと、フルテーブルスキャンが発生する。

パフォーマンスへの影響を以下に示す。

  • フルテーブルスキャン
    全行を走査するため、テーブルサイズに比例して処理時間が増加
  • CPU負荷
    正規表現のマッチング処理はCPU集約的


代替手段として、以下を検討する。

  • LIKE演算子
    プレフィックスマッチ (LIKE 'abc%') はインデックスを使用可能
  • FULLTEXT INDEX
    全文検索インデックスを使用した高速な文字列検索


正規表現のコンパイルコスト

正規表現パターンは、マッチング前にコンパイルされる。
複雑な正規表現パターンは、コンパイルに時間がかかる。

プリペアドステートメントを使用することで、正規表現のコンパイルを1回だけに抑えることができる。

 PREPARE stmt FROM 'SELECT * FROM users WHERE name REGEXP ?';
 SET @pattern = '^[A-Z]';
 EXECUTE stmt USING @pattern;


regexp_stack_limit

正規表現エンジンのスタックメモリ制限を設定するシステム変数である。

デフォルト値は8[MB] (8388608バイト) である。
複雑な正規表現や長い文字列を処理する際に、スタックオーバーフローを防ぐために使用される。

設定例を以下に示す。

 -- セッションレベルで設定
 SET SESSION regexp_stack_limit = 16777216;
 
 -- グローバルレベルで設定
 SET GLOBAL regexp_stack_limit = 16777216;


regexp_time_limit

正規表現エンジンの実行ステップ制限を設定するシステム変数である。

デフォルト値は32ステップである。
複雑な正規表現や長い文字列を処理する際に、無限ループや過度な処理時間を防ぐために使用される。

設定例を以下に示す。

 -- セッションレベルで設定
 SET SESSION regexp_time_limit = 64;
 
 -- グローバルレベルで設定
 SET GLOBAL regexp_time_limit = 64;


時間制限を超過すると、以下のエラーが発生する。

ERROR 3699 (HY000): Timeout exceeded in regular expression match.


代替手段の検討

正規表現のパフォーマンスが問題になる場合は、以下に示す代替手段を検討する。

正規表現の代替手段
代替手段 説明
LIKE演算子 プレフィックスマッチや単純なパターンにはLIKEを使用
プレフィックスマッチ (LIKE 'abc%') はインデックスを使用可能
FULLTEXT INDEX 全文検索が必要な場合はFULLTEXT INDEXを使用
自然言語検索やブーリアンモード検索が可能
アプリケーション側での処理 複雑な文字列処理はアプリケーション側で実装
プログラミング言語の正規表現ライブラリを使用



バージョン互換性

MySQL 5.x系からMySQL 8.0への移行時には、正規表現の動作変更に注意が必要である。

MySQL 5.x → 8.0移行時の注意点

MySQL 8.0では、正規表現ライブラリがHenry Spencer氏のライブラリからICUライブラリに変更された。
この変更により、一部の正規表現パターンが動作しなくなる可能性がある。

主な変更点を以下に示す。

MySQL 8.0における正規表現ライブラリの主な変更点
変更点 説明
単語境界メタ文字 [[:<:]] および [[:>:]] は非サポート
\bに変更する必要がある。
メタ文字のエスケープ要件 一部のメタ文字のエスケープ要件が変更された。
ドットと改行の動作 デフォルトではドット (.) は改行にマッチしない。
MySQL 5.xでは一部の状況でマッチした。
マルチバイト文字処理 バイト単位から文字単位の処理に変更


単語境界の移行

MySQL 5.xでは、単語境界を [[:<:]] および [[:>:]] で表現していた。
MySQL 8.0では、これらは非サポートとなり、\bに変更する必要がある。

移行例を以下に示す。

 -- MySQL 5.x
 SELECT * FROM words WHERE word REGEXP '[[:<:]]test[[:>:]]';
 
 -- MySQL 8.0
 SELECT * FROM words WHERE word REGEXP '\\btest\\b';


SQLクエリ内では、バックスラッシュをエスケープするため、\\bと記述する。

動作変更の確認

移行前に、既存の正規表現パターンが正しく動作するかを確認する必要がある。

確認手順を以下に示す。

  1. 既存のSQLクエリから、正規表現を使用している箇所を抽出する。
  2. テスト環境でMySQL 8.0を構築する。
  3. 抽出した正規表現パターンをテストする。
  4. エラーが発生した場合は、パターンを修正する。


特に、[[:<:]]、[[:>:]]、エスケープシーケンス、ドットの動作を重点的に確認する。

MariaDBとの違い

MariaDBは、PCRE (Perl Compatible Regular Expressions) ライブラリを採用している。
MySQLのICUライブラリとは異なるため、正規表現の動作が異なる場合がある。

主な違いを以下に示す。

MySQLとMariaDBの正規表現の違い
項目 MySQL 8.0 MariaDB
使用ライブラリ ICU PCRE
正規表現構文 ICU構文をサポート Perl互換構文をサポート
後方参照 $1、$2等 \1、\2等


MySQLからMariaDBへの移行、またはその逆の移行時には、正規表現パターンの互換性を確認する必要がある。


サンプルクエリ

メールアドレスのバリデーション

メールアドレスの形式をチェックする例を示す。

 -- 基本的なメールアドレスバリデーション
 SELECT
    email,
    REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$') AS is_valid
 FROM users;
 
 -- 不正なメールアドレスを検出
 SELECT email
 FROM users
 WHERE NOT REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$');


電話番号の抽出

テキストから電話番号を抽出する例を示す。

 -- XXX-XXX-XXXX形式の電話番号を抽出
 SELECT
    message,
    REGEXP_SUBSTR(message, '[0-9]{3}-[0-9]{3}-[0-9]{4}') AS phone_number
 FROM messages;
 
 -- 複数の電話番号形式に対応
 SELECT
    message,
    REGEXP_SUBSTR(message, '[0-9]{3}-[0-9]{3}-[0-9]{4}|\\([0-9]{3}\\) [0-9]{3}-[0-9]{4}') AS phone_number
 FROM messages;


HTMLタグの削除

HTMLタグを削除してプレーンテキストを抽出する例を示す。

 -- HTMLタグを削除
 SELECT
    html_content,
    REGEXP_REPLACE(html_content, '<[^>]+>', '') AS plain_text
 FROM articles;
 
 -- HTMLエンティティも削除
 SELECT
    REGEXP_REPLACE(
       REGEXP_REPLACE(html_content, '<[^>]+>', ''),
       '&[a-zA-Z]+;',
       ''
    ) AS plain_text
 FROM articles;


日付フォーマットの変換

日付形式を変換する例を示す。

 -- YYYY-MM-DD → DD/MM/YYYY
 SELECT
    date_str,
    REGEXP_REPLACE(date_str, '(\\d{4})-(\\d{2})-(\\d{2})', '$3/$2/$1') AS formatted_date
 FROM events;
 
 -- MM/DD/YYYY → YYYY-MM-DD
 SELECT
    date_str,
    REGEXP_REPLACE(date_str, '(\\d{2})/(\\d{2})/(\\d{4})', '$3-$1-$2') AS iso_date
 FROM events;


複数スペースの正規化

複数の連続するスペースを1つのスペースに置換する例を示す。

 -- 複数スペースを1つに正規化
 SELECT
    text,
    REGEXP_REPLACE(text, ' {2,}', ' ') AS normalized_text
 FROM documents;
 
 -- 行頭・行末のスペースも削除
 SELECT
    REGEXP_REPLACE(
       REGEXP_REPLACE(text, '^ +| +$', ''),
       ' {2,}',
       ' '
    ) AS trimmed_text
 FROM documents;


URLの抽出

テキストからURLを抽出する例を示す。

 -- HTTPまたはHTTPSのURLを抽出
 SELECT
    comment,
    REGEXP_SUBSTR(comment, 'https?://[^ ]+') AS url
 FROM comments;
 
 -- 全てのURLを抽出 (複数URLがある場合は最初のもの)
 SELECT
    comment,
    REGEXP_SUBSTR(comment, 'https?://[a-zA-Z0-9./?=_-]+') AS first_url
 FROM comments;


郵便番号のバリデーション

郵便番号の形式をチェックする例を示す。

 -- 日本の郵便番号 (XXX-XXXX形式)
 SELECT
    postal_code,
    REGEXP_LIKE(postal_code, '^[0-9]{3}-[0-9]{4}$') AS is_valid
 FROM addresses;
 
 -- 米国の郵便番号 (XXXXX形式またはXXXXX-XXXX形式)
 SELECT
    zip_code,
    REGEXP_LIKE(zip_code, '^[0-9]{5}(-[0-9]{4})?$') AS is_valid
 FROM us_addresses;


IPアドレスの検証

IPv4アドレスの形式をチェックする例を示す。

 -- IPv4アドレスの基本的な形式チェック
 SELECT
    ip_address,
    REGEXP_LIKE(ip_address, '^([0-9]{1,3}\\.){3}[0-9]{1,3}$') AS is_valid_format
 FROM server_logs;
 
 -- より厳密なIPv4アドレスチェック (0-255の範囲)
 SELECT
    ip_address,
    REGEXP_LIKE(
       ip_address,
       '^(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$'
    ) AS is_valid_ipv4
 FROM server_logs;


パスワード強度のチェック

パスワードの強度をチェックする例を示す。

 -- 最低8文字、英大文字・小文字・数字を含む
 SELECT
    password,
    REGEXP_LIKE(password, '^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9]).{8,}$') AS is_strong
 FROM user_passwords;
 
 -- 英大文字・小文字・数字・記号を含む
 SELECT
    password,
    REGEXP_LIKE(password, '^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[^a-zA-Z0-9]).{8,}$') AS is_very_strong
 FROM user_passwords;


カンマ区切り文字列の分割

カンマ区切り文字列から要素を抽出する例を示す。

 -- 最初の要素を抽出
 SELECT
    csv_data,
    REGEXP_SUBSTR(csv_data, '[^,]+', 1, 1) AS first_element
 FROM data_table;
 
 -- 2番目の要素を抽出
 SELECT
    csv_data,
    REGEXP_SUBSTR(csv_data, '[^,]+', 1, 2) AS second_element
 FROM data_table;
 
 -- 3番目の要素を抽出
 SELECT
    csv_data,
    REGEXP_SUBSTR(csv_data, '[^,]+', 1, 3) AS third_element
 FROM data_table;



「http://mochiuwiki.e2.valueserver.jp/index.php?title=MySQL_-_正規表現&oldid=14302」から取得