MySQL 正規表現の完全ガイド｜基本・応用・パフォーマンス改善まで徹底解説

By 佐川 直弘 | Naohiro Sagawa (運営者)

※記事内に広告を含みます。Amazonアソシエイトとして適格販売により収入を得ています。　

1. はじめに

MySQLでの正規表現の活用

MySQLは、データの検索や操作を行うための強力なデータベース管理システムですが、より柔軟な検索を可能にするために「正規表現（REGEXP）」を使用することができます。正規表現を活用すれば、特定のパターンに一致するデータを効率的に抽出したり、データの整形や検証を行うことができます。

MySQLで正規表現を使うメリット

MySQLの正規表現を使用することで、以下のようなメリットがあります：

• 高度な検索が可能：特定の文字列やパターンを含むデータを簡単に抽出できる。
• データの整合性をチェック：入力データのバリデーション（例：メールアドレスの形式チェック）に利用可能。
• LIKE演算子よりも強力：ワイルドカード（% や _）を使うLIKEよりも、複雑な検索条件を柔軟に定義できる。

この記事の目的と構成

本記事では、MySQLにおける正規表現の基本的な使い方から、応用的な活用法、パフォーマンスの最適化方法、さらにはMySQL 8.0以降で追加された新しい正規表現関数までを解説します。実践的な例を交えながら、初心者でも理解しやすい内容になっています。

本記事の構成：

1. MySQLの正規表現の基本と構文
2. MySQL 8.0以降で追加された正規表現関数
3. 実践的な応用例（バリデーション・データ抽出・フォーマット変換）
4. 正規表現使用時の注意点（パフォーマンス・セキュリティ）
5. FAQ（よくある質問）
6. まとめ

2. MySQLにおける正規表現の基本と構文

MySQLで正規表現を使用する方法

MySQLでは、正規表現を使用するために REGEXP 演算子（または RLIKE）を用います。これは LIKE 演算子と同様に、ある列の値が指定した正規表現パターンに一致するかを判定するために使われます。

基本構文

SELECT * FROM テーブル名 WHERE カラム名 REGEXP '正規表現';

または

SELECT * FROM テーブル名 WHERE カラム名 RLIKE '正規表現';

RLIKE は REGEXP の別名で、どちらを使っても同じ動作をします。

使用例
例えば、名前の中に「山」という文字が含まれるデータを検索する場合：

SELECT * FROM users WHERE name REGEXP '山';

基本的な正規表現パターン

MySQLの正規表現では、以下のようなパターンが使用できます。

LIKE演算子との違い

MySQLには LIKE 演算子もあり、単純なパターンマッチングには LIKE を使うことが一般的ですが、 REGEXP は LIKE よりも強力で、より複雑な条件での検索が可能です。

例1: LIKE演算子の使用

SELECT * FROM users WHERE name LIKE '%山%';

• LIKE の場合、 山 を含むデータを検索できるが、単純なワイルドカード（% や _）しか使えない。

例2: REGEXP演算子の使用

SELECT * FROM users WHERE name REGEXP '^山';

• REGEXP を使えば「山で始まる名前」のデータのみを抽出できる。

複数のパターンを指定する方法

| を使用すると、複数のパターンを OR 条件で指定できます。

例: 「佐藤」または「田中」という姓のデータを取得

SELECT * FROM users WHERE name REGEXP '佐藤|田中';

否定の検索

^ を角括弧内で使用すると「指定した文字を含まない」データを取得できます。

例: 「山」以外の文字が含まれるデータ

SELECT * FROM users WHERE name REGEXP '^[^山]';

この場合、 山 で始まらない名前のデータが検索されます。

まとめ

• MySQLでは REGEXP を使用して、通常の LIKE よりも強力なパターン検索が可能。
• 基本的な構文（ . , ^ , $ , [] , * , + など）を理解することで、柔軟な検索が可能。
• LIKE との違いを理解し、適切に使い分けることが重要。
• | を使用すると複数パターンの検索ができる。
• 否定のパターン [ ] を活用すると、特定の文字を含まないデータを検索できる。

