MariaDB正規表現の代替手段：LIKEやSUBSTRINGとの使い分けでSQLを最適化

MariaDBでは、主に以下の演算子や関数を使って正規表現を扱います。

• REGEXP_SUBSTR(string, pattern [, position [, occurrence [, match_type]]]): 正規表現にマッチする部分文字列を返します。
• REGEXP_REPLACE(string, pattern, replace_by [, position [, occurrence [, match_type]]]): 正規表現にマッチする部分文字列を別の文字列で置換します。
• REGEXP_INSTR(string, pattern [, position [, occurrence [, return_option [, match_type]]]]): 正規表現にマッチする部分文字列の開始位置を返します。
• NOT REGEXP (または NOT RLIKE): REGEXPの逆で、パターンにマッチしないかどうかを調べます。
• REGEXP (または RLIKE): 指定された文字列が正規表現パターンにマッチするかどうかを調べます。マッチすれば1、しなければ0を返します。
  • SELECT column_name FROM table_name WHERE column_name REGEXP 'パターン';

MariaDBの正規表現は、デフォルトでPOSIX拡張正規表現 (ERE) の構文に基づいていますが、MariaDB 10.0.5以降ではPCRE (Perl Compatible Regular Expressions) ライブラリが導入され、より高度な正規表現機能が利用できるようになりました。これにより、再帰パターンや後方参照（look-ahead/look-behind assertions）などもサポートされています。

正規表現の主なメタ文字とパターン

正規表現では、特定の意味を持つ「メタ文字」を組み合わせてパターンを構築します。よく使われるものをいくつかご紹介します。

• \ (バックスラッシュ): メタ文字をエスケープして、リテラル文字として扱います。MariaDBでは文字列リテラルとして\自体をエスケープする必要があるため、\\と2重に記述することが多いです。
  • 例: \. はリテラルのドット(.)にマッチします。
• {m,n}: 直前の文字やグループがm回以上n回以下繰り返される場合にマッチします。
  • 例: a{2,4} は "aa", "aaa", "aaaa" にマッチします。
• {n}: 直前の文字やグループがn回繰り返される場合にマッチします。
  • 例: a{3} は "aaa" にマッチします。
• |: 論理ORを表し、複数のパターンの中からいずれかにマッチします。
  • 例: apple|orange は "apple" または "orange" にマッチします。
• [^...]: 角括弧内にない任意の1文字にマッチします。^が角括弧の先頭にある場合、否定の意味になります。
  • 例: [^0-9] は数字以外の任意の1文字にマッチします。
• []: 角括弧内の任意の1文字にマッチします。
  • 例: [abc] は "a", "b", "c" のいずれかにマッチします。
  • 例: [0-9] は任意の数字にマッチします。
• ?: 直前の文字やグループが0回または1回出現する場合にマッチします。
  • 例: colou?r は "color" または "colour" にマッチします。
• +: 直前の文字やグループが1回以上繰り返される場合にマッチします。
  • 例: ab+c は "abc", "abbc", "abbbc" などにマッチしますが、"ac" にはマッチしません。
• *: 直前の文字やグループが0回以上繰り返される場合にマッチします。
  • 例: ab*c は "ac", "abc", "abbc", "abbbc" などにマッチします。
• .: 改行を除く任意の1文字にマッチします。
  • 例: a.c は "abc", "axc", "a1c" などにマッチします。
• $: 文字列の末尾にマッチします。
  • 例: xyz$ は "...xyz" で終わる文字列にマッチします。
• ^: 文字列の先頭にマッチします。
  • 例: ^abc は "abc..." で始まる文字列にマッチします。

使用例

-- 'maria' で始まるすべての名前を検索
SELECT name FROM users WHERE name REGEXP '^maria';

-- 'com' または 'net' で終わるすべてのメールアドレスを検索
SELECT email FROM users WHERE email REGEXP '(com|net)$';

-- 数字が3つ連続する文字列を含むデータを検索
SELECT product_code FROM products WHERE product_code REGEXP '[0-9]{3}';

-- 'foo' の後に任意の1文字が続き、その後 'bar' が続くパターンを検索
SELECT description FROM items WHERE description REGEXP 'foo.bar';

-- 'old_price' を 'new_price' に置換
SELECT REGEXP_REPLACE('This is an old_price.', 'old_price', 'new_price');
-- 結果: 'This is an new_price.'

