全文检索

在检索大量数据时，普通的数据检索查询条件处理不当时，会检索全部的记录数，导致耗费一定的时间，除了优化查询语句、分页检索等方式提高效率外，全文检索可以让查询速度变得很快。

全文检索简介

全文检索的简称是FTS(full text search), 在sqlite里面的话，我们有FTS3和FTS4可以使用。FTS其实就是创建一张虚拟表以供查询；它的一个很重要的作用就是可以让查询速度变得很快。根据官方统计数据统计显示，下面是两种查询的速度比较：

创建两种表

1 2	CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT); /* FTS3 table / CREATE TABLE enrondata2(content TEXT); / Ordinary table */

查询速度比较

1 2	SELECT count() FROM enrondata1 WHERE content MATCH 'linux'; / 0.03 seconds / SELECT count() FROM enrondata2 WHERE content LIKE '%linux%'; /* 22.5 seconds */

全文检索使用

创建虚表

对于已经存在数据记录的数据表，可以创建虚表，然后复制这些记录到虚表中，然后对虚表进行查询，而非查询原数据表

// 创建虚表vir_table，字段可以不带类型
CREATE VIRTUAL TABLE IF NOT EXISTS vir_table USING FTS3(column1, column2...);

// 拷贝原表记录
INSERT INTO vir_table SELECT * FROM table

// 删除虚表
DROP TABLE IF EXISTS vir_table

虚表增删改查

虚表的基本操作与普通表的操作是一样的

// 增
INSERT INTO vir_table (column1, column2...) VALUES ('column1', 'column2'...);

// 删
DELETE FROM vir_table WHERE column1='column1';

// 改
UPDATE vir_table SET column1='column' WHERE column1='column1';

// 查
SELECT * FROM vir_table WHERE column1 like 'column1%' and column2 like 'column2%';

虽然可以使用普通表的查询，但是效率上非常糟糕（SQLite将做全表扫描），体现不出全文检索的好处来，虚表的检索使用的是 MATCH 运算符：

1 2	SELECT column1, column2 FROM vir_table WHERE column1 MATCH 'column1'; SELECT column1, column2 FROM vir_table WHERE column2 MATCH 'column2';

MATCH 右侧的表达式支持模糊查询（类似like）、之处指定列查询、支持AND/OR/NEAR/NOT等运算，模糊查询使用的是*，不再是%，下面是MATCH的几种写法：

SELECT column1, column2 FROM vir_table WHERE vir_table MATCH 'col*';
SELECT column1, column2 FROM vir_table WHERE vir_table MATCH 'column1:col*';
SELECT column1, column2 FROM vir_table WHERE vir_table MATCH 'column1 AND column2';
SELECT column1, column2 FROM vir_table WHERE vir_table MATCH '(column1 NEAR column3) OR (column2 AND column4)';

注意：

一条sql语句里只能出现一次MATCH判断，WHERE column1 MATCH 'column1' AND column2 MATCH 'column2'是不行的，可以改成WHERE vir_table MATCH 'column1:col* AND column2:col*'。
MATCH的模糊查询不能是这种形式：'*col*'，只能模糊右边：col*或者不使用通配符：col。
全文检索会导致虚表增加，更新和删除记录变慢，需要考虑实际情况进行利弊权衡，对于查询操作量级较大，可以考虑全文检索。

触发器

对虚表的增删改查不会影响到原普通表的数据记录，为了保证虚表、原普通表数据一致，所以要对原普通表进行修改，在对虚表增删改的同时，对原普通表同时进行增删改。对于多个地方修改虚表和原普通表，同时操作两个表比较麻烦，因此可以考虑触发器，即在普通表中建立触发器 （虚表中不可以建触发器） ，当虚表操作时，触发器就会触发，自动对普通表进行增删改。

创建虚表的同时，创建触发器：

// 创建插入触发器
CREATE TRIGGER IF NOT EXISTS tri_insert AFTER INSERT ON vir_table BEGIN INSERT INTO table(column1, column2) VALUES(NEW.column1, NEW.column2); END

// 创建删除触发器
CREATE TRIGGER IF NOT EXISTS tri_delete AFTER DELETE ON vir_table BEGIN DELETE FROM table WHERE column1 = OLD.column1; END

// 创建修改触发器
CREATE TRIGGER IF NOT EXISTS tri_update AFTER UPDATE ON vir_table BEGIN UPDATE table SET column1 = NEW.column1 WHERE column2 = NEW.column2; END

// 触发器的删除
DROP TRIGGER IF EXISTS tri_insert

参考

SQLite全文检索(1)

SQLite全文检索(2)