MySQL_8.4のREGEXP_LIKE関数を使ったデータ検索

2026年7月17日

はじめに

MySQLでは文字列検索を行う際に、LIKE句を利用することがよくあります。
例えば、名前に「SQL」を含むデータを検索する場合は以下のように記述できます。

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

1 2	SELECT * FROM users WHERE name LIKE '%SQL%';

LIKE句はシンプルで使いやすい反面…
・電話番号の形式を検証したい
・メールアドレスの形式を確認したい
・複数キーワードのいずれかにマッチするか調べたい
・特定の桁数や文字種で構成されているか確認したい

…といったケースでは表現力に限界があります。

このような場合に活躍するのが REGEXP_LIKE関数です。
MySQL 8.4では、ICU（International Components for Unicode）ライブラリを利用した高機能な正規表現エンジンが利用でき、柔軟な文字列検索が可能になっています。

参照：MySQL 8.4 Reference Manual – 14.8.2 Regular Expressions
MySQL implements regular expression support using International Components for Unicode (ICU), which provides full Unicode support and is multibyte safe.

今回はREGEXP_LIKE関数の基本的な使い方から、実践例まで紹介していきます。

REGEXP_LIKE関数の基本構文

REGEXP_LIKE関数の構文を下記に記載します。構文自体はとてもシンプルなので、関数として利用しやすい印象を受けます！

REGEXP_LIKE(expression, pattern [, match_type])

1 2	REGEXP_LIKE(expression, pattern [, match_type])

・ expression: 検索対象の文字列
・ pattern: 正規表現パターン
・ match_type: オプション（省略可）

match_typeオプションについて

オプション	説明
`i`	大文字小文字を区別しない
`c`	大文字小文字を区別する
`m`	複数行モード
`n`	`.` が改行文字にもマッチするようになる

複数のオプションを同時に指定することも可能です。例えば 'im' を指定すると、大文字小文字を区別せず、かつ複数行モードで照合します。

参照：MySQL 8.4 Reference Manual – 14.8.2 Regular Expressions
“If characters specifying contradictory options are specified within match_type, the rightmost one takes precedence.”

データ準備

今回は「スーパーヒーロー人材派遣会社」のデータを使用します！ AI時代の到来と共にデータベースの重要性が増した世界。データを操る能力を持つスーパーヒーローたちが、人類のデジタル社会を守るため立ち上がりました！！

…という構想です。

スーパーヒーローたちにも得意、不得意分野が少なからずあると思い、そういった情報も登録していますが…
果たしてREGEXP_LIKE関数とのコラボはあるのか… 本ブログを最後までご覧ください！

-- スーパーヒーロー人材派遣会社のデータベース
Query OK, 0 rows affected (0.01 sec)

CREATE TABLE superhero_employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hero_name VARCHAR(100),
    real_name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20),
    power_description TEXT,
    weakness VARCHAR(200),
    city VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE,
    status ENUM('Active', 'On Mission', 'Vacation', 'Injured', 'Retired')
);
Query OK, 0 rows affected (1.01 sec)

-- テストデータを投入
INSERT INTO superhero_employees (hero_name, real_name, email, phone, power_description, weakness, city, salary, hire_date, status) VALUES
('スーパーSQL太郎', '検索太郎', 'supersql.herotech@example.com', '990-1234-5678', '複雑なJOINクエリを1秒で実行。SELECT文の詠唱により敵を混乱 させる。', 'NoSQLデータベースが苦手', 'Tokyo', 850000.00, '2020-01-15', 'Active'),
('正規表現スナイパー', '精密表規', 'regex.master.herotech@example.com', '580-9876-5432', '任意の文字列パターンを瞬時に検索。パターンマッチングで犯人を特定する。', '不正な正規表現でパフォーマンス低下', 'Osaka', 920000.00, '2019-07-22', 'On Mission'),
('バックアップ戦士', '保管鉄夫', 'backup.warrior.herotech@example.com', '770-1111-2222', '失われたデータを完全復旧。時間を巻き戻してデータロストを防ぐ。', '物理メディアの破損に弱い', 'Kyoto', 780000.00, '2021-03-10', 'Active'),
('インデックスマイスター', '索引巧', 'index.ninja.herotech@example.com', '390-ABCD-EFGH', '高速検索で瞬間移動。B-Treeを操りクエリ性能を最適化。', '全件検索(FULL TABLE SCAN)で力を失う', 'Fukuoka', 950000.00, '2018-11-30', 'Vacation'),
('トランザクション王', '一括健介', 'transaction.king.herotech@example.com', '703-5555-7777', 'ACID特性を操りデータの整合性を守る。ロールバック技で時を戻す。', 'デッドロック発生時に動けなくなる', 'Yokohama', 1200000.00, '2017-05-18', 'Active'),
('プリペアドステートメント姫', '定型文姫子', 'prepared.princess.herotech@example.com', '06-8888-9999', 'SQLインジェクション攻撃を完全防御。パラメータ化クエリの結界を張る。', '動的SQLに対応できない', 'Kobe', 880000.00, '2020-09-12', 'Injured'),
('ビッグデータ魔王', '大容量悪阿', 'bigdata.demon.darktech.evil@example.com', '666-HELL-FIRE', 'ペタバイト級のデータを操る。メモリ不足で街を破壊。', '小さなデータセットでは力を発揮できない', 'Underground', 9999999.99, '2015-10-31', 'Retired'),
('レプリケーション忍者', '複製影丸', 'replication.shadow.herotech@example.com', NULL, 'データを瞬時に複製。分散処理で同時に複数の場所に現れる。', 'マスター・スレーブ間のラグで混乱', 'Sendai', 820000.00, '2019-12-25', 'Active');
Query OK, 8 rows affected (0.09 sec)
Records: 8  Duplicates: 0  Warnings: 0

