やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

📌 ？
トップ > SQLite3 > SQLite3学習　全文検索（FTS5）

SQLite3学習　全文検索（FTS5）

SQLite3

　Full Text Search 5。日本語ではまともに使えない……。

成果物

以前

FTS5を使ってみる

　ターミナルで以下コマンドを実行する。

sqlite3

　対話モードにて以下コマンドをコピペし実行。

FTS5用テーブルを作る

create virtual table posts using fts5(title, content);
  • create virtual文では、型、制約、主キーを追加するとエラーになる
  • FTSテーブルにはrowidというint型のprimary keyが存在する
  • 他にも様々なオプションがある
sqlite> .tables
posts          posts_content  posts_docsize
posts_config   posts_data     posts_idx    

sqlite> select * from sqlite_master;
table|posts|posts|0|CREATE VIRTUAL TABLE posts using fts5(title, content)
table|posts_data|posts_data|2|CREATE TABLE 'posts_data'(id INTEGER PRIMARY KEY, block BLOB)
table|posts_idx|posts_idx|3|CREATE TABLE 'posts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID
table|posts_content|posts_content|4|CREATE TABLE 'posts_content'(id INTEGER PRIMARY KEY, c0, c1)
table|posts_docsize|posts_docsize|5|CREATE TABLE 'posts_docsize'(id INTEGER PRIMARY KEY, sz BLOB)
table|posts_config|posts_config|6|CREATE TABLE 'posts_config'(k PRIMARY KEY, v) WITHOUT ROWID

　自動的に上記のようなシャドーテーブルが作成される。posts以外はアクセスすべきでない。

挿入・更新・削除

　いつもどおりinsert, update, delete文を発行する。

insert into posts values('タイトル1', '本文1');
insert into posts values('タイトル2', '本文2');
insert into posts values('たいとる3', 'ほんぶん3');
insert into posts values('English title 1', 'English content 1.');
insert into posts values('English title 2', 'English content 2.');
insert into posts values('english title 3', 'english content 3.');

select * from posts;

タイトル1|本文1
タイトル2|本文2
たいとる3|ほんぶん3
English title 1|English content 1.
English title 2|English content 2.
english title 3|english content 3.

検索する方法

where, match

select * from posts where posts match 'たいとる';

sqlite> select * from posts where posts match 'たいとる';
sqlite> select * from posts where posts match 'たいとる3';
たいとる3|ほんぶん3

　部分一致検索ができていない。原因はトークナイザが英語用であり、スペース区切りを単語として一致判定するため。日本語用のトークナイザはない。自分で実装するしかない。

　カラムを指定していないため全カラムを対象に検索している。

　英語は以下のとおり単語の一致検索ができる。大文字小文字の区別はしない。

select * from posts where posts match 'english';

English title 1|English content 1.
English title 2|English content 2.
english title 3|english content 3.

　だが、単語の一部だけではヒットしない。

select * from posts where posts match 'eng';

　正規表現*を使えばヒットした。

select * from posts where posts match 'eng*';





English title 1|English content 1.
English title 2|English content 2.
english title 3|english content 3.



### `where`, `=`





select * from posts where posts = 'ほんぶん';







sqlite> select * from posts where posts = 'ほんぶん';
sqlite> select * from posts where posts = 'ほんぶん3';
たいとる3|ほんぶん3



　完全一致検索。

　英語だと以下。単語の一部ではヒットせず。





select * from posts where posts = 'eng';



　単語に完全一致するとヒット。大文字小文字は区別しない。





select * from posts where posts = 'english';







English title 1|English content 1.
English title 2|English content 2.
english title 3|english content 3.



　正規表現`*`でもヒットする。





select * from posts where posts = 'eng*';







English title 1|English content 1.
English title 2|English content 2.
english title 3|english content 3.



### [table-valued関数](https://www.sqlite.org/vtab.html#tabfunc2)





select * from posts('たいとる3');







sqlite> select * from posts('たいとる');
sqlite> select * from posts('たいとる3');
たいとる3|ほんぶん3



　以下はヒットせず。





select * from posts('たいとる');




　英語だと以下。





select * from posts('title');







English title 1|English content 1.
English title 2|English content 2.
english title 3|english content 3.



　以下はヒットせず。





select * from posts('tit');



## ソート

　関連性の高い順に並べる。





SELECT * FROM posts WHERE posts MATCH 'english' ORDER BY rank;







SELECT * FROM posts WHERE posts MATCH 'english' ORDER BY rank;
English title 1|English content 1.
English title 2|English content 2.
english title 3|english content 3.



## `highlight()`

　`highlight()`は指定位置にある列のテキストを加工して返す。





SELECT highlight(posts, 0, '', '') FROM posts('たいとる3');







たいとる3







SELECT highlight(posts, 1, '', '') FROM posts('たいとる3');







ほんぶん3



　検索したワードに一致する部分を`<b>`,`</b>`で囲む。





SELECT highlight(posts, 1, '', '') FROM posts('english');