3. MySQL 8.0以降で追加された正規表現関数

MySQL 8.0では、従来の REGEXP 演算子に加え、 より柔軟な正規表現検索を可能にする4つの新しい関数 が追加されました。これにより、検索結果の位置取得や文字列の抽出・置換などが可能になり、正規表現の活用範囲が大幅に広がりました。

本セクションでは、 新たに追加された正規表現関数 をそれぞれ詳しく解説し、具体的な使用例を紹介します。

3.1 REGEXP_LIKE()

概要

REGEXP_LIKE() は REGEXP 演算子と同様に、指定したカラムの値が 特定の正規表現パターンにマッチするかどうか を判定します。

構文

REGEXP_LIKE(カラム名, '正規表現パターン' [, フラグ])

• カラム名：検索対象のカラム
• '正規表現パターン'：検索条件となる正規表現
• フラグ（省略可）：大文字・小文字の区別を設定（i を指定すると大文字小文字を区別しない）

使用例

「メールアドレスが gmail.com か yahoo.co.jp で終わるユーザーを取得」

SELECT * FROM users WHERE REGEXP_LIKE(email, '(@gmail\.com|@yahoo\.co\.jp)$');

「大文字小文字を区別せずに ‘admin’ を含むユーザー名を検索」

SELECT * FROM users WHERE REGEXP_LIKE(username, 'admin', 'i');

✅ REGEXP_LIKE() は、 REGEXP 演算子と同じ用途ですが、より標準SQLに適した書き方が可能になります。

3.2 REGEXP_INSTR()

概要

REGEXP_INSTR() は、 指定した文字列の中で、正規表現にマッチする位置（開始インデックス）を取得 する関数です。

構文

REGEXP_INSTR(カラム名, '正規表現パターン' [, 開始位置, 出現回数, 0または1, フラグ])

• 開始位置（省略可）：検索を開始する位置（デフォルトは1）
• 出現回数（省略可）：何番目の一致を取得するか（デフォルトは1）
• 0または1（省略可）：0（開始位置を返す）または 1（一致した部分の末尾を返す）
• フラグ（省略可）：i を指定すると大文字小文字を区別しない

使用例

「電話番号の先頭に 090 や 080 がある場合、その開始位置を取得」

SELECT phone, REGEXP_INSTR(phone, '^(090|080)') AS match_pos FROM users;

「メールアドレスのドメイン部分の開始位置を取得」

SELECT email, REGEXP_INSTR(email, '@') AS domain_start FROM users;

• 結果として @ が見つかった位置（例：user@example.com の場合 5）が返る。

✅ REGEXP_INSTR() を使うと、特定のパターンの 位置情報を取得できる ため、文字列処理の幅が広がります。

3.3 REGEXP_SUBSTR()

概要

REGEXP_SUBSTR() は、 文字列の中から、正規表現パターンに一致する部分を抽出 する関数です。

構文

REGEXP_SUBSTR(カラム名, '正規表現パターン' [, 開始位置, 出現回数, フラグ])

• 出現回数（省略可）：n番目の一致を取得（デフォルトは1）
• フラグ（省略可）：大文字小文字の区別（i で無視）

使用例

「メールアドレスのドメイン部分のみを抽出」

SELECT email, REGEXP_SUBSTR(email, '@[a-zA-Z0-9.-]+') AS domain FROM users;

「文中の最初の数字列を取得」

SELECT message, REGEXP_SUBSTR(message, '[0-9]+') AS first_number FROM logs;

✅ REGEXP_SUBSTR() を使うことで、特定のパターンを抽出し、データの整理や加工に活用できます。

3.4 REGEXP_REPLACE()

概要

REGEXP_REPLACE() は、 正規表現にマッチする部分を別の文字列に置換 する関数です。

構文

REGEXP_REPLACE(カラム名, '正規表現パターン', '置換文字列' [, 出現回数, フラグ])