-- テストデータ表示
select * from superhero_employees;
+----+-----------------------------------------+-----------------+-----------------------------------------+---------------+------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------+-------------+------------+------------+------------+
| id | hero_name                               | real_name       | email                                   | phone         | power_description                                                                                          | weakness                                                  | city        | salary     | hire_date  | status     |
+----+-----------------------------------------+-----------------+-----------------------------------------+---------------+------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------+-------------+------------+------------+------------+
|  1 | スーパーSQL太郎                         | 検索太郎        | supersql.herotech@example.com           | 990-1234-5678 | 複雑なJOINクエリを1秒で実行。SELECT文の詠唱により敵を混乱させる。                                         | NoSQLデータベースが苦手                                   | Tokyo       |  850000.00 | 2020-01-15 | Active     |
|  2 | 正規表現スナイパー                      | 精密表規        | regex.master.herotech@example.com       | 580-9876-5432 | 任意の文字列パターンを瞬時に検索。パターンマッチン グで犯人を特定する。                                    | 不正な正規表現でパフォーマンス低下                        | Osaka       |  920000.00 | 2019-07-22 | On Mission |
|  3 | バックアップ戦士                        | 保管鉄夫        | backup.warrior.herotech@example.com     | 770-1111-2222 | 失われたデータを完全復旧。時間を巻き戻してデータロストを防ぐ。                                            | 物理メディアの破損に弱い                                  | Kyoto       |  780000.00 | 2021-03-10 | Active     |
|  4 | インデックスマイスター                  | 索引巧          | index.ninja.herotech@example.com        | 390-ABCD-EFGH | 高速検索で瞬間移動。B-Treeを操りクエリ性能を最適化。                                                       | 全件検索(FULL TABLE SCAN)で力を失う                       | Fukuoka     |  950000.00 | 2018-11-30 | Vacation   |
|  5 | トランザクション王                      | 一括健介        | transaction.king.herotech@example.com   | 703-5555-7777 | ACID特性を操りデータの整合性を守る。ロールバック技で時を戻す。                                            | デッドロック発生時に動けなくなる                          | Yokohama    | 1200000.00 | 2017-05-18 | Active     |
|  6 | プリペアドステートメント姫              | 定型文姫子      | prepared.princess.herotech@example.com  | 06-8888-9999  | SQLインジェクション攻撃を完全防御。パラメータ化クエリの結界を張る。                                        | 動的SQLに対応できない                                     | Kobe        |  880000.00 | 2020-09-12 | Injured    |
|  7 | ビッグデータ魔王                        | 大容量悪阿      | bigdata.demon.darktech.evil@example.com | 666-HELL-FIRE | ペタバイト級のデータを操る。メモリ不足で街を破壊。                                                        | 小さなデータセットでは力を発揮できない                    | Underground | 9999999.99 | 2015-10-31 | Retired    |
|  8 | レプリケーション忍者                    | 複製影丸        | replication.shadow.herotech@example.com | NULL          | データを瞬時に複製。分散処理で同時に複数の場所に現れる。                                                  | マスター・スレーブ間のラグで混乱                          | Sendai      |  820000.00 | 2019-12-25 | Active     |
+----+-----------------------------------------+-----------------+-----------------------------------------+---------------+------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------+-------------+------------+------------+------------+
8 rows in set (0.01 sec)

-- スーパーヒーロー人材派遣会社のデータベース

Query OK, 0 rows affected (0.01 sec)

CREATE TABLE superhero_employees (

id INT AUTO_INCREMENT PRIMARY KEY,

hero_name VARCHAR(100),

real_name VARCHAR(100),

email VARCHAR(100),

phone VARCHAR(20),

power_description TEXT,

weakness VARCHAR(200),

city VARCHAR(50),

salary DECIMAL(10, 2),

hire_date DATE,

status ENUM('Active', 'On Mission', 'Vacation', 'Injured', 'Retired')

);

Query OK, 0 rows affected (1.01 sec)

-- テストデータを投入

INSERT INTO superhero_employees (hero_name, real_name, email, phone, power_description, weakness, city, salary, hire_date, status) VALUES

('スーパーSQL太郎', '検索太郎', 'supersql.herotech@example.com', '990-1234-5678', '複雑なJOINクエリを1秒で実行。SELECT文の詠唱により敵を混乱させる。', 'NoSQLデータベースが苦手', 'Tokyo', 850000.00, '2020-01-15', 'Active'),