-- 特定のパターンにマッチする部分文字列を抽出
SELECT REGEXP_SUBSTR('[email protected]', '@[a-z]+\\.com');
-- 結果: '@example.com'

LIKEとの違い

LIKE演算子は、シンプルなワイルドカード (% で任意の文字列、_ で任意の1文字) を使ったパターンマッチングに限定されます。一方、正規表現はより複雑で柔軟なパターン定義が可能であり、高度な文字列検索やデータ加工を行う際に非常に強力です。

• PCREとの互換性: MariaDB 10.0.5以降でPCREが導入されたことにより、それ以前のバージョンや他のデータベースシステムとの正規表現の挙動に若干の違いが生じる可能性があります。
• 大文字・小文字の区別: デフォルトでは大文字・小文字を区別しませんが、バイナリ文字列に対しては区別されます。また、REGEXP関数のmatch_type引数で制御することも可能です。
• パフォーマンス: 正規表現による検索は、インデックスを利用しにくいため、大きなテーブルに対してはパフォーマンスが低下する可能性があります。パフォーマンスが重要な場合は、必要に応じてLIKEと組み合わせたり、アプリケーション側で処理したりすることも検討してください。

---

パターンが意図した通りにマッチしない

これは最もよくある問題です。正規表現の構文は非常に厳密であり、少しのミスでも期待する結果が得られないことがあります。

一般的な原因と解決策

• 特殊な文字の扱い
  改行文字 (\n) やタブ文字 (\t) など、目に見えない特殊文字を扱う場合、パターンにそれらを含める必要があります。
• キャプチャリンググループと非キャプチャリンググループ
  ()はグループ化とキャプチャの両方を行います。?: を使って (?:...) とすると非キャプチャリンググループになります。複雑なパターンで後方参照が必要ない場合は、パフォーマンスのために非キャプチャリンググループを使用することを検討します。
• 文字クラスの誤用
  [abc] は a, b, c のいずれか1文字にマッチします。[0-9]は数字1文字にマッチします。
  • よくある間違い
    [A-Z]で小文字もマッチさせたい場合、MariaDBのデフォルトでは大文字・小文字を区別しないため問題ないことが多いですが、バイナリ文字列や特定の照合順序では区別されるため注意が必要です。明示的に大文字・小文字両方をマッチさせたい場合は[a-zA-Z]とします。
  • POSIX文字クラス ([:alnum:], [:digit:]など) の使用
    これらは便利ですが、正確な構文を覚える必要があります (例: [[:digit:]] であって [\d] ではない場合があります。MariaDB 10.0.5以降のPCRE互換では\dも使えますが、旧バージョンや移植性を考慮する場合は[[:digit:]]が安全です)。
• 量指定子の誤用 (*, +, ?, {})
  
  • * (0回以上) と + (1回以上) の違いを理解する。例えば、a*は"" (空文字列) にもマッチしますが、a+はマッチしません。
  • ? (0回または1回) は、オプションの文字やグループに便利です。
  • {n}、{m,n} などの回数指定が適切か確認します。
• アンカーの誤用 (^, $)
  ^は文字列の先頭、$は文字列の末尾にマッチします。これらを付けると、文字列全体がパターンに厳密に一致しないとマッチしません。部分一致を探しているのにこれらを使用すると、結果が得られないことがあります。
  • 例
    SELECT 'foobar' REGEXP '^foo$'; はマッチしません。'foobar'全体が'foo'ではないからです。
  • 解決策
    部分一致を探している場合は、^や$を削除するか、適切に使用します。
    • SELECT 'foobar' REGEXP 'foo';
    • SELECT 'foobar' REGEXP '^foo.*';
• メタ文字のエスケープ不足
  . * + ? ( ) [ ] { } | ^ $ \ などのメタ文字をリテラルとして扱いたい場合、前にバックスラッシュ(\)を付けてエスケープする必要があります。MariaDBの文字列リテラル内では、バックスラッシュ自体もエスケープする必要があるため、\をマッチさせたい場合は\\と記述します。
  • 例
    ドット（.）をリテラルとしてマッチさせたい場合、\.ではなく\\.と書く必要があります。
  • 誤った例
    SELECT 'a.b' REGEXP 'a.b'; （これは aab, axb などにもマッチします）
  • 正しい例
    SELECT 'a.b' REGEXP 'a\\.b';

