在设计后台的时候，需要管理blog当中的静态图片，于是就有了这样的需求：

  • 根据图片名检索数据库，获取图片的使用情况

搜索了一番，找到了如下网页 http://www.sqlite.org/fts3.html 。 sqlite也能实现全文检索。官网介绍了fts3，fts4插件，需求的版本应分别高于3.5.0和3.7.4。而fts1，fts2因为年久失修不被推荐使用。据介绍，ft4显著快于ft4，但可能会多占用1~2%的磁盘空间。

如何使用？

-- 不指定Column，将自动创建一个Column为content的fts3表，当然还有rowid:
CREATE VIRTUAL TABLE data USING fts3();

-- 传入一组逗号隔开的列表创建Column:
CREATE VIRTUAL TABLE pages USING fts4(title, keywords, body);

-- 更加详细的建表方式，但会忽略所有约束
CREATE VIRTUAL TABLE mail USING fts3(
  subject VARCHAR(256) NOT NULL,
  body TEXT CHECK(length(body)<10240)
);
-- 使用完成删除表
DROP TABLE data;

优化

为了支持全文检索，fts会维护一个反向索引（inverted index）映射了分词及其在表中的位置。详细内容参考（http://www.sqlite.org/fts3.html#data_structures）。在查询的时候，使用"INSERT INTO () VALUES('optimize')"这样的语法来加快后续检索速度，但它是一个开销很大的操作。

-- 优化data表的查询结构.
INSERT INTO data(data) VALUES('optimize');

在SELECT查询中，注意几点可以提高查询速度

  • 使用MATCH取代=，MATCH会利用到内置的全文索引，速度更快
  • 使用rowid查询，会直接使用整数主键索引，速度更快
  • 除了以上两种情况，其他查询方式都会进行全表搜索，1.5GB大概需要30s完成
-- The examples in this block assume the following FTS table:
CREATE VIRTUAL TABLE mail USING fts3(subject, body);

SELECT * FROM mail WHERE rowid = 15;                -- Fast. Rowid lookup.
SELECT * FROM mail WHERE body MATCH 'sqlite';       -- Fast. Full-text query.
SELECT * FROM mail WHERE mail MATCH 'search';       -- Fast. Full-text query.
SELECT * FROM mail WHERE rowid BETWEEN 15 AND 20;   -- Slow. Linear scan.
SELECT * FROM mail WHERE subject = 'database';      -- Slow. Linear scan.
SELECT * FROM mail WHERE subject MATCH 'database';  -- Fast. Full-text query

查询语法(我的实现)

DROP TABLE IF EXISTS [docs];

CREATE VIRTUAL TABLE [docs] USING [fts4](
    uuid, 
    content);

INSERT INTO [docs]
    ([docs])
    VALUES ('optimize');

INSERT INTO [docs]
    ([uuid], 
    [content])
    SELECT [Article].[uuid], 
       [Article].[content]
FROM   [Article];

SELECT [uuid], 
       SNIPPET ([docs], '<b style="color:red";>', '</b>', '...', 1, 10) AS [content]
FROM   [docs]
WHERE  [docs] MATCH '"20170124165204*"';

结果如下：...img alt="" src="/static/upload/<b style="color:red";>20170124165204</b>.jpeg" style="height:614px...

如果你使用最新的fts5搜索，它还多了一个highlight()函数，功能类似snippet()，参考地址。

错误no such module: fts4

在Python部署的时候遇到了no such module: fts4的错误，而我在使用SQLite Expert的时候并没有如上状况。

  • windows 在http://www.sqlite.org/download.html 下载对应版本的sqlite.dll复制替换到python27/DLLs/文件夹
  • linux 重新编译sqlite 加上-DSQLITE_ENABLE_FTS4 flag