('正規表現スナイパー', '精密表規', 'regex.master.herotech@example.com', '580-9876-5432', '任意の文字列パターンを瞬時に検索。パターンマッチングで犯人を特定する。', '不正な正規表現でパフォーマンス低下', 'Osaka', 920000.00, '2019-07-22', 'On Mission'),

('バックアップ戦士', '保管鉄夫', 'backup.warrior.herotech@example.com', '770-1111-2222', '失われたデータを完全復旧。時間を巻き戻してデータロストを防ぐ。', '物理メディアの破損に弱い', 'Kyoto', 780000.00, '2021-03-10', 'Active'),

('インデックスマイスター', '索引巧', 'index.ninja.herotech@example.com', '390-ABCD-EFGH', '高速検索で瞬間移動。B-Treeを操りクエリ性能を最適化。', '全件検索(FULL TABLE SCAN)で力を失う', 'Fukuoka', 950000.00, '2018-11-30', 'Vacation'),

('トランザクション王', '一括健介', 'transaction.king.herotech@example.com', '703-5555-7777', 'ACID特性を操りデータの整合性を守る。ロールバック技で時を戻す。', 'デッドロック発生時に動けなくなる', 'Yokohama', 1200000.00, '2017-05-18', 'Active'),

('プリペアドステートメント姫', '定型文姫子', 'prepared.princess.herotech@example.com', '06-8888-9999', 'SQLインジェクション攻撃を完全防御。パラメータ化クエリの結界を張る。', '動的SQLに対応できない', 'Kobe', 880000.00, '2020-09-12', 'Injured'),

('ビッグデータ魔王', '大容量悪阿', 'bigdata.demon.darktech.evil@example.com', '666-HELL-FIRE', 'ペタバイト級のデータを操る。メモリ不足で街を破壊。', '小さなデータセットでは力を発揮できない', 'Underground', 9999999.99, '2015-10-31', 'Retired'),

('レプリケーション忍者', '複製影丸', 'replication.shadow.herotech@example.com', NULL, 'データを瞬時に複製。分散処理で同時に複数の場所に現れる。', 'マスター・スレーブ間のラグで混乱', 'Sendai', 820000.00, '2019-12-25', 'Active');

Query OK, 8 rows affected (0.09 sec)

Records: 8 Duplicates: 0 Warnings: 0

-- テストデータ表示

select * from superhero_employees;

+----+-----------------------------------------+-----------------+-----------------------------------------+---------------+------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------+-------------+------------+------------+------------+

8 rows in set (0.01 sec)

REGEXP_LIKE関数の使い方

ケース1:基本的なパターンマッチング

まずは基本的な使い方から見てみましょう。
ヒーロー名に「SQL」を含むデータを検索してみます。

# 「SQL」を含むヒーロー名を検索
SELECT hero_name, real_name, power_description
FROM superhero_employees
WHERE REGEXP_LIKE(hero_name, 'SQL');
+-----------------------+--------------+----------------------------------------------------------------------------------------------+
| hero_name             | real_name    | power_description                                                                            |
+-----------------------+--------------+----------------------------------------------------------------------------------------------+
| スーパーSQL太郎       | 検索太郎     | 複雑なJOINクエリを1秒で実行。SELECT文の詠唱により敵を混乱させる。                            |
+-----------------------+--------------+----------------------------------------------------------------------------------------------+
1 row in set (0.21 sec)

# 「SQL」を含むヒーロー名を検索

SELECT hero_name, real_name, power_description

FROM superhero_employees

WHERE REGEXP_LIKE(hero_name, 'SQL');

+-----------------------+--------------+----------------------------------------------------------------------------------------------+

| hero_name | real_name | power_description |

+-----------------------+--------------+----------------------------------------------------------------------------------------------+

| スーパーSQL太郎 | 検索太郎 | 複雑なJOINクエリを1秒で実行。SELECT文の詠唱により敵を混乱させる。 |

+-----------------------+--------------+----------------------------------------------------------------------------------------------+

1 row in set (0.21 sec)

次に'i'フラグを指定し、大文字小文字を区別せずに検索します。

# 大文字小文字を区別せずに「a」を検索
SELECT hero_name, real_name, status
FROM superhero_employees
WHERE REGEXP_LIKE(status, 'a', 'i');
+-----------------------------------+--------------+----------+
| hero_name                         | real_name    | status   |
+-----------------------------------+--------------+----------+
| スーパーSQL太郎                   | 検索太郎     | Active   |
| バックアップ戦士                  | 保管鉄夫     | Active   |
| インデックスマイスター            | 索引巧       | Vacation |
| トランザクション王                | 一括健介     | Active   |
| レプリケーション忍者              | 複製影丸     | Active   |
+-----------------------------------+--------------+----------+
5 rows in set (0.01 sec)

# 大文字小文字を区別せずに「a」を検索

SELECT hero_name, real_name, status

FROM superhero_employees

WHERE REGEXP_LIKE(status, 'a', 'i');

