【SQL】LIKE演算子完全ガイド｜ワイルドカード・エスケープ・大文字小文字・NOT LIKE・パフォーマンス・全文検索まで解説

-- customers テーブル（顧客）
-- id | name           | email                     | phone        | memo
--  1 | 田中 太郎      | tanaka@example.com        | 090-1234-5678| VIP顧客
--  2 | 田中 花子      | hanako@gmail.com          | 080-2345-6789| NULL
--  3 | 鈴木 一郎      | suzuki@example.co.jp      | 070-3456-7890| 特別割引 10%
--  4 | 高橋_美咲      | takahashi@company.com     | 090-4567-8901| NULL
--  5 | Tanaka Taro    | tanaka.t@example.com      | NULL         | 海外在住
--  6 | SUZUKI ICHIRO  | s.ichiro@EXAMPLE.COM      | 090-5678-9012| NULL

-- 基本形
SELECT 列名 FROM テーブル名 WHERE 列名 LIKE 'パターン';

-- 前方一致: 「田」で始まる名前
SELECT * FROM customers WHERE name LIKE '田%';

-- 後方一致: 「郎」で終わる名前
SELECT * FROM customers WHERE name LIKE '%郎';

-- 部分一致（中間一致）: 名前に「一」を含む
SELECT * FROM customers WHERE name LIKE '%一%';

-- 完全一致（= と同じ動作、実用上は = を使う）
SELECT * FROM customers WHERE name LIKE '田中 太郎';

-- example.com ドメインのメールアドレスを取得
SELECT id, name, email FROM customers
WHERE email LIKE '%@example%';

-- 結果:
-- id=1: tanaka@example.com ✓
-- id=3: suzuki@example.co.jp ✓
-- id=5: tanaka.t@example.com ✓

-- 電話番号が 090 で始まる顧客
SELECT id, name, phone FROM customers
WHERE phone LIKE '090%';

-- パーセントや割引に関するメモを含む顧客
-- ※ % 自体を検索する場合は後述のエスケープが必要
SELECT id, name, memo FROM customers
WHERE memo LIKE '%割引%';

-- 「田中 ??」（田中 の後に空白と任意の2文字）
SELECT * FROM customers WHERE name LIKE '田中 __';
-- マッチ: 田中 太郎（田中[空白]太郎 → _=太, _=郎）✓
-- 不マッチ: 田中 花子太（4文字なのでマッチしない）

-- 3文字の名前
SELECT * FROM customers WHERE name LIKE '___';

-- 電話番号の形式チェック（090-XXXX-XXXX）
SELECT * FROM customers
WHERE phone LIKE '0__-____-____%';

-- 2文字目が「中」である名前
SELECT * FROM customers WHERE name LIKE '_中%';

-- 「10%」という文字列を検索（% をエスケープ）
-- ESCAPE '!'  → !の直後の % はワイルドカードではなく文字として扱う
SELECT * FROM customers
WHERE memo LIKE '%10!%%' ESCAPE '!';

-- 「商品_A」という文字列を含む検索（_ をエスケープ）
SELECT * FROM products
WHERE product_code LIKE '%商品!_A%' ESCAPE '!';

-- 標準 SQL の ESCAPE 句（すべての RDBMS で動作）
SELECT * FROM customers
WHERE memo LIKE '%割引 !__%' ESCAPE '!';
-- ↑ 「割引 _」で始まるもの（ _ は文字として扱われる）

-- MySQL では \ をエスケープ文字として使うことも可（非推奨）
-- SELECT * FROM ... WHERE memo LIKE '%10\%%';  -- MySQL 固有

-- 方法1: UPPER() / LOWER() で統一する（すべての RDBMS で使える）
SELECT * FROM customers
WHERE UPPER(name) LIKE UPPER('%tanaka%');
-- → 「田中」「TANAKA」「Tanaka」すべてにマッチ

-- 方法2: PostgreSQL の ILIKE（大文字小文字を区別しない LIKE）
-- ILIKE は PostgreSQL 専用。MySQL では使えない
SELECT * FROM customers
WHERE email ILIKE '%@EXAMPLE%';
-- → example, EXAMPLE, Example すべてにマッチ

-- 方法3: MySQL の照合順序を明示（COLLATE）
SELECT * FROM customers
WHERE email LIKE '%@example%'
COLLATE utf8mb4_general_ci;  -- ci = case insensitive

-- サンプルデータでの確認
-- id=6: s.ichiro@EXAMPLE.COM
-- LIKE '%@example%'  → MySQL(ci照合順序)ではマッチ、PostgreSQL ではマッチしない
-- ILIKE '%@example%' → PostgreSQL でマッチ
-- UPPER(email) LIKE '%@EXAMPLE%' → すべての RDBMS でマッチ