• 置換文字列：マッチした部分を置き換える内容
• 出現回数（省略可）：n番目のマッチのみを置換（省略時はすべて置換）
• フラグ（省略可）：i を指定すると大文字小文字を区別しない

使用例

「電話番号の -（ハイフン）を削除」

SELECT phone, REGEXP_REPLACE(phone, '-', '') AS clean_phone FROM users;

「HTMLタグを除去」

SELECT comment, REGEXP_REPLACE(comment, '<[^>]+>', '') AS clean_text FROM reviews;

✅ REGEXP_REPLACE() を使えば、 データのフォーマット変換やクリーニングが容易に行えます。

3.5 まとめ

4. MySQL正規表現の実践的な応用例

MySQLの正規表現を活用することで、 データのバリデーション、 特定のデータ抽出、 フォーマット変換 など、業務でのデータ処理を効率化できます。本セクションでは、実際の業務で役立つユースケースを紹介し、各例のSQLコードと解説を行います。

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

概要

データベースに保存されているメールアドレスが、適切な形式であるかどうかをチェックするために正規表現を使用します。

使用するSQL

SELECT email FROM users 
WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';

解説

• ^[a-zA-Z0-9._%+-]+ → @ の前に、英数字・ドット・アンダースコア・プラス記号が1文字以上ある
• @[a-zA-Z0-9.-]+ → @ の後にドメイン名が含まれる
• \.[a-zA-Z]{2,}$ → 末尾に 2文字以上のTLD（Top Level Domain）（例: .com, .jp, .net など）がある

このクエリを使えば、誤ったメールアドレス（例：user@@example.com、user@.com）を除外 できます。

4.2 日本の電話番号フォーマットのチェック

概要

日本の一般的な電話番号（例: 090-1234-5678、03-1234-5678）が正しいフォーマットかを判定します。

使用するSQL

SELECT phone FROM users 
WHERE phone REGEXP '^(0[789]0-[0-9]{4}-[0-9]{4}|0[1-9]-[0-9]{4}-[0-9]{4})$';

解説

• 0[789]0-[0-9]{4}-[0-9]{4} → 携帯番号 (090-xxxx-xxxx、080-xxxx-xxxx など)
• 0[1-9]-[0-9]{4}-[0-9]{4} → 固定電話 (03-xxxx-xxxx、06-xxxx-xxxx など)

このように、フォーマットの統一が必要な場面で正規表現が役立ちます。

4.3 クレジットカード番号のフォーマットチェック

概要

クレジットカード番号（Visa、Mastercard、American Expressなど）の形式を検証します。

使用するSQL

SELECT card_number FROM payments 
WHERE card_number REGEXP '^(4[0-9]{12}(?:[0-9]{3})?|5[1-5][0-9]{14}|3[47][0-9]{13})$';

解説

• ^4[0-9]{12}(?:[0-9]{3})?$ → Visa（16桁または13桁）
• ^5[1-5][0-9]{14}$ → Mastercard（16桁）
• ^3[47][0-9]{13}$ → American Express（15桁）

このクエリを使えば、誤ったカード番号（桁数が足りない、頭の番号が不正）をデータベース内で排除 できます。

4.4 HTMLタグの削除

概要

ユーザーが入力したデータの中にHTMLタグが含まれている場合、それを削除し、プレーンテキストに変換 します。

使用するSQL

SELECT REGEXP_REPLACE(comment, '<[^>]+>', '') AS clean_comment FROM reviews;

解説

• '<[^>]+>' → < と > の間にある全てのHTMLタグを削除

例

ユーザーコメントやブログ投稿でのHTMLタグ除去に有効 です。

4.5 郵便番号のフォーマットチェック（日本）

概要

日本の郵便番号（例: 123-4567）が正しい形式かどうかを検証します。

使用するSQL

SELECT postal_code FROM addresses 
WHERE postal_code REGEXP '^[0-9]{3}-[0-9]{4}$';

解説

• ^[0-9]{3}-[0-9]{4}$ → 「3桁-4桁」のフォーマットを持つ郵便番号