+-----------------------------------+--------------+----------+

| hero_name | real_name | status |

+-----------------------------------+--------------+----------+

| スーパーSQL太郎 | 検索太郎 | Active |

| バックアップ戦士 | 保管鉄夫 | Active |

| インデックスマイスター | 索引巧 | Vacation |

| トランザクション王 | 一括健介 | Active |

| レプリケーション忍者 | 複製影丸 | Active |

+-----------------------------------+--------------+----------+

5 rows in set (0.01 sec)

続いて | を使ったOR検索です。「王」または「姫」を含むヒーローを絞り込みます。
以下のようにLIKEで同じ条件を書くと OR を複数つなぐ必要がありますが、REGEXP_LIKE関数ならパターン1つで完結することがわかります。

# 「王」または「姫」を含むヒーローを検索
# パターン1：Like句による検索
SELECT hero_name, real_name, power_description
FROM superhero_employees
WHERE hero_name LIKE '%王%' OR hero_name LIKE '%姫%';
+-----------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------+
| hero_name                               | real_name       | power_description                                                                                   |
+-----------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------+
| トランザクション王                      | 一括健介        | ACID特性を操りデータの整合性を守る。ロールバック技で時を戻す。                                     |
| プリペアドステートメント姫              | 定型文姫子      | SQLインジェクション攻撃を完全防御。パラメータ化クエリの結界を張る。                                 |
| ビッグデータ魔王                        | 大容量悪阿      | ペタバイト級のデータを操る。メモリ不足で街を破壊。                                                 |
+-----------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

# パターン2：REGEXP_LIKE関数による検索
SELECT hero_name, real_name, power_description 
FROM superhero_employees 
WHERE REGEXP_LIKE(hero_name, '王|姫');
+-----------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------+
| hero_name                               | real_name       | power_description                                                                                   |
+-----------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------+
| トランザクション王                      | 一括健介        | ACID特性を操りデータの整合性を守る。ロールバック技で時を戻す。                                      |
| プリペアドステートメント姫              | 定型文姫子      | SQLインジェクション攻撃を完全防御。パラメータ化クエリの結界を張る。                                 |
| ビッグデータ魔王                        | 大容量悪阿      | ペタバイト級のデータを操る。メモリ不足で街を破壊。                                                  |
+-----------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

# 「王」または「姫」を含むヒーローを検索

# パターン1：Like句による検索

SELECT hero_name, real_name, power_description

FROM superhero_employees

WHERE hero_name LIKE '%王%' OR hero_name LIKE '%姫%';

+-----------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------+

| hero_name | real_name | power_description |

+-----------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------+

| トランザクション王 | 一括健介 | ACID特性を操りデータの整合性を守る。ロールバック技で時を戻す。 |

| プリペアドステートメント姫 | 定型文姫子 | SQLインジェクション攻撃を完全防御。パラメータ化クエリの結界を張る。 |

| ビッグデータ魔王 | 大容量悪阿 | ペタバイト級のデータを操る。メモリ不足で街を破壊。 |

+-----------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------+

3 rows in set (0.01 sec)

# パターン2：REGEXP_LIKE関数による検索

SELECT hero_name, real_name, power_description

FROM superhero_employees

WHERE REGEXP_LIKE(hero_name, '王|姫');

+-----------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------+

| hero_name | real_name | power_description |

+-----------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------+

| トランザクション王 | 一括健介 | ACID特性を操りデータの整合性を守る。ロールバック技で時を戻す。 |

| プリペアドステートメント姫 | 定型文姫子 | SQLインジェクション攻撃を完全防御。パラメータ化クエリの結界を張る。 |

| ビッグデータ魔王 | 大容量悪阿 | ペタバイト級のデータを操る。メモリ不足で街を破壊。 |

+-----------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------+

3 rows in set (0.00 sec)

ケース2:電話番号のマッチング検索

次に電話番号の形式を検証してみましょう。
SELECT句にis_valid_phoneを表示しており、一目で確認できるようにしています。

# 正常な電話番号形式（XXX-XXXX-XXXX）をチェック
SELECT hero_name, phone,
       REGEXP_LIKE(phone, '^[0-9]{2,3}-[0-9]{4}-[0-9]{4}$') AS is_valid_phone
FROM superhero_employees;
+-----------------------------------------+---------------+----------------+
| hero_name                               | phone         | is_valid_phone |
+-----------------------------------------+---------------+----------------+
| スーパーSQL太郎                         | 990-1234-5678 |              1 |
| 正規表現スナイパー                      | 580-9876-5432 |              1 |
| バックアップ戦士                        | 770-1111-2222 |              1 |
| インデックスマイスター                  | 390-ABCD-EFGH |              0 |
| トランザクション王                      | 703-5555-7777 |              1 |
| プリペアドステートメント姫              | 06-8888-9999  |              1 |
| ビッグデータ魔王                        | 666-HELL-FIRE |              0 |
| レプリケーション忍者                    | NULL          |           NULL |
+-----------------------------------------+---------------+----------------+
8 rows in set (0.02 sec)

# 正常な電話番号形式（XXX-XXXX-XXXX）をチェック

