mregexpとは
目的
今のところ(mysql 4.0.27, 5.0.24a)、MySQLのネイティブ関数REGEXPは日本語の文字列を正しく処理できません (一方、LIKEやSUBSTRINGなどは日本語の処理に対応しています)。そこで日本語をきちんと扱える正規表現関数、mregexpというものをユーザー定義関数(UDF=User Defined Function)という仕組みを用いて作りました。
機能
- mregexpは、REGEXPと同じように、WHERE句でレコードの抽出条件に使うことができます。
REGEXPの問題点
REGEXPは日本語などのマルチバイト文字を考慮していないため、以下のような問題があります。
- パターン'
あ.う'が文字列'あいう'にマッチしません。
正規表現の「.」が、任意の1文字ではなく、任意の1バイトにマッチしてしまうからです。● LIKEは期待通り「あいう」がマッチするが、 mysql> SELECT * FROM regexp_test WHERE s LIKE 'あ_う'; +--------+ | あいう | | あXう | +--------+ ● REGEXPでは「あいう」がマッチしない。 mysql> SELECT * FROM regexp_test WHERE s REGEXP 'あ.う'; +-------+ | あXう | +-------+ ● mregexpはLIKEと同じように期待した通りにマッチします。 mysql> SELECT * FROM regexp_test WHERE mregexp(s, 'あ.う'); +--------+ | あいう | | あXう | +--------+
- EUC-JPの場合、パターン'
い'が文字列'イあ'にマッチしてしまう。
これは「い」のコードポイントが0xA4A4で、 「イあ」のが0xA5A4A4A2であるために起こる現象です。イ あ ┌─┐ ┌─┐ A5 A4 A4 A2 └─┘ い● LIKEだと「イあ」はマッチしないが、 mysql> SELECT * FROM regexp_test WHERE s LIKE '%い%'; +--------+ | s | +--------+ | あいう | +--------+ ● REGEXPだと「い」を含まない「イあ」がマッチしてしまう。 mysql> SELECT * FROM regexp_test WHERE s REGEXP 'い'; +--------+ | s | +--------+ | あいう | | イあ | +--------+ ● mregexpはLIKEと同じように期待した通りにマッチします。 mysql> SELECT * FROM regexp_test WHERE mregexp(s, 'い'); +--------+ | あいう | +--------+
mregexpの正規表現の処理には鬼車(Oniguruma)を使用しています。 Onigurumaは日本語の文字エンコーディングであるEUC-JP, Shift_JIS, UTF-8をはじめ、様々な文字エンコーディングに対応しているため、mregexpを使えばこのような問題は起こりません。
ライセンス
ライセンスは修正済BSDライセンスです。 ライセンス条件を満たす限り、商用/非商用、クローズ/オープンソースに関わらず再配布、使用することができます。 詳細なライセンスは配布物に含まれるファイルCOPYINGを参照してください。 参考までに、FreeBSD Projectのサイトに修正済BSDライセンスの日本語訳があります。
mregexpの記事を書きました
翔泳社
2006-10-04発売
翔泳社のムック『超・極める!MySQL』のUDFの章に、mregexpの記事を書きました。
REGEXPの問題点、mregexpを作るに至った経緯、ソースコードmregexp.cの詳説などなどを書きましたので、手にとって見てもらえればと思います。
詳しい使い方
文法
mregexp(expr,pat[,encoding])
文字列exprに対して、正規表現patのパターンマッチを実行します。 デフォルトでは大文字小文字を区別してパターンマッチを実行します。
mregexp関数の返値は、パターンにマッチした場合は1、マッチしなかった場合は0になります。
encodingでは、exprとpatの文字エンコーディングを指定します。
encodingとして指定できるものは以下の通りで、これらは大文字小文字を区別しません。つまり、'euc-jp'と'Euc-Jp'とは同値です。
また、encodingは省略可能で、そのときはコンパイル時にDEFAULT_ENCODINGで指定したものになります。
Shift_JIS,sjis,cp932EUC-JP,ujis,eucjpms
UTF-8,utf8UTF-16BEUTF-16LEUTF-32BEUTF-32LE
ASCIIISO-8859-1,latin1ISO-8859-2,latin2ISO-8859-3ISO-8859-4ISO-8859-5ISO-8859-6ISO-8859-7,greekISO-8859-8,hebrewISO-8859-9,latin5ISO-8859-10ISO-8859-11ISO-8859-13,latin7ISO-8859-14ISO-8859-15ISO-8859-16
EUC-TWEUC-KR,euckrEUC-CNKOI8-R,koi8rBig5GB18030
もう一つ、 mregexpのバージョン、mregexp関数のencodingの初期値、Onigurumaのバージョンを含む文字列を返す関数mregexp_versionがあります。
mregexp_version()
mysql> select mregexp_version(); mregexp-1.0 [EUC-JP] (oniguruma-4.4.4)
使用例
以下 に使用例を示します。 使用できる正規表現の詳細は、 Oniguruma 正規表現を参照してください。 mregexpで使用している正規表現の種類はRuby(= ONIG_SYNTAX_DEFAULT = ONIG_SYNTAX_RUBY)です。
- カラムs1をEUC-JPとしてパターンマッチを実行します。
SELECT * FROM table_name WHERE mregexp(s1, '^あ', 'ujis');- 「あ」で始まり「お」で終わるカラムを抽出します。
SELECT * FROM table_name WHERE mregexp(s1, '^あ.*お$');- 「あいう」もしくは「かきく」を含むカラムを抽出します。
SELECT * FROM table_name WHERE mregexp(s1, '(あいう|かきく)');- 「あ」か「か」で始まるカラムを抽出します。
SELECT * FROM table_name WHERE mregexp(s1, '^[あか]');- 数字を含むカラムを抽出します。
SELECT * FROM table_name WHERE mregexp(s1, '\\d');
\dではなく\\dである点に注意してください。- 大文字小文字を区別して「abc」を含むカラムを抽出します。
SELECT * FROM table_name WHERE mregexp(s1, 'abc');SELECT * FROM table_name WHERE mregexp(s1, '(?-i)abc');- 大文字小文字を区別しないで「abc」を含むカラムを抽出します。
SELECT * FROM table_name WHERE mregexp(s1, '(?i)abc');
注意点
- カラムの型がCHARの場合、カラム値の末尾の連続したスペース(0x20)は無視してパターンマッチングを行います。
つまり、CHAR(5)の値'
abcd'(末尾にスペースが1つある)は、 パターン'd$'にマッチしますが、'd $'や'd\\s$'にはマッチしません。
理由は次の通りです。
UDFに渡されるVARCHAR型の文字列はその最大長になるように末尾にスペースが詰められているため、固定長のCHAR型でスペースが詰められているのと区別できません。 独断で、CHAR型を固定長として使うよりVARCHAR型を使う頻度が高いと判断し、このような実装にしました。 - 扱える正規表現はOnigurumaのRuby型(ONIG_SYNTAX_RUBY)です。
- MySQL 4.1.1では、初期化関数(mregexp_init)でエラーが発生するとmysqldが再起動してしまいます。 これはmysqldのバグ(Bug #2697)で、4.1.2で修正されました。
ダウンロード
| name | version | date | size |
|---|---|---|---|
| mregexp-1.0.tar.gz | 1.0 | 2006-09-29 18:25:04 | 13.6 KB |
インストール方法
必要なもの
- MySQLの、開発者用ファイル
(libmysqlとヘッダファイル。rpm版だとMySQL-develに含まれています)
MySQLのバージョンは、4.0、4.1、5.0で動作確認をしています。 - Oniguruma 4.X系
インストール
以降ではUNIX likeな環境でのインストール方法を示します。
Windowsの場合は、VC++でのインストール方法が、MySQL ML
[mysql 09205] <http://www.mysql.gr.jp/mysqlml/mysql/msg/9205>
と
[mysql 09206] <http://www.mysql.gr.jp/mysqlml/mysql/msg/9206>
とで報告されているのでそちらを参照してください。
また、VC2005 Expressを使った場合のコンパイル方法が、
『超・極める!MySQL
Makefileの
INSTALL_LIBDIRとDEFAULT_ENCODINGの値を適宜変更してください。
INSTALL_LIBDIRはUDFのオブジェクトファイルを配置するディレクトリ
(以下、ここで指定したディレクトリをINSTALL_LIBDIRと表記します)
です。
DEFAULT_ENCODINGで指定したものは、
mregexp関数の実行時に、mregexpの引数で文字エンコーディングが指定されなかった場合の初期値となります。my.cnfの[mysqld]セクションのdefault-character-setと同じ文字エンコーディングにするといいでしょう。
MakefileにはShift_JIS, EUC-JP, UTF8の例がありますが、これ以外の文字エンコーディング(EUC-KRなど)をここで指定したい場合は、
OnigurumaのAPIにあるONIG_ENCODING_XXXを指定してください。
Makefileを変更したら、コンパイルしてrootになってインストールします。
$ make # make install
環境変数LD_LIBRARY_PATHにINSTALL_LIBDIRを指定してmysqldを起動し直します。
MySQL AB提供のrpmパッケージを使っている場合は、mysqldの起動スクリプト/etc/init.d/mysqlを以下のように変更してから/etc/init.d/mysql restartと実行して起動し直せばいいです。
--- /etc/init.d/mysql 2004/04/12 08:40:57 1.1
+++ /etc/init.d/mysql 2004/04/12 08:47:27
@@ -151,6 +151,7 @@
then
# Give extra arguments to mysqld with the my.cnf file. This script may
# be overwritten at next upgrade.
+ env LD_LIBRARY_PATH=/usr/local/lib/mysql \
$bindir/mysqld_safe --datadir=$datadir --pid-file=$pid_file >/dev/null 2>&1 &
# Make lock for RedHat / SuSE
if test -w /var/lock/subsys
mysqldが起動したら、mysqlのユーザーrootで接続し、以下のSQL文を発行します。
CREATE FUNCTION mregexp RETURNS INTEGER SONAME 'mregexp.so'; CREATE FUNCTION mregexp_version RETURNS STRING SONAME 'mregexp.so';
以上でmregexp関数が使えるようになったと思います。
必須ではありませんが、次のようにして動作確認を行うことができます。
- MySQL 4.0 系で
default-character-set = ujisの場合 make test_ujis- MySQL 4.0 系で
default-character-set = sjisの場合 make test_sjis- MySQL 4.1 系、5.0系の場合
make test41
動作確認のために、
デフォルトではmysql -s testでデータベースtestに接続し、
DROP TABLE, CREATE TABLE, INSERT, SELECTを発行します。
接続ユーザーなどを変更したい場合は、make実行時にT_DOSQLの値を指定することで制御できます。
例えば、ユーザーscott、パスワードtiger、データベースempに接続して動作確認する場合は、
make T_DOSQL='mysql -s -uscott -ptiger emp' test_ujis
と実行します。
性能について
目的
組み込みのREGEXPやLIKEと比べ、mregexpがどの程度の性能となるかを調べてみます。
一般的に、性能評価は非常に難しく、ハードウエアやmysqldのパラメータなどに大きく左右されます。ここでは絶対的な評価ではなく、ある環境の中でのmregexpの傾向を見ることを主眼とします。
環境
- ハードウエア
-
- Intel Celeron 2.00GHz
- メモリ 512MB
- ソフトウエア
-
- Linux, kernel-2.6.5, glibc-2.2.5
- MySQL-4.0.18-log
- Oniguruma-2.2.6
- gcc-2.95.4
テーブルは以下のような単純なものです。
CREATE TABLE `t_perm` ( `c1` varchar(12) default NULL, KEY `idx_c1` (`c1`) ) TYPE=InnoDB
データは177,409行格納されています。 参考までに、このデータは青空文庫で公開されている芥川龍之介の 『文芸的な、余りに文芸的な』 『偸盗』 『羅生門』 『路上』 『素戔嗚尊』 をMeCab (和布蕪)で字句解析した結果の文字列です。
結果
テストしたクエリは以下の通りです。
select * from t_perm where c1 like 'タ%';select * from t_perm where c1 regexp '^タ';select * from t_perm where mregexp(c1,'^タ');select * from t_perm where mregexp(c1,'^タ.テ');select * from t_perm where c1 like 'タ%' and mregexp(c1,'^タ.テ');
クエリが完了するまでに要した時間は以下の通りでした。 1回目はmysqldが起動して初めて発行した場合で、2回目は1回目に続けて同じクエリを発行した場合です。
| クエリ番号 | 1回目[sec] | 2回目[sec] |
|---|---|---|
| 1 | 0.09 | 0.06 |
| 2 | 0.84 | 0.06 |
| 3 | 1.09 | 0.86 |
| 4 | 1.07 | 1.01 |
| 5 | 0.06 | 0.06 |
前出のクエリに対してEXPLAINを実行した結果のうち、type、possible_keys、rowsのみを抜き出したものは以下の通りになりました。
| クエリ番号 | type | possible_keys | rows |
|---|---|---|---|
| 1 | range | idx_c1 | 10459 |
| 2 | index | NULL | 176937 |
| 3 | index | NULL | 176937 |
| 4 | index | NULL | 176937 |
| 5 | range | idx_c1 | 10450 |
考察
まず、クエリ3のEXPLAINの結果を見ると、mregexpでは前方一致の正規表現であってもインデックスが使われていないことがわかります。
ではREGEXPはどうかというと、クエリ2のEXPLAINの結果の通り、たとえ組み込み関数で前方一致の正規表現であってもインデックスは使用されません。
しかし、クエリ2と3で1回目と2回目の所要時間を見ると、クエリ2では2回目の所要時間が大幅に短くなっているのがわかります。これはクエリのキャッシュが効いているためで、試しにmysqldのquery_cache_sizeを極端に小さくして試してみると、2回目でも1回目と同程度の所要時間になったことからも裏付けられます。
一方、クエリ3では、2回目は多少所要時間が短くなっていますが、クエリ2ほど大幅なものではありません。推測するに、UDFを使ったクエリの結果はキャッシュされないのではないでしょうか。
この推測が正しいと、UDFであるmregexpは前方一致であってもインデックスが使われないし結果のキャッシュもされないことになり、性能が非常に悪いということになってしまいます。
そこでmregexpで処理するレコード数を少なくすることによって、所要時間にどのような変化があらわれるか見てみました。これがクエリ4と5です。
クエリ5はLIKEとmregexpを使っているのでクエリ4に比べてコストが高いように見えますが、所要時間を比べてみると圧倒的に二度手間のクエリ5の方が速いという結果になりました。
これは、まず最初にLIKEが適用され、クエリ4に比べてmregexpが処理するレコードが大幅に少なくなったためだと思います。また、この場合でのLIKEではインデックスが適用されるのでLIKEの処理は短い時間で完了します。
以上より、mregexpはLIKEやREGEXPに比べると性能面では劣りますが、LIKEとmregexpを組み合わせて使うことで高い性能が得られることがわかりました。
正規表現を使いたい全ての局面でLIKEと組み合わせられるわけではありませんが、クエリのチューニングの際にはこのようなケースも考慮すべきだと思います。
変更履歴
mregexp
- 2006-09-29
- version 1.0をリリース。
- 『超・極める!MySQL
- 『超・極める!MySQL
- 2004-08-25
- version 0.3をリリース。
- args->lengths[i]ではなくてstrlen(args->args[i])を使っていたのを修正。(Thanks to Yuzo Kimpara)
- 2004-04-13
- version 0.2をリリース。
- 2004-04-07
- 日本のMySQL MLに初版を投稿。
このページ
- 2006-09-29
- 全体的に書き直しました。
- 2004-08-25
- 開発環境の情報を更新。
- 2004-04-18
- Windows(VC++)でのインストール方法へのリンクを追加。
- 2004-04-13
- 公開。
目次
関連リンク
- MySQL への新しい関数の追加 [英語] [日本語]
- 鬼車 (Oniguruma)
他コンテンツ
- home
- はじめに
- 書き物類
- はじめに
- スケーラブルWebシステム工房
- 現場指向のレプリケーション詳説 - mysql
- USBをシリアルコンソールに
- Debian woodyをkernel 2.6にするメモ
- kernelイメージから設定情報を取り出す
- AWStatsの検索文字列の文字化けを解消
- 現場指向のレプリケーション詳説 - mysql
- コード
- mregexp - MySQLで日本語の正規表現を扱う
- kcode - 各種文字コードを表示する