SELECT highlight(posts, 1, '', '') FROM posts('english');
English content 1.
English content 2.
english content 3.



# 所感

　日本語で使えないから無意味。ちょっと調べてみようか。

# 対象環境

* <time datetime="2019-08-10T08:05:55+0900" title="実施日">2019-08-10</time>
* [Raspbierry pi](https://ja.wikipedia.org/wiki/Raspberry_Pi) 3 Model B+
* [Raspbian stretch](https://ja.wikipedia.org/wiki/Raspbian) 9.0 2018-11-13
* [bash](https://ja.wikipedia.org/wiki/Bash) 4.4.12(1)-release
* [SQLite 3.29.0](http://ytyaru.hatenablog.com/entry/2021/01/26/000000)





$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux



# 前回まで

* [SQLite3学習をはじめよう](http://ytyaru.hatenablog.com/entry/2021/01/18/000000)
* [SQLite3学習　SQLiteについて](http://ytyaru.hatenablog.com/entry/2021/01/19/000000)
* [SQLite3学習　SQLiteの適切な用途](http://ytyaru.hatenablog.com/entry/2021/01/20/000000)
* [SQLite3学習　SQLiteの特徴](http://ytyaru.hatenablog.com/entry/2021/01/21/000000)
* [SQLite3学習　SQLiteのクセ](http://ytyaru.hatenablog.com/entry/2021/01/22/000000)
* [SQLite3学習　データ型とアフィニティ](http://ytyaru.hatenablog.com/entry/2021/01/23/000000)
* [SQLite3学習　演算子の一覧](http://ytyaru.hatenablog.com/entry/2021/01/24/000000)
* [SQLite3学習　よくある質問](http://ytyaru.hatenablog.com/entry/2021/01/25/000000)
* [SQLite3学習　SQLiteダウンロード＆コンパイル](http://ytyaru.hatenablog.com/entry/2021/01/26/000000)
* [SQLite3学習　Tclで操作する](http://ytyaru.hatenablog.com/entry/2021/01/27/000000)
* [SQLite3学習　ビルドオプション動作確認（SQLITE_ALLOW_URI_AUTHORITY）](http://ytyaru.hatenablog.com/entry/2021/01/28/000000)
* [SQLite3学習　面白そうなコンパイルオプション](http://ytyaru.hatenablog.com/entry/2021/01/29/000000)
* [SQLite3学習　SQLiteの拡張について](http://ytyaru.hatenablog.com/entry/2021/01/30/000000)
* [SQLite3学習　JSON拡張](http://ytyaru.hatenablog.com/entry/2021/01/31/000000)
* [SQLite3学習　JSON拡張（json_extract）](http://ytyaru.hatenablog.com/entry/2021/02/01/000000)
* [SQLite3学習　JSON拡張（json_each）](http://ytyaru.hatenablog.com/entry/2021/02/02/000000)
* [SQLite3学習　JSON拡張（json_tree オブジェクト→行）](http://ytyaru.hatenablog.com/entry/2021/02/03/000000)
* [SQLite3学習　JSON拡張（json_tree オブジェクトツリー→行）](http://ytyaru.hatenablog.com/entry/2021/02/04/000000)
* [SQLite3学習　JSON拡張（json_tree オブジェクト配列→行）](http://ytyaru.hatenablog.com/entry/2021/02/05/000000)
* [SQLite3学習　JSON拡張（json_group_array 行→配列）](http://ytyaru.hatenablog.com/entry/2021/02/06/000000)
* [SQLite3学習　JSON拡張（json_group_object 行→オブジェクト）](http://ytyaru.hatenablog.com/entry/2021/02/07/000000)
* [SQLite3学習　JSON拡張（json_array_length）](http://ytyaru.hatenablog.com/entry/2021/02/08/000000)
* [SQLite3学習　JSON拡張（json_type）](http://ytyaru.hatenablog.com/entry/2021/02/09/000000)
* [SQLite3学習　JSON拡張（json_valid）](http://ytyaru.hatenablog.com/entry/2021/02/10/000000)
* [SQLite3学習　JSON拡張（json_quote）](http://ytyaru.hatenablog.com/entry/2021/02/11/000000)
* [SQLite3学習　JSON拡張（json_array）](http://ytyaru.hatenablog.com/entry/2021/02/12/000000)
* [SQLite3学習　JSON拡張（json_object）](http://ytyaru.hatenablog.com/entry/2021/02/13/000000)
* [SQLite3学習　JSON拡張（json_patch）](http://ytyaru.hatenablog.com/entry/2021/02/14/000000)
* [SQLite3学習　JSON拡張（json_insert）](http://ytyaru.hatenablog.com/entry/2021/02/15/000000)
* [SQLite3学習　JSON拡張（json_replace）](http://ytyaru.hatenablog.com/entry/2021/02/16/000000)
* [SQLite3学習　JSON拡張（json_set）](http://ytyaru.hatenablog.com/entry/2021/02/17/000000)
* [SQLite3学習　JSON拡張（json_remove）](http://ytyaru.hatenablog.com/entry/2021/02/18/000000)