-- gmail.com 以外のメールアドレス
SELECT id, name, email FROM customers
WHERE email NOT LIKE '%@gmail.com';

-- 結果: id=1, 3, 4, 5, 6 (id=2 の hanako@gmail.com が除外)

-- NULL の email も除外される点に注意
-- id=5 は NULL ではないのでマッチする (NULL はない)

-- 「VIP」を含まないメモの顧客（NULL も除外される）
SELECT id, name, memo FROM customers
WHERE memo NOT LIKE '%VIP%';
-- id=3（特別割引）のみ → id=2,4,6 の NULL も除外される

-- NULL を含めたい場合
SELECT id, name, memo FROM customers
WHERE memo NOT LIKE '%VIP%' OR memo IS NULL;

-- 「田中」または「鈴木」を含む名前（OR で複数の LIKE を並べる）
SELECT * FROM customers
WHERE name LIKE '%田中%'
   OR name LIKE '%鈴木%';

-- example.com または gmail.com のメール
SELECT * FROM customers
WHERE email LIKE '%@example%'
   OR email LIKE '%@gmail.com';

-- IN と LIKE は直接組み合わせられない（エラーになる）
-- NG: WHERE email IN LIKE ('%@example%', '%@gmail%')  -- 構文エラー

-- 3 つ以上のパターンがある場合: OR を列挙するか、RDBMS 固有の方法を使う
-- MySQL: REGEXP で代替
SELECT * FROM customers
WHERE email REGEXP '@example|@gmail|@company';
-- PostgreSQL: SIMILAR TO または ~ 演算子
-- WHERE email SIMILAR TO '%(example|gmail|company)%'

-- 正規表現の詳細は別記事を参照

-- 前方一致: インデックスが使われる（type: range や ref）
EXPLAIN SELECT * FROM customers WHERE name LIKE '田%';

-- 後方・中間一致: インデックスが使われない（type: ALL = フルスキャン）
EXPLAIN SELECT * FROM customers WHERE name LIKE '%田%';

-- 解決策1: 前方一致に書き換えられないか検討する
-- NG: WHERE email LIKE '%@example.com'
-- OK: 設計段階でドメイン列を別に持つ → WHERE domain = 'example.com'

-- 解決策2: MySQL の FULLTEXT インデックス（全文検索）
-- 大量データの中間一致には FULLTEXT INDEX が有効
CREATE FULLTEXT INDEX idx_name ON customers (name);
SELECT * FROM customers
WHERE MATCH(name) AGAINST ('田中' IN BOOLEAN MODE);

-- PostgreSQL: GIN インデックス + pg_trgm（トライグラム）で中間一致を高速化
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_name_trgm ON customers USING GIN (name gin_trgm_ops);

-- インデックスを使った中間一致検索（高速）
SELECT * FROM customers WHERE name LIKE '%田中%';
-- GIN インデックスがあれば %キーワード% でもインデックスが効く

-- MySQL: FULLTEXT インデックスの全文検索
ALTER TABLE customers ADD FULLTEXT INDEX ft_name (name);
SELECT * FROM customers
WHERE MATCH(name) AGAINST ('田中' IN BOOLEAN MODE);

-- 姓だけで検索（スペースの前まで）
SELECT * FROM customers WHERE name LIKE '田中%';

-- メールアドレスのドメイン検索
SELECT * FROM customers WHERE email LIKE '%@example.com';

-- 複数ドメイン（OR で並べる）
SELECT * FROM customers
WHERE email LIKE '%@example.com'
   OR email LIKE '%@example.co.jp';

-- 商品コードの前方一致（例: 「A-」で始まる商品）
SELECT * FROM products WHERE product_code LIKE 'A-%';

-- 電話番号の形式チェック（ハイフンなし 11 桁）
SELECT * FROM customers
WHERE phone NOT LIKE '%-%'       -- ハイフンなし
  AND phone LIKE '0__________';  -- 0 で始まる 11 桁

-- 住所に「東京」を含む（フルスキャンになるため件数が少ない場合のみ推奨）
SELECT * FROM users WHERE address LIKE '%東京%';

-- 複合条件: カテゴリが「食品」かつ名前に「有機」を含む
SELECT * FROM products
WHERE category = '食品'          -- インデックスが効く（= 条件）
  AND name LIKE '%有機%';        -- category で絞った後に LIKE

-- テクニック: 絞り込みやすい条件を先に書く
-- category = '食品' で先に行数を減らすと LIKE のスキャン対象が減る

-- 期間 + LIKE の組み合わせ（WHERE複数条件）
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
  AND customer_name LIKE '田%';