この正規表現を使うことで、データの一貫性を保ち、誤ったフォーマットを防ぐことができます。

4.6 ユーザー名のフィルタリング（禁止ワードの検出）

概要

登録時にユーザー名に特定の禁止ワード（例: admin、root、system）が含まれていないかをチェックします。

使用するSQL

SELECT username FROM users 
WHERE username REGEXP 'admin|root|system';

解説

• admin|root|system → これらの単語が含まれるユーザー名を検出

このクエリを活用すれば、システム管理者専用の名称を一般ユーザーが使用するのを防げます。

まとめ

• メールアドレス、電話番号、クレジットカード番号のバリデーション に正規表現が活用できる。
• HTMLタグの削除やデータのフォーマット変換 にも応用可能。
• 禁止ワードのフィルタリングや郵便番号チェック など、データベースの安全性・一貫性を向上させるのに有効。

5. 正規表現使用時の注意点（パフォーマンス・セキュリティ）

MySQLで正規表現を使用すると、強力なデータ検索やデータクレンジングが可能になります。しかし、 不適切な使用はパフォーマンスの低下やセキュリティリスクを引き起こす可能性 があります。本セクションでは、MySQLの正規表現を安全かつ効率的に活用するための注意点について解説します。

5.1 正規表現のパフォーマンス最適化

MySQLの正規表現検索は、便利な一方で インデックスが適用されないため、処理速度が遅くなることがある というデメリットがあります。

パフォーマンス向上のための対策

1. LIKE や FULLTEXT インデックスを併用する

   SELECT * FROM users WHERE email LIKE '%gmail.com';

2. シンプルな正規表現を使用する

   SELECT * FROM users WHERE name REGEXP '^admin|admin$';

3. 事前に対象データをフィルタリングする

   SELECT * FROM users WHERE email LIKE 'a%' AND email REGEXP '^[a-zA-Z0-9._%+-]+@gmail\.com$';

5.2 セキュリティリスクと対策

1. 正規表現のDoS攻撃（ReDoS）

• .* のような無制限のマッチを避ける。
• ^ や $ を利用し検索範囲を限定する。
• ユーザー入力を直接 REGEXP に渡さない。

2. SQLインジェクションと正規表現

❌ 危険なクエリ

SELECT * FROM users WHERE username REGEXP '$input';

✅ 安全なクエリ（プレースホルダを使用）

$stmt = $pdo->prepare("SELECT * FROM users WHERE username REGEXP ?");
$stmt->execute([$sanitized_input]);

5.3 バージョン間の互換性

まとめ

• REGEXP はインデックスが効かないため、パフォーマンス対策が重要。
• ReDoS（正規表現DoS攻撃）を防ぐため、過度なワイルドカードの使用を避ける。
• SQLインジェクションを防ぐため、ユーザー入力をそのまま REGEXP に適用しない。

6. FAQ（よくある質問）

MySQLの正規表現（REGEXP）を使用する際によくある質問をまとめました。これらの疑問を解決することで、正規表現をより効果的に活用できるようになります。

6.1 MySQLで使用できる正規表現のパターンは？

6.2 LIKEとREGEXPの違いは？

✅ LIKE の方が高速なので、簡単な検索には LIKE を優先し、複雑な検索が必要な場合に REGEXP を使用すると良いでしょう。

6.3 MySQL 5.xとMySQL 8.xでの正規表現の違いは？

6.4 正規表現が期待通りに動作しない場合の対処法は？

✅ チェックポイント

1. エスケープが適切にされているか

   SELECT * FROM users WHERE email REGEXP '\.com$';

2. REGEXP_LIKE() で i フラグを試す

   SELECT * FROM users WHERE REGEXP_LIKE(username, 'admin', 'i');

3. データのエンコーディングを確認

   SHOW VARIABLES LIKE 'character_set_database';

6.5 REGEXP関数のパフォーマンスを向上させる方法は？

1. LIKE を併用して事前フィルタリング

   SELECT * FROM users 
   WHERE email LIKE '%gmail.com' 
   AND email REGEXP '^[a-zA-Z0-9._%+-]+@gmail\.com$';

