憂鬱なプログラマの形而上学

さくらのレンタルサーバーでMySQLの全文検索

MySQL

最近、さくらのレンタルサーバーでMySQLの全文検索システムを作ってみた。しかもスタンダートプランで（＾＾；

WEBプログラムを作っていると、たいていの人は一度は通る全文検索ですが、私の場合、以下のページなどを参考にいつもはMySQL+Sennaで実装します。（たぶん、MySQLで全文検索といったらこれが一番、普及しているような気がします）

• MySQLで全文検索 - FULLTEXTインデックスの基礎知識｜blog｜たたみラボ
• MySQL FULLTEXT + Ngram : LIKE検索より数十倍高速な、お手軽 日本語全文検索 について｜blog｜たたみラボ

ですが、今回はレンタルサーバーなので、MySQLはSSHクライアントから使えるのですが、当然rootはもっていませんし、Sennaをインストールしたり、my.cnf（MySQLのconfファイル）を書き換えたり、といったことも当然できません。FULLTEXTインデックスは使えるので、FULLTEXTインデックスを張って、2文字ずつに区切った文字列をNgramで検索させようとしました。

/* 検索用のテーブル */
CREATE TABLE mytest (
  id INT NOT NULL , 
  keywords TEXT  , 
  PRIMARY KEY (id),
  FULLTEXT (keywords) 
) ENGINE=MyIsam DEFAULT CHARSET=utf8;

/* ほげらっちょの場合 */
INSERT INTO mytest VALUES (1 , 'ほげ げら らっ っち ちょ');

/* abcの場合 */
INSERT INTO mytest VALUES (1 , 'ab bc');

/* サンタクロースの場合 */
INSERT INTO mytest VALUES (1 , 'サン ンタ タク クロ ロー ース');

/* ほげらっちょを含むレコードを検索 (正確には「ほげ」「げら」「らっ」「っち」「ちょ」を含むレコード) */
SELECT * FROM mytest WHERE MATCH( keywords ) AGAINST('+ほげ +げら +らっ +っち +ちょ');

しかし、困ったことにMySQLのデフォルトの設定では、FULLTEXTインデックスの検索において、4文字以下の検索語はすべて無視されてしまいます。

これを変更したい場合、/etc/my.cnfの以下の設定を変え、mysqldを再起動する必要があります。

[mysqld]
ft_min_word_len=4

ですが、これもroot権限が必要なので、できません。

もちろん、4文字以下の検索はできません、と仕様として割り切ってしまえば楽なのですが、今回はそういうわけにもいかなかったので、最終的にはすべて文字列を16進法に変換して検索することにしました。

今回は、Ngramのテーブルは検索用で、実際のデータはすべて別のテーブルに入っていたので（正規化といいます）見た目の設計もあまり複雑になることなく実装できたかなと思います。

/* ほげらっちょの場合 */
INSERT INTO mytest VALUES (1 , 'e381bbe38192 e38192e38289 e38289e381a3 e381a3e381a1 e381a1e38287');

/* abcの場合 */
INSERT INTO mytest VALUES (1 , '6162 6263');

/* サンタクロースの場合 (e382b5 e383b3 e382bf e382af e383ad e383bc e382b9) */
INSERT INTO mytest VALUES (1 , 'e382b5e383b3 e383b3e382bf e382bfe382af e382afe383ad e383ade383bc e383bce382b9');

/* ほげらっちょを含むレコードを検索 (正確には「ほげ」「げら」「らっ」「っち」「ちょ」を含むレコード) */
SELECT * FROM mytest WHERE MATCH( keywords ) AGAINST('+e381bbe38192 +e38192e38289 +e38289e381a3 +e381a3e381a1 +e381a1e38287');

今回、上記のようにNgramでutf8の文字２文字ずつに区切って16進法に変換する関数をPHPで作ってみました。

今回の場合、検索時に「カタカナ」<->「ひらがな」、「全角」<->「半角」の区別なくヒットさせるために、mb_convert_kana()である程度、文字の差分を補正しています。何を作りたいかによりますが、余分であれば、この部分は削除してもいいかなと思います。

<?php
function utf8_ngram($str,$prefix="") {
	/*
	a  	全角英数字を半角英数字に変換する★
	A 	半角英数字を全角英数字に変換する
	c 	全角カタカナを全角ひらがなに変換する★
	C 	全角ひらがなを全角カタカナに変換する
	k 	全角カタカナを半角カタカナに変換する
	K 	半角カタカナを全角カタカナに変換する★
	h 	全角ひらがなを半角カタカナに変換する
	H 	半角カタカナを全角ひらがなに変換する
	n 	全角数字を半角数字に変換する
	N 	半角数字を全角数字に変換する
	r 	全角英文字を半角英文字に変換する
	R 	半角英文字を全角英文字に変換する
	s 	全角スペースを半角スペースに変換する (U+3000 → U+0020) ★
	S 	半角スペースを全角スペースに変換する (U+0020 → U+3000)
	V 	濁点つきの文字を１文字に変換する (K、H と共に利用する）★
	*/
	$str = mb_convert_kana($str, "acHsV", "utf-8");

	$res = "";
	$s = unpack('C*', $str);
	$len = count($s);
	$s1 = $s2 = "";
	for ($i=1;$i<=$len;$i++) {
		$c1 = $c2 = $c3 = 0;
		$s2 = $s1;
		$s1 = "";
		if (($c1 = $s[$i]) > 0x80) {
			if ($c1 <= 0xDF) {
				$s1 = bin2hex(chr($c1).chr($s[++$i]));
			} else {
				$s1 = bin2hex(chr($c1).chr($s[++$i]).chr($s[++$i]));
			}
		} else {
			if ($c1 <= 0x20) continue;	// 空白
			
			$s1 = bin2hex(chr($c1));
		}
		if ($s1 && $s2) {
			$res .= $prefix.$s2.$s1." ";
		}
	}
	return rtrim($res);
}

$keywords = utf8_ngram("ほげらっちょ");	// INSERT時
$keywords = utf8_ngram("ほげらっちょ","+"); // 検索時
?>

今回はrootを持っていないレンタルサーバーで上記のようなことを行ったのですが、本来なら、500円/月のレンタルサーバーでそんな凝ったことするなよ・・・というのが正直なところかなと思いますが、やりようによっては安価なサーバーでもいろいろできるという一つの例としてみて頂けるとありがたいかなと思います（＾＾；

ツイートする

Permalink | コメント(0) | トラックバック(0) | 16:35