SELECT hero_name, phone,

REGEXP_LIKE(phone, '^[0-9]{2,3}-[0-9]{4}-[0-9]{4}$') AS is_valid_phone

FROM superhero_employees;

+-----------------------------------------+---------------+----------------+

| hero_name | phone | is_valid_phone |

+-----------------------------------------+---------------+----------------+

| スーパーSQL太郎 | 990-1234-5678 | 1 |

| 正規表現スナイパー | 580-9876-5432 | 1 |

| バックアップ戦士 | 770-1111-2222 | 1 |

| インデックスマイスター | 390-ABCD-EFGH | 0 |

| トランザクション王 | 703-5555-7777 | 1 |

| プリペアドステートメント姫 | 06-8888-9999 | 1 |

| ビッグデータ魔王 | 666-HELL-FIRE | 0 |

| レプリケーション忍者 | NULL | NULL |

+-----------------------------------------+---------------+----------------+

8 rows in set (0.02 sec)

（補足）正規表現パターン解説:
・ ^: 行の開始
・ [0-9]{2,3}: 数字が2-3桁
・ -: ハイフン（リテラル）
・ [0-9]{4}: 数字がちょうど4桁
・ $: 行の終了

ケース３:メールアドレスの検証とドメイン分析

電話番号のチェックだけでなく、メールアドレスの形式チェックや解析も可能です。
CASEと組み合わせることにより、条件分岐が可能となりますので幅広く活用することができます！

# メールアドレスの形式検証とドメイン分析
SELECT hero_name, email,
       REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$') AS is_valid_email,
       CASE 
           WHEN REGEXP_LIKE(email, 'herotech@example\.com$') THEN 'ヒーロー正社員'
           WHEN REGEXP_LIKE(email, 'darktech\.evil@example\.com$') THEN '悪の組織'
           ELSE 'その他'
       END AS organization
FROM superhero_employees;
+-----------------------------------------+-----------------------------------------+----------------+-----------------------+
| hero_name                               | email                                   | is_valid_email | organization          |
+-----------------------------------------+-----------------------------------------+----------------+-----------------------+
| スーパーSQL太郎                         | supersql.herotech@example.com           |              1 | ヒーロー正社員        |
| 正規表現スナイパー                      | regex.master.herotech@example.com       |              1 | ヒーロー正社員        |
| バックアップ戦士                        | backup.warrior.herotech@example.com     |              1 | ヒーロー正社員        |
| インデックスマイスター                  | index.ninja.herotech@example.com        |              1 | ヒーロー正社員        |
| トランザクション王                      | transaction.king.herotech@example.com   |              1 | ヒーロー正社員        |
| プリペアドステートメント姫              | prepared.princess.herotech@example.com  |              1 | ヒーロー正社員        |
| ビッグデータ魔王                        | bigdata.demon.darktech.evil@example.com |              1 | 悪の組織              |
| レプリケーション忍者                    | replication.shadow.herotech@example.com |              1 | ヒーロー正社員        |
+-----------------------------------------+-----------------------------------------+----------------+-----------------------+
8 rows in set (0.00 sec)

# メールアドレスの形式検証とドメイン分析

SELECT hero_name, email,