2. インデックスを活用する

   ALTER TABLE users ADD FULLTEXT(email);

まとめ

• REGEXP は LIKEより強力だが、パフォーマンスに注意 する必要がある。
• MySQL 8.0 では 新しい正規表現関数（REGEXP_LIKE() など）が追加され、より柔軟な処理が可能。
• パフォーマンス向上には、LIKEとの組み合わせ、インデックスの活用、シンプルな正規表現の設計が重要。

7. まとめ

MySQLの正規表現（REGEXP）は、データの検索、バリデーション、フォーマット変換などに非常に便利なツールです。本記事では、MySQLにおける正規表現の基本的な使い方から、MySQL 8.0以降で追加された新機能、実践的な応用例、注意点、FAQまでを詳しく解説しました。

7.1 記事の要点の振り返り

本記事の重要なポイントを簡単に振り返ります。

1. MySQLの正規表現の基本

• REGEXP 演算子 を使用すると、通常の LIKE よりも 柔軟なパターンマッチング が可能。
• 主な正規表現パターン
• ^（先頭）、$（末尾）、.（任意の1文字）、[]（文字クラス）、+（1回以上の繰り返し）など。

2. MySQL 8.0以降の新しい正規表現関数

MySQL 8.0では、以下の4つの関数が追加され、データの処理がより柔軟に：

• REGEXP_LIKE()：REGEXP 演算子の代替
• REGEXP_INSTR()：一致する文字列の開始位置を取得
• REGEXP_SUBSTR()：一致する文字列を抽出
• REGEXP_REPLACE()：正規表現を使用した文字列の置換

3. 実践的な応用例

• メールアドレスのバリデーション
• 電話番号やクレジットカード番号のチェック
• HTMLタグの削除
• 郵便番号フォーマットの検証
• 禁止ワードの検出
• データのクレンジング（空白削除、カンマの除去 など）

4. 正規表現使用時の注意点

• パフォーマンスの最適化
• REGEXP はインデックスが効かず、フルテーブルスキャン になるため注意。
• 代わりに LIKE や FULLTEXT インデックスを併用することで改善可能。
• セキュリティ対策
• ReDoS（正規表現DoS攻撃） を防ぐために、.* の多用を避ける。
• SQLインジェクション を防ぐために プレースホルダ を使用する。

5. FAQ（よくある質問）

• MySQLの正規表現で使用できるパターン
• LIKE と REGEXP の違い
• MySQL 5.xと8.xの機能の違い
• 正規表現がうまく動作しない場合のトラブルシューティング
• パフォーマンスを向上させる方法

7.2 正規表現を活用する際のベストプラクティス

1. 正規表現をシンプルにする

• 例：.* の多用を避け、^（先頭）や $（末尾）を活用する。

1. LIKE や FULLTEXT と併用する

• LIKE で事前フィルタリングし、対象を絞ってから REGEXP を適用することで、処理負荷を軽減。

1. REGEXP_REPLACE() を活用してデータクレンジング

• 例：HTMLタグを削除、不要なスペースを整理。

1. MySQL 8.0以降を活用する

• REGEXP_LIKE() などの新関数を使うと、より可読性の高いSQLを記述できる。

1. セキュリティ対策を徹底する

• ユーザー入力を直接 REGEXP に適用しない（SQLインジェクション対策）。
• 動的なクエリには プレースホルダ を使用する。

7.3 さらなる学習のためのリソース

MySQLの正規表現をさらに深く学ぶための参考資料を紹介します。

公式ドキュメント

• MySQL 8.0 正規表現リファレンス
• MySQL 5.x 正規表現の基本

7.4 最後に

MySQLの正規表現は、データ検索やクリーニング、バリデーションなど多くの場面で活用できます。ただし、 パフォーマンスやセキュリティの注意点を理解した上で適切に使用することが重要 です。

本記事が、MySQLの正規表現を活用する際の参考になれば幸いです。 ぜひ実際のプロジェクトで活用してみてください！