SQLite全文检索(1)

SQLite 是一款非常轻量的嵌入型数据库，没有独立的进程，非常小的 footprint，零配置，支持事务，“public domain”开源，对于客户端程序来说已经游刃有余。[更多的介绍，System.Data.SQLite 库]

说到全文检索，目前比较流行、也比较成熟的选择是 Lucene.net。今天给大家介绍的是 SQLite 内置的全文检索功能，以如此小的 footprint 实现全文检索功能，我想还是有一定吸引力的。国内目前涉及此领域的文章还很少，.net 圈估计本文是第一篇吧，能力有限，多多包涵。

要使用 SQLite 全文检索，首先要创建 VIRTUAL TABLE：

CREATE VIRTUAL TABLE pages USING fts3(title, body);

虚表 pages 包含 title, body 两个文本字段，此外还有一个 docid 整数型的内置字段（你可以自行为 docid 赋值，也可以插入 NULL 让系统自动分配）。虚表可以和其他普通表类似的操作、连接等。

INSERT INTO pages (docid, title, body) VALUES (1, 'hello, world', '"hello, world"is my first line of data.');
UPDATE pages SET title='hello, world !' WHERE docid=1;
SELECT title, body FROM pages INNER JOIN pageinfo ON pageinfo.docid=pages.docid WHERE pageinfo.SiteName='sina';

虽然你可以写 WHERE title=’hello, world !’ 这样的查询，但这样做首先效率上比较糟糕（SQLite 将做全表扫描），而且体现不出全文检索的好处来。实际上应该是用 MATCH 运算符：

SELECT title, body FROM pages WHERE pages MATCH 'world';
SELECT title, body FROM pages WHERE title MATCH 'world';

注意这两句，前一个 MATCH 左边写了表名，后一个写的是列名。后一个仅搜索 title 列，前一个是搜索全部列（docid 列以外）。

MATCH 右侧的表达式支持模糊查询、支持指定列查询、支持 AND/OR/NEAR/NOT 等运算：

SELECT title, body FROM pages WHERE pages MATCH 'hel*';
SELECT title, body FROM pages WHERE pages MATCH 'title:hello';
SELECT title, body FROM pages WHERE pages MATCH 'hello AND world';
SELECT title, body FROM pages WHERE pages MATCH '(hello NEAR world) OR (program AND language)';

但要注意只能出现一次 MATCH 判断，WHERE title MATCH 'hello' AND body MATCH 'world' 是不行的，可以改作 WHERE pages MATCH 'title:hello AND body:world'。

我们一般都希望检索结果能给出一片段，并标出所查关键词，此时可以使用 snippet 函数：

SELECT title, snippet(pages, '<strong>', '</strong>', '...') FROM pages WHERE pages MATCH 'hello';

将会选出一两个包含检索关键词的片段，并在关键词两边用 <strong> 标记出来； '...' 将用在片段的间隔和末尾。如果略去后三个参数，则默认是以 <b> 标签标关键词。

【提示】

1. 从sqlite.org 下载的 SQLite 版本默认是不支持全文检索功能的，需要修改编译参数重新编译。不过，.net 开发者一般直接使用从 phxsoftware.com 下载的 System.Data.SQLite.dll 版本，这个版本是默认开启全文检索功能的。

2. SQLite 内置的切词器只支持西方文字，对缺乏空格分隔的东亚文字无能为力，我们需要做一些扩展让它支持东亚文字的切词。我们下一篇就来讨论这一话题。

3. 我们希望检索结果能按照相关度排序，这需要你先研究明白 offset，matchinfo 函数的用法，然后根据 SQLite 的官方文档的示例，写出自定义的相关度计算函数，然后再据此进行排序。如果有机会，我再单独写一篇这个话题的内容。