【初心者向け】MariaDBのSQLステートメントと構造をPCREで操作する方法

MariaDB では、Perl 互換正規表現 (PCRE) を利用して、SQL ステートメントと構造を操作することができます。これは、データの抽出、検証、変換など、様々なタスクに役立ちます。

PCRE とは？

PCRE は、強力で柔軟な正規表現エンジンです。Perl で使用される正規表現構文とほぼ互換性があり、多くのプログラミング言語でサポートされています。

MariaDB での PCRE の利用

MariaDB では、PCRE を以下の方法で使用できます。

REPLACE 関数
文字列内の部分文字列を、正規表現パターンと一致する別の文字列に置き換えます。
EXTRACT 関数
正規表現パターンに一致する部分文字列を抽出します。
RLIKE 演算子
文字列が正規表現パターンに一致するかどうかを調べます。
REGEXP 関数
文字列と正規表現パターンの照合を行います。

例：PCRE を使用して MariaDB テーブルからデータを取得する

以下の例では、customers テーブルから、名前が "Alice" または "Bob" である顧客のレコードを取得する方法を示します。

SELECT *
FROM customers
WHERE name REGEXP '^(Alice|Bob)$';

このクエリは、REGEXP 関数を使用して、name 列の値が正規表現パターン ^(Alice|Bob)$ に一致するレコードをすべて選択します。

PCRE パフォーマンス
PCRE 演算子
PCRE 関数
PCRE の構文

特定の文字を含むレコードを取得する

SELECT *
FROM customers
WHERE name REGEXP 'example|example@.*'
    OR email REGEXP 'example|example@.*';

このクエリは、REGEXP 関数を使用して、name 列または email 列の値が正規表現パターン example|example@.* に一致するレコードをすべて選択します。

電話番号をフォーマットする

以下の例では、phone_numbers テーブルの phone_number 列の値を、国際的な形式に変換する方法を示します。

UPDATE phone_numbers
SET phone_number = REPLACE(phone_number, '([^0-9+])', '');
SET phone_number = CONCAT('+', phone_number);

このクエリは、REPLACE 関数を使用して、phone_number 列の値から非数字文字をすべて削除します。その後、CONCAT 関数を使用して、先頭に "+" 記号を追加します。

HTML タグを削除する

以下の例では、articles テーブルの content 列の値から HTML タグをすべて削除する方法を示します。

UPDATE articles
SET content = REPLACE(content, '<[^>]*>', '');

このクエリは、REPLACE 関数を使用して、content 列の値から < > で囲まれたすべての文字列を削除します。

IP アドレスを検証する

以下の例では、users テーブルの ip_address 列の値が有効な IP アドレスであるかどうかを検証する方法を示します。

SELECT *
FROM users
WHERE ip_address REGEXP '^(25[0-5]|2[0-4][0-9]|[0-1]?[0-9][0-9]?)\.( 
			25[0-5]|2[0-4][0-9]|[0-1]?[0-9][0-9]?)\.( 
			25[0-5]|2[0-4][0-9]|[0-1]?[0-9][0-9]?)\.( 
			25[0-5]|2[0-4][0-9]|[0-1]?[0-9][0-9]?)$';

このクエリは、REGEXP 関数を使用して、ip_address 列の値が正規表現パターン ^(25[0-5]|2[0-4][0-9]|[0-1]?[0-9][0-9]?)\.( 25[0-5]|2[0-4][0-9]|[0-1]?[0-9][0-9]?)\.( 25[0-5]|2[0-4][0-9]|[0-1]?[0-9][0-9]?)\.( 25[0-5]|2[0-4][0-9]|[0-1]?[0-9][0-9]?)$ に一致するかどうかを調べます。

以下の例では、users テーブルの password 列の値が十分な強度であるかどうかを検証する方法を示します。

SELECT *
FROM users
WHERE password REGEXP '^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[~!@#$%^&*_])[a-zA-Z0-9~!@#$%^&*_]{8,}$';

このクエリは、REGEXP 関数を使用して、password 列の値が正規表現パターン ^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[~!@#$%^&*_])[a-zA-Z0-9~!@#$%^&*_]{8,}$ に一致するかどうかを調べます。このパターンは、パスワードが少なくとも 8 文字の長さで、小文字、大文字、数字、特殊文字を含むことを保証します。

MariaDB の SQL ステートメントと構造における PCRE ドキュメント [無効

Perl Compatible Regular Expressions (PCRE) は、強力で柔軟な正規表現エンジンですが、代替となる選択肢もいくつか存在します。

代替となる選択肢

Z regex
Unicode に対応した高速な正規表現エンジンです。
RE2
Google が開発した C++ 向けの正規表現ライブラリで、簡潔な構文と高速な処理速度が特徴です。
Hy
Python 向けの高速な正規表現エンジンです。
Boost.Regex
C++ 向けの正規表現ライブラリで、PCRE と同様の構文と機能を提供します。
Oniguruma
PCRE と同等の機能を備え、より高速な処理速度を誇るエンジンです。

それぞれの選択肢の比較

エンジン	長所	短所
PCRE	多くの言語でサポートされている、豊富な機能	処理速度が遅い
Oniguruma	高速な処理速度	PCRE と完全な互換性がない
Boost.Regex	C++ 向けに特化	初心者にとって学習曲線がやや高い
Hy	Python 向けに特化、高速な処理速度	他言語での利用は限定的
RE2	簡潔な構文、高速な処理速度	機能が PCRE ほど豊富ではない
Z regex	Unicode に対応、高速な処理速度	比較的新しく、利用者が少ない

最適な選択肢の選び方

最適な選択肢は、以下の要素によって異なります。

Unicode サポート
Unicode に対応したエンジンが必要な場合は、Z regex がおすすめです。
使いやすさ
初心者であれば、PCRE や Boost.Regex などの比較的使い慣れたエンジンを選ぶと良いでしょう。
機能
PCRE と同等の機能が必要な場合は、Oniguruma がおすすめです。
処理速度
高速な処理速度が求められる場合は、Oniguruma や RE2 などのエンジンが適しています。
使用しているプログラミング言語
一部のエンジンは特定の言語に特化しています。