KtJ Dragon

1. テーブルのデータベースエンジンをMyISAMにする

FULLTEXTインデックスはMyISAMデータベースエンジン特有の機能なので、テーブルがInnoDB等MyISAM以外のエンジンを使っている場合は変換する必要があります。クラッシュ耐性やトランザクションの実装等、InnoDBの方が推奨される場合が多いのですが。

(2021/2/23追記) マニュアルによれば少なくとも現在はInnoDBもFULLTEXTインデックス対応とのこと。(追記終わり)

2. my.cnfを設定

デフォルトでは4字未満の単語はヒットしない設定となっているようですが、それだと日本語環境で不便なので、 [mysqld] セクションに ft_min_word_len=1 を追加して1文字から検索できるようにします。設定したらmysqlを再起動します。

(2021/2/23追記)InnoDBの場合は ft_min_word の代わりに innodb_ft_min_token_size を使用する。(追記終わり)

3. テーブルにインデックスをつける

CREATE FULLTEXT INDEX インデックス名 ON テーブル名(列名)

でインデックスを作成します。

(以下、2018/11/3以降追記)

4. 検索

SELECT * FROM テーブル名 WHERE MATCH ( 列名 ) AGAINST ( 'キーワード' )

AND検索とかするならBOOLEAN MODEの方がよさそう

SELECT * FROM テーブル名 WHERE MATCH ( 列名 ) AGAINST ( '+キーワード1 +キーワード2' IN BOOLEAN MODE )

(2021/2/21追記)MeCabで分かち書きを行った場合、例えば「ブリティッシュ」が「ブリ ティッシュ」のように分割されてしまうため、キーワードの方も前処理としてMeCabで分かち書きにした上で split() メソッドで単語ごとに分けたほうが良さそう。(追記終わり)

(2021/2/23追記)

単に検索語を分かち書きに変換しただけだと、例えば検索キーワードに「ブリティッシュ」を入れた場合に、「ブリ」と「ティッシュ」が連続していないテキストもヒットしてしまう。あと欧文のフレーズ検索もできるとなおよし。ということでちょっと変更。Pythonのshlexビルトインモジュールを使うと、ダブルクォーテーションで囲まれた部分はフレーズとして分割してくれるとのことなので、これを利用して下記のようなルーチンを組んでみた。フレーズは引用符で囲むことでフレーズとして検索できる。

import pymysql, MeCab, shlex

#フォームから取得した文字列を、引用符を考慮して空白で分割
#例えば「This is "a pen"」なら['This', 'is', 'a pen']という3要素のリストに変換される
try:
  search_keywords = shlex.split(request.forms.getunicode('search_keywords'))
  errorflag = False
except ValueError:
  #キーワードの引用符の使い方がおかしい(例えば引用符一つで検索した場合)と
  #エラーになるのでトラップしておき、errorflagに基づいてエラーメッセージ
  #表示などを行う
  search_keywords = []
  errorflag = True

if len(search_keywords)>0:
  #分割したキーワードのそれぞれをMeCabで分かち書きにした上で、
  #ダブルクォーテーションで囲い、頭に+を付け、スペース区切りで連結。
  #元々のキーワードにダブルクォーテーションが含まれていた場合はエスケープする
  m = MeCab.Tagger("-Owakati")
  parsed_keywords = ""
  for var in search_keywords:
    parsed_keywords.append('+"' + m.parse(var).replace('"', '\\"') + '" ')
  parsed_keyword = parsed_keyword[0:-1] #末尾のスペースを取る

  #検索
  con = pymysql.connect(
    host='データベースのIPアドレス',
    port=データベースのポート,
    db='データベースのスキーム名',
    user='ユーザ名',
    passwd='パスワード',
    charset='utf8mb4'
  )
  cur = con.cursor

  sql = "SELECT * FROM テーブル名 WHERE MATCH( 列名 ) AGAINST ( %s IN BOOLEAN MODE )"
  cur.execute(sql, (parsed_keywords,))
  result = cur.fetchall()
  cur.close()
  con.close()

(追記終わり)

5. インデックスの再構築

例えばft_min_word_lenを変更した場合などは、インデックスの再構築を以下のコマンドで行う。

REPAIR TABLE テーブル名 QUICK;

(2021/2/23追記)

InnoDBの場合は以下のコマンドでインデックスの再構築を行う。

set GLOBAL innodb_optimize_fulltext_only=ON;
OPTIMIZE TABLE opening_lines;

(追記終わり)