ご存知の方も多いと思います。sqlite3には全文検索（フルテキスト検索）が備わっています。
とは言っても、テーブルを生成する際に呪文を唱えておく必要があるので、今あるテーブルをそのまま全文検索の対象にするのはできません。
CREATE VIRTUAL TABLE tbl USING fts3(content TEXT);
元テーブルをselectして、新しいVIRTUAL FT3/FT4テーブルを作ってインサートするだけでコピーできるので、悲観することはないです。INSERTはとっても速いですしね！
フルテキストにはバージョンがあったりします
先ほどの呪文を唱える際、USING fts3と記載しています。
fts3がバージョンを意味しています。
FTS1,2は非推奨、現在使ってはいけないレベルの代物です。
FTS3とFTS4が現在使えるバージョンで、SQLiteのマニュアルではFTS4を推奨しています。
FTS3とFTS4の違いは、マニュアルによると「FTS3 and FTS4 are nearly identical.」、ほぼ一緒らしいです。
FTS4は、 SQLite version 3.7.4 から利用可能になっています。
フルテキストクエリのパフォーマンスが向上し、追加オプションにも対応しているので、FTS4を利用するようにしておいた方が安心です。
FTS4の唯一のデメリットは、追加情報によってディスク容量が増える可能性があるところです。
この辺りは、事前の評価で許容範囲かどうかの確認ポイントですね！
そして、最新はFTS5です。
SQLite 3.9.0から使えるようになっています。コンパイル時の指定はOFFになっています。
手順を踏んでコンパイルする必要があります。
ここではせっかくなので、FTS3,FTS4,FTS5の３パターンで謎を究明してみます。
全文検索の謎
sqlite3の全文検索を使い始めてみると一致する単語なのに見つからないってことがよくあります。
くせがあるというんでしょうか、よっぽどLIKE ‘%単語%’の方が確実です。
でも、全文検索（フルテキスト検索）は、チョー高速です。使ってみるとわかると思います。
速度に関してはストレスフリーです。
なので、全文検索のくせをつかもうと調査しました。
SQLite3の全文検索は、sqlite3.dllやsqlite3.lib(so)などをコンパイルする際に指定する”コンパイルオプション”で挙動が変わります。
ですので、ご自身の環境を知っておくことも重要です。
CREATE VIRTUAL TABLE tbl USING fts3(id,content)
insert into tbl (id, content) values (1, '単語 文章 を スペース 区切り で 挿入 します');
insert into tbl (id, content) values (2, 'The most useful thing about FTS tables is the queries that may be performed using the built-in full-text index.');
フルテキスト検索はmatchを利用します。
-
select id from tbl where content match '単語'
-
select id from tbl where content match '%語'
%ではなく、＊を使います。
select id from tbl where content match '単*'
後方検索はOKです。
select id from tbl where content match '*語'
前方検索は❌NGです。
- 単語にマッチしないと見つかりません。
select id from tbl where content match 'table';
tablesはありますが、tableはないため見つかりません。
select id from tbl where content match 'table*';
で見つかります。
- 大文字小文字は標準では区別されない感じです。
select id from tbl where content match 'THE'
select id from tbl where content match 'the'
どちらも2が見つかります。
- 複数の単語を探す場合はスペースで区切って指定します。
select id from tbl where content match 'スペース 単語'
select id from tbl where content match 'fts most'
- matchは1つのみ利用できます。複数指定するとエラーになります。
select id from tbl where content match 'fts most' and id match '1';
Runtime error: unable to use function MATCH in the requested context
コメント