トラブルシューティングのヒント

• REGEXP_SUBSTR や REGEXP_REPLACE を使って検証する
  REGEXPでマッチするかどうかだけでなく、実際にマッチした部分文字列をREGEXP_SUBSTRで抽出したり、REGEXP_REPLACEで置換して確認することで、意図通りにパターンが機能しているかを確認できます。
• オンラインの正規表現テスターを利用する
  regex101.com や regexr.com などのサイトは、正規表現の動作をリアルタイムで視覚的に確認でき、各部分が何にマッチしているかを詳しく表示してくれるため、非常に有用です。MariaDBがPCREをサポートしている場合、PCREモードでテストできます。
• 小さな文字列とパターンで試す
  複雑な正規表現を構築する前に、小さなテスト文字列と簡単なパターンで動作を確認します。

-- 例: エスケープ忘れ
SELECT 'abc.def' REGEXP 'abc.def'; -- 1 を返す (abcXdef にもマッチしてしまう)
SELECT 'abc.def' REGEXP 'abc\\.def'; -- 1 を返す (正しくドットをエスケープ)

-- 例: アンカーの誤用
SELECT 'hello world' REGEXP 'world'; -- 1 を返す
SELECT 'hello world' REGEXP '^world$'; -- 0 を返す (文字列全体が 'world' ではないため)

パフォーマンスの問題

正規表現は非常に柔軟ですが、その分、LIKE演算子と比較して処理が重くなる傾向があります。特に大量のデータに対して正規表現を使用すると、クエリの実行が非常に遅くなることがあります。

一般的な原因と解決策

• 解決策
  
  1. REGEXPとLIKEの併用
     最も一般的な最適化手法です。まずLIKEを使ってインデックスが効く範囲でデータを絞り込み、その後、残りの少量のデータに対してREGEXPを適用します。
     • 例
       WHERE column_name LIKE 'prefix%' AND column_name REGEXP '^prefix[0-9]{3}$'
     • この場合、LIKE 'prefix%'がまずインデックスを使って効率的に絞り込み、その後、絞り込まれた行に対してのみ正規表現が適用されます。
  2. パターンを具体的にする
     可能な限りパターンを具体的にし、曖昧な部分を減らすことで、正規表現エンジンの処理負荷を軽減します。
  3. MariaDBのバージョンを確認する
     MariaDB 10.0.5以降でPCREが導入されたことで、正規表現の処理効率が向上している場合があります。
  4. 必要であればアプリケーション側で処理する
     データベースでの正規表現処理がボトルネックになる場合、データをアプリケーションに取得してから、アプリケーション側で正規表現処理を行うことを検討します。ただし、データ転送量が増える可能性があるため、慎重に検討が必要です。
  5. EXPLAINを使ってクエリの実行計画を確認する
     EXPLAIN SELECT ... WHERE column REGEXP '...' とすることで、クエリがどのように実行されているか、インデックスが利用されているかなどを確認できます。
• 不適切なパターン
  非効率なパターン (例: 必要以上に曖昧なパターンや、大量のバックトラッキングを引き起こすパターン) は、処理時間を大幅に増加させます。
  • 例
    .*foo.* のようなパターンは、文字列全体をスキャンするため非効率です。
• インデックスが利用されない
  REGEXP演算子は、基本的にインデックスを利用できません。これは、正規表現が文字列のどこにでもマッチする可能性があるため、データベースがインデックスを使って効率的に行を絞り込むことができないためです。

文字コードと照合順序の問題

MariaDBの正規表現は、デフォルトで大文字・小文字を区別しませんが、これは使用している文字セットや照合順序に依存する場合があります。特にマルチバイト文字やアクセント付き文字を扱う場合に問題となることがあります。

一般的な原因と解決策

• 大文字・小文字の区別
  
  • デフォルトでは大文字・小文字を区別しません。
  • 明示的に大文字・小文字を区別したい場合は、BINARYキーワードを使用するか、カラムの照合順序をバイナリ照合順序 (_bin) に変更します。
  • SELECT 'TEST' REGEXP BINARY 'test'; -- 0 を返す

構文エラー (ERROR 1139 (42000): Got error ... from regexp)

これは正規表現のパターン自体に構文上の誤りがある場合に発生します。

一般的な原因と解決策

