MySQLにおける高度なフィルタリングと正規表現クエリ
基本的なフィルタリングでは不十分な場合、高度なフィルタリング機能を使えば、特定のデータをピンポイントで絞り込むことができます。
このレッスンでは、複雑な条件の組み合わせや正規表現について詳しく解説します。
graph TB
A[WHERE Combinations of Conditions] --> B[AND Logic and]
A --> C[OR Logical OR]
A --> D[NOT Negate]
B --> E[All conditions are met simultaneously]
C --> F[Meets any one of the conditions]
A --> G[IN List Matching]
A --> H[BETWEEN Scope]
A --> I[LIKE Wildcard]
A --> J[REGEXP Regular]
A --> K[EXISTS Subquery]
1. 学習内容
- AND／OR条件の組み合わせ
- 該当／非該当リストの照会
- BETWEEN 範囲クエリ
- LIKE ワイルドカード一致
- REGEXP 正規表現
2. 検索機能にまつわる実話
(1) 課題：検索結果の精度が低い
あるユーザーが「john」を検索し、以下の検索結果を求めています：
- ジョン・スミス
- ジョンソン
- john@example.com ああ、ジョン
単純な LIKE '%john%' は、1つのシナリオにしか一致しません。
(2) REGEXP による解決策
SQL
SELECT * FROM users
WHERE first_name REGEXP '^john|john$|john'
OR email REGEXP 'john';
3. AND/OR の詳細解説
▶ 例：条件の複雑な組み合わせ
SQL
-- Search: Engineering or Sales department, and salary > 5000
SELECT * FROM employees
WHERE (department = 'Engineering' OR department = 'Sales')
AND salary > 5000;
-- Search: Salary 5000-8000, not HR department
SELECT * FROM employees
WHERE salary BETWEEN 5000 AND 8000
AND department != 'HR';
-- Search: Hired after 2024, or salary > 10000
SELECT * FROM employees
WHERE hire_date >= '2024-01-01' OR salary > 10000;
4. IN／NOT IN
▶ 例：リストクエリ
SQL
-- IN Search
SELECT * FROM products WHERE category_id IN (1, 3, 5, 7);
-- NOT IN Search
SELECT * FROM products WHERE category_id NOT IN (2, 4, 6);
-- Subquery IN
SELECT * FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE location = 'Beijing');
-- NOT EXISTS replacement for NOT IN (Better performance)
SELECT * FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
5. 高度なBETWEEN
▶ 例：範囲クエリ
SQL
-- Value Range
SELECT * FROM products WHERE price BETWEEN 100 AND 500;
-- Date Range
SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-06-30';
-- String Range
SELECT * FROM employees WHERE last_name BETWEEN 'A' AND 'M';
-- NOT BETWEEN
SELECT * FROM products WHERE price NOT BETWEEN 100 AND 500;
6. LIKE ワイルドカード
▶ 例：ファジーマッチング
SQL
-- % Match any number of characters (including 0)
SELECT * FROM users WHERE name LIKE 'J%'; -- Starts with J
SELECT * FROM users WHERE name LIKE '%son'; -- Ends with son
SELECT * FROM users WHERE name LIKE '%john%'; -- Contains john
-- _ Matches exactly one character
SELECT * FROM users WHERE name LIKE 'J_hn'; -- J_hn (John, Johan)
-- Used in combination
SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- ESCAPE escaping special characters
SELECT * FROM files WHERE name LIKE '%\_%' ESCAPE '\\'; -- Contains _
SELECT * FROM files WHERE name LIKE '%%' ESCAPE '\\'; -- Contains %
7. REGEXP 正規表現
(1) 一般的な正規表現のメタ文字
| メタ文字 | 説明 | 例 |
|---|---|---|
^ |
冒頭 | '^John' — ジョンから始まる |
$ |
終わり | 'son$' — 「息子」で終わる |
. |
任意の1文字 | 'J.hn' — ジョン、ヨハン |
[...] |
文字セット | '[abc]' — a/b/c のいずれか |
[^...] |
文字セットを除外 | [^abc] — a/b/c 以外 |
* |
0回以上 | 'ab*' — a, ab, abb |
+ |
1回以上 | 'ab+' — a, ab |
? |
0回または1回 | 'ab?' — a, ab |
{n} |
ちょうど n 回 | 'a{3}' — aaa |
{n,m} |
n回からm回 | 'a{2,4}' — aa~aaaa |
| |
または | 'cat|dog' — 猫か犬 |
▶ 例：REGEXP クエリ
SQL
-- Starts with J or j
SELECT * FROM users WHERE first_name REGEXP '^[Jj]';
-- Contains numbers
SELECT * FROM users WHERE username REGEXP '[0-9]';
-- Email format validation (Simple)
SELECT * FROM users WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
-- Mobile phone number format (Mainland China)
SELECT * FROM users WHERE phone REGEXP '^1[3-9][0-9]{9}$';
-- Contains CJK characters
SELECT * FROM users WHERE name REGEXP '[\\u4e00-\\u9fff]';
▶ 例：REGEXP 対 LIKE
SQL
-- LIKE matches must be at the beginning and end only
SELECT * FROM users WHERE name LIKE '%john%'; -- Contains john
-- REGEXP allows for precise control
SELECT * FROM users WHERE name REGEXP '^john$'; -- Exactly equal to john (Case-insensitive)
SELECT * FROM users WHERE name REGEXP 'john|jane'; -- john or jane
SELECT * FROM users WHERE name REGEXP '^[A-Z]'; -- Starts with a capital letter
8. EXISTS サブクエリ
▶ 例：EXISTS の使用
SQL
-- Query Customers with Orders
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- Query Customers with No Orders
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
❓ よくある質問
Q
LIKE '%keyword%' を含むクエリではインデックスを使用できますか？A プレフィックスに
% が含まれている場合、インデックスは使用できず、フルテーブルスキャンが実行されます。大規模なデータセットの場合は、フルテキストインデックス (FULLTEXT) の使用を検討してください。Q REGEXP と LIKE、どちらが高速ですか？
A 通常は LIKE の方が高速です（インデックスを利用できるため）。REGEXP は柔軟性が高いですが、インデックスを利用できません（MySQL 8.0 では一部サポートされています）。
Q IN と OR、どちらのパフォーマンスが優れていますか？
A データ量が少ない場合は、両者のパフォーマンスはほぼ同じです。IN のリストが長い場合、MySQL はそれをソート済みバイナリ検索に最適化するため、OR よりも高速になります。
Q NOT IN には NULL トラップはありますか？
A はい。
NOT IN (1, 2, NULL) は、NULL を含む比較では結果が NULL になるため、常に NULL を返します。代わりに NOT EXISTS を使用してください。📖 まとめ
- AND/OR の組み合わせ条件；演算の順序を指定するには括弧を使用してください
- IN/NOT IN リストのクエリ：NULL による落とし穴に注意
- BETWEEN 範囲クエリ（両端を含む）
- LIKE ワイルドカード：
%は任意の文字数、_は1文字 - REGEXP 正規表現。複雑なパターンマッチングに対応しています。
- EXISTS サブクエリの存在チェック。一般的に、IN よりもパフォーマンスが優れている
📝 練習問題
-
基本問題（難易度：⭐）：LIKEクエリを使用して、メールアドレスに「@gmail.com」が含まれ、かつユーザー名が「J」で始まるユーザーを検索してください。
-
上級問題（難易度 ⭐⭐）：REGEXP を使用して、中国本土の携帯電話番号（先頭が 1 で、2 桁目が 3～9、合計 11 桁のもの）に一致する正規表現を作成してください。
-
チャレンジ問題（難易度：⭐⭐⭐）：NULL値が存在する場合、
NOT INとNOT EXISTSのクエリ結果の違いを比較してください。
