MySQLで全文検索1
私は、MySQLをほとんど使わないのであまり考えたこともないのですが、MySQLの全文検索はブランクやカンマで区切られた単語単位でしかインデックスしてくれないので、単語の区切れのない日本語ではほぼ使えません。
そこで nGram でカットするファンクションを作ってみました。
nGramにカッとしたりするのは外側の言語でやれば良い。という考え方もあるでしょうが、私は RDBMS で完結するべきと考えています。
アプリ側に任せてしまうと、例えば、PHPで作ったモジュールを Javaで呼ばないと行けなくなったり、Javaのプログラムに書き直したりということが起こり得ます。
RDBMSを引っ越すよりも、複数言語を扱うことの方が圧倒的に確率は高いですし、SQL側で行った方がパフォーマンスも良くなります。
nGramにカットするのは、確かに外側の言語で行った方が RDBMS の負荷は小さくなりますが、基本的には SQL を使いきる方が RDBMS(サーバ）の負荷は小さくなる。
これよく、ウソをついているプロが多すぎて、私がいい加減なことを言っているように思われることすらあるけれど、プロならウソをついたらアカンで。
やるとしたら、巨大になれば、インデックスだけの別DB（RDBMS）でやる（つまり、Googleですな）こともあり得ますけれど、データと正規化のプログラムは一体化している方が管理上のメリットはあるでしょう。
では実際のソース。
ストアドファンクションを作る
半角カナや異字体を正規化する Normalize ファンクションと、2文字毎にカットする nGram ファンクションを作ります。
DROP FUNCTION IF EXISTS Normalize;
DELIMITER //
CREATE FUNCTION Normalize(pText TEXT)
RETURNS TEXT
DETERMINISTIC
BEGIN
DECLARE vLen INT; -- 変換対象の長さ
DECLARE vPos INT; -- 変換対象の文字位置
DECLARE vChar1 VARCHAR(2); -- 半角カタカナ（入力）の１文字取り出し
DECLARE vChar2 VARCHAR(2); -- 濁音、半濁音操作のため半角カタカナ（入力）の１文字取り出し
DECLARE vLstPos INT; -- リスト中の変換候補の位置
DECLARE vMChar VARCHAR(2); -- 全角カタカナの１文字
DECLARE vBefor VARCHAR(255); -- 変換元
DECLARE vAfter VARCHAR(255); -- 変換先
DECLARE vRet TEXT; -- 全角カタカナ（出力）
IF pText is null THEN
RETURN null;
END IF;
SET vLen = CHAR_LENGTH(pText);
IF vLen = 0 THEN
RETURN '';
END IF;
SET vRet = '';
SET vPos = 1;
WHILE vPos <= vLen DO
SET vChar1 = SUBSTR(pText, vPos, 1);
IF vChar1 < '｡' OR vChar1 > 'ﾟ' THEN -- 半角カタカナ以外の時
-- 禁則文字 ※ 編集してください。
SET vBefor = '!"#$%&()=-~^\|@`{}*:+;_?/.>,<！ ”＃＄％＆（）＝−￣＾￥｜＠‘｛｝＊：＋；＿？／．＞，＜…♪';
SET vLstPos = INSTR(vBefor, vChar1);
IF vLstPos > 0 THEN
SET vRet = CONCAT(vRet, ' '); -- ブランクに置き換え
ELSE
-- 異体字 ※ 編集してください ここはパフォーマンス上、直書きの方が良いと思う
SET vBefor = '齊斎齋�癲嶋嶌';
SET vAfter = '斉斉斉高島島';
SET vLstPos = INSTR(vBefor, vChar1);
SET vMChar = SUBSTR(vAfter, vLstPos, 1);
IF vLstPos = 0 THEN
SET vRet = CONCAT(vRet, vChar1); -- 変換不要
ELSE
SET vRet = CONCAT(vRet, vMChar);
END IF;
END IF;
ELSE -- 半角カタカナか？
SET vChar2 = SUBSTR(pText, vPos + 1, 1); -- 濁音、半濁音のチェック
CASE
WHEN (vChar1 = 'ｳ'
OR vChar1 BETWEEN 'ｶ' AND 'ﾄ'
OR vChar1 BETWEEN 'ﾊ' AND 'ﾎ')
AND vChar2 = 'ﾞ' THEN
SET vLstPos = FIELD(CONCAT(vChar1,vChar2)
, 'ｳﾞ'
, 'ｶﾞ', 'ｷﾞ', 'ｸﾞ', 'ｹﾞ', 'ｺﾞ'
, 'ｻﾞ', 'ｼﾞ', 'ｽﾞ', 'ｾﾞ', 'ｿﾞ'
, 'ﾀﾞ', 'ﾁﾞ', 'ﾂﾞ', 'ﾃﾞ', 'ﾄﾞ'
, 'ﾊﾞ', 'ﾋﾞ', 'ﾌﾞ', 'ﾍﾞ', 'ﾎﾞ' );
SET vMChar = ELT(vLstPos -- vLstPos = 0 ならば、vMChar = ''
, 'ヴ'
, 'ガ', 'ギ', 'グ', 'ゲ', 'ゴ'
, 'ザ', 'ジ', 'ズ', 'ゼ', 'ゾ'
, 'ダ', 'デ', 'ヅ', 'デ', 'ド'
, 'バ', 'ビ', 'ブ', 'ベ', 'ボ' );
SET vRet = CONCAT(vRet, vMChar); -- リスト中にあり、全角変換
SET vPos = vPos + 1; -- 濁点分はチェック済みなのでカウントアップ
WHEN (vChar1 between 'ﾊ' AND 'ﾎ')
AND vChar2 = 'ﾟ' THEN
SET vLstPos = FIELD(CONCAT(vChar1,vChar2)
, 'ﾊﾟ', 'ﾋﾟ', 'ﾌﾟ', 'ﾍﾟ', 'ﾎﾟ');
SET vMChar = ELT(vLstPos -- vLstPos = 0 ならば、vMChar = ''
, 'パ', 'ピ', 'プ', 'ペ', 'ポ');
SET vRet = CONCAT(vRet, vMChar); -- リスト中にあり、全角変換
SET vPos = vPos + 1; -- 濁点分はチェック済みなのでカウントアップ
ELSE
SET vMChar = '';
END CASE;
-- 濁音、半濁音じゃなかった
IF vMChar ='' THEN
SET vBefor = '｡｢｣､･ｦｧｨｩｪｫｬｭｮｯｰｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜﾝﾞﾟ';
SET vAfter = ' ヲァィゥェォャュョッーアイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワン'; -- ゛゜'; ノーマライズじゃなければ、濁点半濁点にするべきか？……。
SET vLstPos = INSTR(vBefor, vChar1);
SET vMChar = SUBSTR(vAfter, vLstPos, 1);
SET vRet = CONCAT(vRet, vMChar); -- vLstPos = 0 ならば、vMChar = '' なので濁点半濁点はカットされる
END IF;
END IF;
SET vPos = vPos + 1;
END WHILE;
RETURN vRet;
END;
//
DROP FUNCTION IF EXISTS nGram;
DELIMITER //
CREATE FUNCTION nGram(pText TEXT)
RETURNS TEXT
DETERMINISTIC
BEGIN
DECLARE vLen INT; -- 変換対象の長さ
DECLARE vPos INT; -- 変換対象の文字位置
DECLARE vWrk TEXT; -- 全角カタカナ（出力）
DECLARE vRet TEXT; -- 全角カタカナ（出力）
DECLARE vChar2 VARCHAR(2); -- 半角カタカナ（入力）の１文字取り出し
DECLARE vMChar VARCHAR(2); -- 切られた2文字
-- DECLARE vBefor VARCHAR(255); -- 変換元
-- DECLARE vAfter VARCHAR(255); -- 変換先
IF pText is null THEN
RETURN null;
END IF;
SET vWrk = TRIM(Normalize(pText));
SET vLen = CHAR_LENGTH(vWrk);
IF vLen = 0 THEN
RETURN '';
END IF;
SET vRet = '';
SET vPos = 1;
WHILE vPos < vLen DO
SET vMChar = SUBSTR(vWrk, vPos, 2);
IF SUBSTR(vMChar, 1, 1) != ' ' AND SUBSTR(vMChar, 2, 1) != ' ' THEN
SET vRet = CONCAT(vRet, ' ', vMChar);
END IF;
SET vPos = vPos + 1;
END WHILE;
RETURN TRIM(vRet);
END;
//
長くなったので、使い方は明日書きます。