REGEXP_LIKE(email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$') AS is_valid_email,

CASE

WHEN REGEXP_LIKE(email, 'herotech@example\.com$') THEN 'ヒーロー正社員'

WHEN REGEXP_LIKE(email, 'darktech\.evil@example\.com$') THEN '悪の組織'

ELSE 'その他'

END AS organization

FROM superhero_employees;

+-----------------------------------------+-----------------------------------------+----------------+-----------------------+

+-----------------------------------------+-----------------------------------------+----------------+-----------------------+

+-----------------------------------------+-----------------------------------------+----------------+-----------------------+

8 rows in set (0.00 sec)

（補足）メール検証パターンの詳細解説:
・ ^[a-zA-Z0-9._%+-]+: ローカル部（@より前）
・ @: アットマーク
・ [a-zA-Z0-9.-]+: ドメイン名部分
・ [a-zA-Z]{2,}$: トップレベルドメイン（2文字以上）
・ \.: ドット（エスケープ必須）

⚠️ 重要: 正規表現での「\」は「ドット（.）」をリテラル文字として扱うためのエスケープ文字です。「.」だけの場合は「任意の1文字」という意味になってしまいます！

ケース４:日付パターンの検索

MySQLで特定の年を抽出する場合、YEAR関数やDATE_FORMAT関数を使用するケースもあると思いますが、こういった関数を使用せずに抽出することが可能です。

# 2020年以降に入社したヒーローを検索
SELECT hero_name, hire_date
FROM superhero_employees
WHERE REGEXP_LIKE(hire_date, '^202[0-9]');
+-----------------------------------------+------------+
| hero_name                               | hire_date  |
+-----------------------------------------+------------+
| スーパーSQL太郎                         | 2020-01-15 |
| バックアップ戦士                        | 2021-03-10 |
| プリペアドステートメント姫              | 2020-09-12 |
+-----------------------------------------+------------+
3 rows in set (0.00 sec)

# 2020年以降に入社したヒーローを検索

SELECT hero_name, hire_date

FROM superhero_employees

WHERE REGEXP_LIKE(hire_date, '^202[0-9]');

+-----------------------------------------+------------+

| hero_name | hire_date |

+-----------------------------------------+------------+

| スーパーSQL太郎 | 2020-01-15 |

| バックアップ戦士 | 2021-03-10 |

| プリペアドステートメント姫 | 2020-09-12 |

+-----------------------------------------+------------+

3 rows in set (0.00 sec)

また、以下のように特定の月だけ抽出することもできます！

# 春（3-5月）に入社したヒーロー
SELECT hero_name, hire_date 
FROM superhero_employees 
WHERE REGEXP_LIKE(hire_date, '-0[3-5]-');
+-----------------------------+------------+
| hero_name                   | hire_date  |
+-----------------------------+------------+
| バックアップ戦士            | 2021-03-10 |
| トランザクション王          | 2017-05-18 |
+-----------------------------+------------+
2 rows in set (0.00 sec)

# 年末年始（12月または1月）入社したヒーロー
SELECT hero_name, hire_date 
FROM superhero_employees 
WHERE REGEXP_LIKE(hire_date, '-(12|01)-');
+--------------------------------+------------+
| hero_name                      | hire_date  |
+--------------------------------+------------+
| スーパーSQL太郎                | 2020-01-15 |
| レプリケーション忍者           | 2019-12-25 |
+--------------------------------+------------+
2 rows in set (0.00 sec)

# 春（3-5月）に入社したヒーロー

SELECT hero_name, hire_date

FROM superhero_employees

WHERE REGEXP_LIKE(hire_date, '-0[3-5]-');

+-----------------------------+------------+

| hero_name | hire_date |

+-----------------------------+------------+

| バックアップ戦士 | 2021-03-10 |

| トランザクション王 | 2017-05-18 |

+-----------------------------+------------+

2 rows in set (0.00 sec)

# 年末年始（12月または1月）入社したヒーロー

SELECT hero_name, hire_date

FROM superhero_employees

WHERE REGEXP_LIKE(hire_date, '-(12|01)-');

+--------------------------------+------------+

| hero_name | hire_date |

+--------------------------------+------------+

| スーパーSQL太郎 | 2020-01-15 |

| レプリケーション忍者 | 2019-12-25 |

+--------------------------------+------------+

2 rows in set (0.00 sec)

ケース5:リスク分析

業務によっては、特定のワード、数値でリスク分析を行うこともあると思いますが、
このような場合においても、CASEとREGEXP_LIKE関数が活用できます！

# 危険度の高いヒーローを特定（弱点が致命的なもの）
SELECT hero_name, weakness, salary,
       CASE 
           WHEN REGEXP_LIKE(weakness, '(破壊|攻撃|失う|動けなくなる)', 'i') THEN '高リスク'
           WHEN REGEXP_LIKE(weakness, '(苦手|弱い|できない)', 'i') THEN '中リスク'
           ELSE '低リスク'
       END AS risk_level
FROM superhero_employees
ORDER BY salary DESC;
+-----------------------------------------+-----------------------------------------------------------+------------+--------------+
| hero_name                               | weakness                                                  | salary     | risk_level   |
+-----------------------------------------+-----------------------------------------------------------+------------+--------------+
| ビッグデータ魔王                        | 小さなデータセットでは力を発揮できない                    | 9999999.99 | 中リスク     |
| トランザクション王                      | デッドロック発生時に動けなくなる                          | 1200000.00 | 高リスク     |
| インデックスマイスター                  | 全件検索(FULL TABLE SCAN)で力を失う                       |  950000.00 | 高リスク     |
| 正規表現スナイパー                      | 不正な正規表現でパフォーマンス低下                        |  920000.00 | 低リスク     |
| プリペアドステートメント姫              | 動的SQLに対応できない                                     |  880000.00 | 中リスク     |
| スーパーSQL太郎                         | NoSQLデータベースが苦手                                   |  850000.00 | 中リスク     |
| レプリケーション忍者                    | マスター・スレーブ間のラグで混乱                          |  820000.00 | 低リスク     |
| バックアップ戦士                        | 物理メディアの破損に弱い                                  |  780000.00 | 中リスク     |
+-----------------------------------------+-----------------------------------------------------------+------------+--------------+
8 rows in set (0.00 sec)

# 危険度の高いヒーローを特定（弱点が致命的なもの）

SELECT hero_name, weakness, salary,

CASE

WHEN REGEXP_LIKE(weakness, '(破壊|攻撃|失う|動けなくなる)', 'i') THEN '高リスク'

WHEN REGEXP_LIKE(weakness, '(苦手|弱い|できない)', 'i') THEN '中リスク'

ELSE '低リスク'

END AS risk_level

FROM superhero_employees

ORDER BY salary DESC;

+-----------------------------------------+-----------------------------------------------------------+------------+--------------+

+-----------------------------------------+-----------------------------------------------------------+------------+--------------+

+-----------------------------------------+-----------------------------------------------------------+------------+--------------+

8 rows in set (0.00 sec)

ケース6:アンマッチデータの検索

REGEXP_LIKE関数を利用して、パターンに一致しないデータ（アンマッチデータ）を検索したい場合、NULL値の扱いに注意が必要です。

例えば、電話番号が正しい形式ではないデータを抽出したいとします。
今回は、以下の3件が抽出されることを期待します。

■想定結果
+-----------------------------------------+---------------+----------------+
| hero_name                               | phone         | is_valid_phone |
+-----------------------------------------+---------------+----------------+
| インデックスマイスター                  | 390-ABCD-EFGH |              0 |
| ビッグデータ魔王                        | 666-HELL-FIRE |              0 |
| レプリケーション忍者                    | NULL          |           NULL |
+-----------------------------------------+---------------+----------------

■想定結果

+-----------------------------------------+---------------+----------------+

| hero_name | phone | is_valid_phone |

+-----------------------------------------+---------------+----------------+

| インデックスマイスター | 390-ABCD-EFGH | 0 |

| ビッグデータ魔王 | 666-HELL-FIRE | 0 |

| レプリケーション忍者 | NULL | NULL |

+-----------------------------------------+---------------+----------------

まず失敗例は以下の通りです。
NOT演算子＋REGEXP_LIKE関数を使っていますが、NULL値が抽出されていません。
なぜNULL値は抽出されないのでしょうか？

SELECT hero_name, phone
FROM superhero_employees
WHERE NOT REGEXP_LIKE(phone, '^[0-9]{2,3}-[0-9]{4}-[0-9]{4}$');
+-----------------------------------+---------------+
| hero_name                         | phone         |
+-----------------------------------+---------------+
| インデックスマイスター            | 390-ABCD-EFGH |
| ビッグデータ魔王                  | 666-HELL-FIRE |
+-----------------------------------+---------------+
2 rows in set (0.01 sec)

SELECT hero_name, phone

FROM superhero_employees

WHERE NOT REGEXP_LIKE(phone, '^[0-9]{2,3}-[0-9]{4}-[0-9]{4}$');

+-----------------------------------+---------------+

| hero_name | phone |

+-----------------------------------+---------------+

| インデックスマイスター | 390-ABCD-EFGH |

| ビッグデータ魔王 | 666-HELL-FIRE |

+-----------------------------------+---------------+

2 rows in set (0.01 sec)

REGEXP_LIKE関数は、検索対象またはパターンがNULL値の場合、戻り値としてNULL値を返します。MySQLリファレンスマニュアルにも次のように記載されています。

参照：MySQL 8.4 Reference Manual – 14.8.2 Regular Expressions

“Returns 1 if the string expr matches the regular expression specified by the pattern pat, 0 otherwise. If expr or pat is NULL, the return value is NULL.”

つまり、電話番号がNULLの場合、
REGEXP_LIKE(NULL, '^[0-9]{2,3}-[0-9]{4}-[0-9]{4}$')
の結果(戻り値)は NULL値となります。

さらに、NOT演算子でNULL値の結果を判定すると NOT NULLはNULL値になります。
そのため、

REGEXP_LIKEの結果	NOT演算後	WHERE句で抽出されるか
1	0	×
0	1	○
NULL	NULL	×

…となり、NULL値は抽出されません。

これはSQLが採用している 三値論理（TRUE / FALSE / UNKNOWN）による動作です。
なおNULL値も不正データとして扱いたい場合は、 IS NULLや IS UNKNOWNを組み合わせることで抽出可能です。

SELECT hero_name, phone
FROM superhero_employees
WHERE phone IS NULL
   OR NOT REGEXP_LIKE(phone, '^[0-9]{2,3}-[0-9]{4}-[0-9]{4}$');
+-----------------------------------+---------------+
| hero_name                         | phone         |
+-----------------------------------+---------------+
| インデックスマイスター            | 390-ABCD-EFGH |
| ビッグデータ魔王                  | 666-HELL-FIRE |
| レプリケーション忍者              | NULL          |
+-----------------------------------+---------------+
3 rows in set (0.04 sec)

SELECT hero_name, phone
FROM superhero_employees
WHERE phone IS UNKNOWN
   OR NOT REGEXP_LIKE(phone, '^[0-9]{2,3}-[0-9]{4}-[0-9]{4}$');
+-----------------------------------+---------------+
| hero_name                         | phone         |
+-----------------------------------+---------------+
| インデックスマイスター            | 390-ABCD-EFGH |
| ビッグデータ魔王                  | 666-HELL-FIRE |
| レプリケーション忍者              | NULL          |
+-----------------------------------+---------------+
3 rows in set (0.01 sec)

SELECT hero_name, phone

FROM superhero_employees

WHERE phone IS NULL

OR NOT REGEXP_LIKE(phone, '^[0-9]{2,3}-[0-9]{4}-[0-9]{4}$');

+-----------------------------------+---------------+

| hero_name | phone |

+-----------------------------------+---------------+

| インデックスマイスター | 390-ABCD-EFGH |

| ビッグデータ魔王 | 666-HELL-FIRE |

| レプリケーション忍者 | NULL |

+-----------------------------------+---------------+

3 rows in set (0.04 sec)

SELECT hero_name, phone

FROM superhero_employees

WHERE phone IS UNKNOWN

OR NOT REGEXP_LIKE(phone, '^[0-9]{2,3}-[0-9]{4}-[0-9]{4}$');

+-----------------------------------+---------------+

| hero_name | phone |

+-----------------------------------+---------------+

| インデックスマイスター | 390-ABCD-EFGH |

| ビッグデータ魔王 | 666-HELL-FIRE |

| レプリケーション忍者 | NULL |

+-----------------------------------+---------------+

3 rows in set (0.01 sec)

またCOALESCE関数を上手く使うことで、
以下のようにマッチ/アンマッチデータにNULL値を含めることができます！！

SELECT hero_name, phone
FROM superhero_employees
WHERE NOT REGEXP_LIKE(COALESCE(phone,FALSE), '^[0-9]{2,3}-[0-9]{4}-[0-9]{4}$');
+-----------------------------------+---------------+
| hero_name                         | phone         |
+-----------------------------------+---------------+
| インデックスマイスター            | 390-ABCD-EFGH |
| ビッグデータ魔王                  | 666-HELL-FIRE |
| レプリケーション忍者              | NULL          |
+-----------------------------------+---------------+
3 rows in set (0.01 sec)

SELECT hero_name, phone
FROM superhero_employees
WHERE COALESCE(REGEXP_LIKE(phone, '^[0-9]{2,3}-[0-9]{4}-[0-9]{4}$'),TRUE);
+-----------------------------------------+---------------+
| hero_name                               | phone         |
+-----------------------------------------+---------------+
| スーパーSQL太郎                         | 990-1234-5678 |
| 正規表現スナイパー                      | 580-9876-5432 |
| バックアップ戦士                        | 770-1111-2222 |
| トランザクション王                      | 703-5555-7777 |
| プリペアドステートメント姫              | 06-8888-9999  |
| レプリケーション忍者                    | NULL          |
+-----------------------------------------+---------------+
6 rows in set (0.00 sec)

SELECT hero_name, phone

FROM superhero_employees

WHERE NOT REGEXP_LIKE(COALESCE(phone,FALSE), '^[0-9]{2,3}-[0-9]{4}-[0-9]{4}$');

+-----------------------------------+---------------+

| hero_name | phone |

+-----------------------------------+---------------+

| インデックスマイスター | 390-ABCD-EFGH |

| ビッグデータ魔王 | 666-HELL-FIRE |

| レプリケーション忍者 | NULL |

+-----------------------------------+---------------+

3 rows in set (0.01 sec)

SELECT hero_name, phone

FROM superhero_employees

WHERE COALESCE(REGEXP_LIKE(phone, '^[0-9]{2,3}-[0-9]{4}-[0-9]{4}$'),TRUE);

+-----------------------------------------+---------------+

| hero_name | phone |

+-----------------------------------------+---------------+

| スーパーSQL太郎 | 990-1234-5678 |

| 正規表現スナイパー | 580-9876-5432 |

| バックアップ戦士 | 770-1111-2222 |

| トランザクション王 | 703-5555-7777 |

| プリペアドステートメント姫 | 06-8888-9999 |

| レプリケーション忍者 | NULL |

+-----------------------------------------+---------------+

6 rows in set (0.00 sec)

データ品質チェックでは、フォーマットエラーだけでなくNULL値も異常データとして扱うケースが多いため、この書き方を覚えておくと実務でも役立ちます。

なおNULL値の扱い方は、「MySQLのNULL値の扱い方について」というブログでも紹介していますので、是非ご参考にしてください。

まとめ

今回はMySQL 8.4で利用できるREGEXP_LIKE関数を紹介しました。

REGEXP_LIKE関数を利用することで、
・電話番号形式のチェック
・メールアドレスの検証
・日付けパターンの検索
・リスク分析
・アンマッチデータの検索
など、LIKE句だけでは実現が難しい検索処理を簡潔に記述できます。

一方で、インデックスが利用されないケースもあるため、大量データに対して利用する場合はパフォーマンスにも注意が必要です。
正規表現を活用することで、SQLによるデータ検索やデータ品質管理の幅が大きく広がります。ぜひ業務でも活用してみてください。

よかったらシェアしてね！

URLをコピーしました！

ClickHouseとMySQLの連携方法を徹底比較！関数・Dictionary・エンジン・インターフェイス

MySQL_8.4のREGEXP_LIKE関数を使ったデータ検索

はじめに

REGEXP_LIKE関数の基本構文

match_typeオプションについて

データ準備

REGEXP_LIKE関数の使い方

ケース1:基本的なパターンマッチング

ケース2:電話番号のマッチング検索

ケース３:メールアドレスの検証とドメイン分析

ケース４:日付パターンの検索

ケース5:リスク分析

ケース6:アンマッチデータの検索

まとめ

スマートスタイルTECHブログについて

関連記事