• 不適切な量指定子
  ++ のように量指定子が連続している場合など。
• 範囲指定の誤り
  [a-z のように範囲が閉じられていない、または [z-a] のように開始が終了より大きい。
• 無効なエスケープシーケンス
  存在しないエスケープシーケンス (\z など) を使用するとエラーになります。
• 閉じ括弧、閉じ角括弧、閉じ波括弧の欠如
  (, [, { などを使用した場合、対応する閉じ文字がないと構文エラーになります。

トラブルシューティングのヒント

• オンラインの正規表現テスターを利用する
  構文エラーも即座に検出してくれます。
• 正規表現を分割してテストする
  長い正規表現の場合、怪しい部分をコメントアウトしたり、削除したりして、エラーの原因となっている最小限のパターンを特定します。
• エラーメッセージをよく読む
  Got error 'missing ) at offset 100' from regexp のように、どの部分で、どのメタ文字が問題であるか、オフセット（文字位置）が示されることがあります。

これは非常に単純ですが、SQLの経験が浅い人が犯しやすい間違いです。MariaDB（およびMySQL）で正規表現を扱う演算子はREGEXPまたはRLIKEです。

一般的な原因と解決策

• 解決策
  REGEXではなく、常にREGEXPまたはRLIKEを使用します。

MariaDBで正規表現を扱う際のトラブルシューティングは、主に以下の点を意識することが重要です。

• 文字コードと照合順序
  特に非ASCII文字を扱う場合は、文字コード設定を確認します。
• パフォーマンスの考慮
  大量のデータに対してはLIKEとの併用など、最適化を検討します。
• テストとデバッグ
  小さなデータとオンラインツールを使って、パターンが意図通りに動作するかを繰り返しテストします。
• 正規表現の構文に習熟する
  メタ文字の意味、エスケープのルール、量指定子の使い方などを正確に理解することが基本です。

---

-- サンプルテーブルの作成
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    description TEXT,
    sku VARCHAR(100), -- Stock Keeping Unit
    price DECIMAL(10, 2)
);

-- サンプルデータの挿入
INSERT INTO products (product_name, description, sku, price) VALUES
('Laptop Pro X1', 'High performance laptop with 16GB RAM and 512GB SSD. Model: LPX1-2023', 'LPX1-001', 1200.00),
('Gaming PC Ultra', 'Ultimate gaming machine with RTX 4090. Serial: GMULTRA-A123', 'GPU-005', 2500.00),
('External SSD 1TB', 'Portable SSD for fast data transfer. USB 3.2 Gen2. Code: ESD-1TB-V2', 'SSD-010', 150.00),
('USB-C Hub 7-in-1', 'Multiport adapter for modern laptops. Model: USBCHUB-7A', 'HUB-003', 45.00),
('Wireless Mouse M300', 'Ergonomic design with silent clicks. Model: WM-M300-BLK', 'WM-007', 25.00),
('4K Monitor 27 inch', 'Vivid display for professional use. Model: MON-4K-27-PRO', 'MON-012', 450.00),
('Old Laptop A1', 'An old laptop from 2010. Still works for basic tasks. LPX1-2010', 'OLDA-001', 100.00),
('Old Desktop B2', 'Vintage PC from 2005. Best for collectors. Code: DESKTOP-OLD-B2', 'OLDB-002', 50.00),
('Smartphone X Pro', 'Latest smartphone with triple camera. SM-XP-2024', 'SM-020', 800.00);

REGEXP / RLIKE 演算子 (マッチングの検索)

指定された文字列が正規表現パターンにマッチするかどうかを調べます。マッチすれば1（真）、しなければ0（偽）を返します。

例1: 特定のキーワードを含む製品を検索

descriptionカラムに「SSD」または「RAM」という単語が含まれる製品を検索します。

SELECT product_name, description
FROM products
WHERE description REGEXP 'SSD|RAM';

結果

+--------------------+-----------------------------------------------------+
| product_name       | description                                         |
+--------------------+-----------------------------------------------------+
| Laptop Pro X1      | High performance laptop with 16GB RAM and 512GB SSD.|
| External SSD 1TB   | Portable SSD for fast data transfer. USB 3.2 Gen2.  |
+--------------------+-----------------------------------------------------+

例2: SKUが特定のパターンに従っている製品を検索

SKUが「3つの英字」-「3桁の数字」の形式（例: LPX-001）に従っている製品を検索します。

SELECT product_name, sku
FROM products
WHERE sku REGEXP '^[A-Z]{3}-[0-9]{3}$';

結果

+--------------------+-----------+
| product_name       | sku       |
+--------------------+-----------+
| Laptop Pro X1      | LPX1-001  | -- これは残念ながらマッチしません。LPX1-001 は {3} ではなく {4} になっているため
| Gaming PC Ultra    | GPU-005   |
| External SSD 1TB   | SSD-010   |
| USB-C Hub 7-in-1   | HUB-003   |
| Wireless Mouse M300| WM-007    |
| 4K Monitor 27 inch | MON-012   |
+--------------------+-----------+

解説

• $: 文字列の末尾
• [0-9]{3}: 数字が3回繰り返す
• -: リテラルのハイフン
• [A-Z]{3}: 大文字アルファベットが3回繰り返す
• ^: 文字列の先頭

修正版 (LPX1-001も含むように)

SELECT product_name, sku
FROM products
WHERE sku REGEXP '^[A-Z]{2,4}-[0-9]{3}$';

修正版の結果

+--------------------+-----------+
| product_name       | sku       |
+--------------------+-----------+
| Laptop Pro X1      | LPX1-001  |
| Gaming PC Ultra    | GPU-005   |
| External SSD 1TB   | SSD-010   |
| USB-C Hub 7-in-1   | HUB-003   |
| Wireless Mouse M300| WM-007    |
| 4K Monitor 27 inch | MON-012   |
| Old Laptop A1      | OLDA-001  |
| Old Desktop B2     | OLDB-002  |
| Smartphone X Pro   | SM-020    |
+--------------------+-----------+

REGEXP_INSTR(string, pattern [, position [, occurrence [, return_option [, match_type]]]]) (開始位置の取得)

正規表現にマッチする部分文字列の開始位置（1から始まる）を返します。

例: 説明文中の「Model:」または「Serial:」の開始位置を特定

SELECT
    product_name,
    description,
    REGEXP_INSTR(description, 'Model:|Serial:') AS match_start_position
FROM products
WHERE description REGEXP 'Model:|Serial:';

結果

+---------------------+-----------------------------------------------------+----------------------+
| product_name        | description                                         | match_start_position |
+---------------------+-----------------------------------------------------+----------------------+
| Laptop Pro X1       | High performance laptop with 16GB RAM and 512GB SSD.| 47                   |
| Gaming PC Ultra     | Ultimate gaming machine with RTX 4090. Serial: GMUL| 45                   |
| USB-C Hub 7-in-1    | Multiport adapter for modern laptops. Model: USBCHUB| 42                   |
| Wireless Mouse M300 | Ergonomic design with silent clicks. Model: WM-M300-| 40                   |
| 4K Monitor 27 inch  | Vivid display for professional use. Model: MON-4K-27| 39                   |
+---------------------+-----------------------------------------------------+----------------------+

REGEXP_SUBSTR(string, pattern [, position [, occurrence [, match_type]]]) (部分文字列の抽出)

正規表現にマッチする部分文字列を抽出します。

例: 説明文中のモデル番号を抽出

「Model: 」または「Serial: 」の後に続く文字列を抽出します。

SELECT
    product_name,
    description,
    REGEXP_SUBSTR(description, '(Model:|Serial:)[[:space:]]*[A-Za-z0-9-]+') AS extracted_model_serial
FROM products
WHERE description REGEXP '(Model:|Serial:)';

結果

+---------------------+-----------------------------------------------------+------------------------+
| product_name        | description                                         | extracted_model_serial |
+---------------------+-----------------------------------------------------+------------------------+
| Laptop Pro X1       | High performance laptop with 16GB RAM and 512GB SSD.| Model: LPX1-2023       |
| Gaming PC Ultra     | Ultimate gaming machine with RTX 4090. Serial: GMUL| Serial: GMULTRA-A123   |
| USB-C Hub 7-in-1    | Multiport adapter for modern laptops. Model: USBCHUB| Model: USBCHUB-7A      |
| Wireless Mouse M300 | Ergonomic design with silent clicks. Model: WM-M300-| Model: WM-M300-BLK     |
| 4K Monitor 27 inch  | Vivid display for professional use. Model: MON-4K-27| Model: MON-4K-27-PRO   |
+---------------------+-----------------------------------------------------+------------------------+

解説

• [A-Za-z0-9-]+: 英数字とハイフンが1回以上繰り返すパターンにマッチ。
• [[:space:]]*: 0個以上の空白文字にマッチ（POSIX文字クラス [:space:]を使用）。
• (Model:|Serial:): 「Model:」または「Serial:」のいずれかにマッチするグループ。

REGEXP_REPLACE(string, pattern, replace_by [, position [, occurrence [, match_type]]]) (文字列の置換)

正規表現にマッチする部分文字列を別の文字列で置換します。

例1: 古い形式のモデル番号を新しい形式に置換

説明文中の「LPX1-YYYY」（YYYYは年）という形式を「Product Model: LPX1-YYYY」に置換します。

SELECT
    product_name,
    description AS original_description,
    REGEXP_REPLACE(description, 'LPX1-([0-9]{4})', 'Product Model: LPX1-\\1') AS modified_description
FROM products
WHERE description REGEXP 'LPX1-[0-9]{4}';

結果

+-----------------+--------------------------+----------------------------+
| product_name    | original_description     | modified_description       |
+-----------------+--------------------------+----------------------------+
| Laptop Pro X1   | ... Model: LPX1-2023     | ... Model: Product Model: LPX1-2023 |
| Old Laptop A1   | ... LPX1-2010            | ... Product Model: LPX1-2010|
+-----------------+--------------------------+----------------------------+

解説

• Product Model: LPX1-\\1: 置換する文字列です。\\1 は、パターンでキャプチャした最初のグループの内容（この場合は4桁の数字）を指します。
• LPX1-([0-9]{4}): LPX1- の後に4桁の数字が続くパターン。( ) で囲むことで、4桁の数字を「キャプチャリンググループ」として捕捉します。

例2: 説明文からSKUのような形式を削除

説明文中に誤って含まれる可能性のある[A-Z]{3,4}-[0-9]{3}形式の文字列を削除します。

SELECT
    product_name,
    description AS original_description,
    REGEXP_REPLACE(description, '[A-Z]{3,4}-[0-9]{3}', '') AS cleaned_description
FROM products
WHERE description REGEXP '[A-Z]{3,4}-[0-9]{3}';

結果

+--------------------+-----------------------------------------------------+-----------------------------------------------------+
| product_name       | original_description                                | cleaned_description                                 |
+--------------------+-----------------------------------------------------+-----------------------------------------------------+
| Laptop Pro X1      | High performance laptop with 16GB RAM and 512GB SSD.| High performance laptop with 16GB RAM and 512GB SSD.|
| Gaming PC Ultra    | Ultimate gaming machine with RTX 4090. Serial: GMUL| Ultimate gaming machine with RTX 4090. Serial:      |
| External SSD 1TB   | Portable SSD for fast data transfer. USB 3.2 Gen2. | Portable SSD for fast data transfer. USB 3.2 Gen2.  |
| USB-C Hub 7-in-1   | Multiport adapter for modern laptops. Model: USBCHU| Multiport adapter for modern laptops. Model:        |
| Wireless Mouse M300| Ergonomic design with silent clicks. Model: WM-M300| Ergonomic design with silent clicks. Model:         |
| 4K Monitor 27 inch | Vivid display for professional use. Model: MON-4K-27| Vivid display for professional use. Model:          |
| Old Laptop A1      | An old laptop from 2010. Still works for basic tasks| An old laptop from 2010. Still works for basic tasks|
| Old Desktop B2     | Vintage PC from 2005. Best for collectors. Code: DES| Vintage PC from 2005. Best for collectors. Code:    |
| Smartphone X Pro   | Latest smartphone with triple camera. SM-XP-2024   | Latest smartphone with triple camera. SM-XP-2024    |
+--------------------+-----------------------------------------------------+-----------------------------------------------------+

注記
この例では、説明文中のSKUに似たパターンを削除しましたが、Laptop Pro X1など、実際のSKUが説明文中に含まれていない行は変化しません。これは期待通りの動作です。

MariaDB 10.0.5 以降で利用可能なPCRE互換の正規表現では、match_type オプションを使って、大文字・小文字の区別や改行の扱いなどを細かく制御できます。

例: 大文字・小文字を区別して検索 (PCREの 'c' オプション)

デフォルトでは大文字・小文字を区別しませんが、'c' フラグを指定すると区別するようになります。

-- デフォルト (区別しない)
SELECT 'Apple' REGEXP 'apple'; -- 1 を返す

-- 'c' フラグで区別する
SELECT 'Apple' REGEXP 'apple' COLLATE utf8mb4_bin; -- 0 を返す (バイナリ照合順序を使うか、match_type を使う)

-- REGEXP_INSTR の match_type を使う例 (MariaDB 10.0.5+ PCRE)
-- 'Apple' は 'apple' とマッチしない
SELECT REGEXP_INSTR('Apple', 'apple', 1, 1, 0, 'c'); -- 0 を返す
SELECT REGEXP_INSTR('apple', 'apple', 1, 1, 0, 'c'); -- 1 を返す

-- 'i' フラグで明示的に大文字・小文字を区別しない (冗長だが明確)
SELECT REGEXP_INSTR('Apple', 'apple', 1, 1, 0, 'i'); -- 1 を返す

• SQL文の中で、REGEXP演算子の場合はCOLLATE句を使うことが多いです。関数ではmatch_type引数が便利です。
• match_type オプションは、MariaDBのバージョンとPCREライブラリのサポートに依存します。古いバージョンでは利用できない場合があります。

---

LIKE 演算子

最も一般的で基本的な文字列パターンマッチングの代替手段です。ワイルドカード文字（%と_）を使用します。

• _: 任意の1文字にマッチします。
• %: 任意の数の文字（0文字以上）にマッチします。

メリット

• パフォーマンス: 条件によってはインデックスを利用できるため、大量のデータに対して非常に高速に動作する可能性があります。特に、LIKE 'prefix%' のように前方一致でインデックスが貼られているカラムに対しては、REGEXPよりも圧倒的に高速です。
• シンプルで分かりやすい: 簡単なパターンには直感的です。

デメリット

• 部分一致のインデックス非効率性: LIKE '%suffix' や LIKE '%middle%' のような後方一致や中間一致では、インデックスが利用されにくく、フルスキャンになるためパフォーマンスが低下します。
• 機能の限界: 正規表現のような複雑なパターン（繰り返し、選択肢、グループ化など）は表現できません。

使用例

-- 'Laptop' で始まる製品名を検索
SELECT product_name FROM products WHERE product_name LIKE 'Laptop%';

-- SKU が 'SSD' で始まり、その後に任意の1文字、その後 '010' が続くものを検索
SELECT product_name, sku FROM products WHERE sku LIKE 'SSD_010';

-- 説明文に 'gaming' が含まれるが、どこにあっても良い場合
SELECT product_name, description FROM products WHERE description LIKE '%gaming%';

LOCATE(), INSTR(), POSITION() 関数

これらの関数は、文字列内の特定の部分文字列の位置を見つけるために使われます。正規表現ほど複雑なパターンは扱えませんが、特定のキーワードの存在や位置を確認するのにシンプルで効率的です。

• POSITION(substring IN string): INSTRと同じ動作をします。
• INSTR(string, substring): string 内で substring が最初に出現する位置を返します（LOCATEの引数の順序が逆）。
• LOCATE(substring, string [, start_position]): string 内で substring が最初に出現する位置を返します。start_position から検索を開始できます。

メリット

• インデックスの利用: 特定のケース（例: 関数の結果をインデックス化する仮想カラムや、検索条件として最適化される場合）でパフォーマンスに寄与する可能性がありますが、通常はフルスキャンになります。
• シンプルで高速: 単純な部分文字列検索には最適です。

デメリット

• 単一の部分文字列に限定: 正規表現のようなパターンマッチングはできません。

使用例

-- 説明文に 'SSD' が含まれる製品を検索（位置が1以上なら存在する）
SELECT product_name, description
FROM products
WHERE LOCATE('SSD', description) > 0;

-- SKU に '00' が含まれる製品を検索
SELECT product_name, sku
FROM products
WHERE INSTR(sku, '00') > 0;

SUBSTRING() と LEFT(), RIGHT()

文字列の一部を抽出する関数です。固定長や特定の位置からの文字列を扱う場合に便利です。

• RIGHT(string, length): string の右端から length 分の文字を抽出します。
• LEFT(string, length): string の左端から length 分の文字を抽出します。
• SUBSTRING(string, start_position [, length]): string の start_position から length 分の文字を抽出します。

メリット

• 明確な抽出: 固定された位置や長さで文字列を抽出するのに適しています。

デメリット

• パターンマッチングには不向き: 正規表現のように動的なパターンで抽出することはできません。

使用例

-- SKU の最初の3文字が 'LPX' である製品を検索
SELECT product_name, sku
FROM products
WHERE LEFT(sku, 3) = 'LPX';

-- 説明文の最初から10文字を抽出
SELECT product_name, SUBSTRING(description, 1, 10) AS short_description
FROM products;

文字列操作関数 (REPLACE(), CONCAT(), TRIM() など)

これらの関数は、文字列の特定の部分を置換したり、結合したり、余分な空白を除去したりするのに使われます。

• TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str): 文字列の先頭、末尾、または両端から指定された文字を除去します。
• CONCAT(string1, string2, ...): 複数の文字列を結合します。
• REPLACE(string, from_string, to_string): string 内のすべての from_string を to_string で置換します。

メリット

• シンプルな置換・整形: 固定文字列の置換や基本的な整形に最適です。

デメリット

• パターンベースの置換は不可: 正規表現のようにパターンでマッチした内容に応じて置換内容を変えることはできません。

使用例

-- 説明文中の 'SSD' を 'Solid State Drive' に置換
SELECT product_name, REPLACE(description, 'SSD', 'Solid State Drive') AS new_description
FROM products;

-- 製品名とSKUを結合して新しい識別子を作成
SELECT CONCAT(product_name, ' (', sku, ')') AS full_identifier
FROM products;

アプリケーション層での処理

SQL内で正規表現を使用する代わりに、データベースからデータを取得した後、アプリケーションコード（PHP, Python, Java, Node.jsなど）で正規表現処理を行う方法です。

メリット

• パフォーマンスの分散: データベースサーバーの負荷を軽減し、アプリケーションサーバーに処理をオフロードできます。
• 柔軟なエラーハンドリング: アプリケーション側でエラー処理やデバッグがしやすくなります。
• 豊富な正規表現ライブラリ: 各プログラミング言語には、MariaDBのPCRE互換性よりもさらに高度な機能（例: 再帰的なパターン、名前付きキャプチャグループなど）を持つ正規表現ライブラリが用意されています。

デメリット

• 複雑性の増加: アプリケーションコードとSQLの両方でロジックを管理する必要があります。
• データ転送量: フィルターや変換を行う前の全データ（または大量のデータ）をデータベースからアプリケーションに転送する必要があるため、ネットワークI/Oが増加し、大規模なデータセットでは非効率になる可能性があります。

import mysql.connector
import re

# MariaDB接続設定 (仮)
config = {
    'user': 'your_user',
    'password': 'your_password',
    'host': '127.0.0.1',
    'database': 'your_database'
}

try:
    conn = mysql.connector.connect(**config)
    cursor = conn.cursor(dictionary=True) # 辞書形式で結果を取得

    cursor.execute("SELECT product_name, description FROM products")
    products = cursor.fetchall()

    pattern = r'Model:\s*([A-Za-z0-9-]+)' # Pythonの正規表現パターン

    for product in products:
        match = re.search(pattern, product['description'])
        if match:
            model_number = match.group(1)
            print(f"Product: {product['product_name']}, Extracted Model: {model_number}")
        else:
            print(f"Product: {product['product_name']}, No model found.")

except mysql.connector.Error as err:
    print(f"Error: {err}")
finally:
    if conn.is_connected():
        cursor.close()
        conn.close()

• どうしても複雑なパターンマッチングが必要で、かつデータベースのパフォーマンスが許容範囲内: REGEXP を使用します。ただし、パフォーマンス問題が発生しないか注意深く監視してください。
• データベースの負荷軽減や、MariaDBの正規表現では不十分な複雑なパターン: アプリケーション層での処理を検討します。
• 固定文字列の置換や整形: REPLACE() などの文字列関数が適しています。
• 固定位置からの文字列抽出: SUBSTRING() / LEFT() / RIGHT() が明確です。
• 特定の文字列の存在確認や位置: LOCATE() / INSTR() がシンプルで効率的です。
• 単純な前方/後方/中間一致: LIKE が最も適切で、インデックスの恩恵を受けやすいです。