fts测试

select COUNT(*) from mgg2;
select COUNT(*) from mgg;
select COUNT(*) from tableOld;

select * FROM mgg2 where content match '%cat%';

select * FROM mgg where overview like '%cat%';

select * FROM tableOld where content like '%cat%';

create virtual table mgg2 using fts4(id PRIMARY KEY AUTO_INCREMENT, title text,content text);
insert into mgg2 select id,title,content from data;

ALTER TABLE data RENAME TO tableOld;
CREATE TABLE mgg(id INTEGER PRIMARY KEY AUTOINCREMENT, title text,overview text);
SELECT id,title,content FROM tableOld;
SELECT id,title,overview FROM mgg;
INSERT INTO mgg(id,title,overview) SELECT id,title,content FROM tableOld;


select * FROM mgg2 where content match 'cat';

select * FROM mgg2 where content match 'cat';

select * FROM mgg2 WHERE content match 'content:cat OR title:cat';

select * FROM mgg2 WHERE content match 'content:cat AND title:cat';

select * FROM mgg2 WHERE content match 'title:cat';

select * FROM mgg2 WHERE content match 'content:cat';

图片.png
图片.png

SELECT docid,snippet(mgg, '<b>', '</b>', '...', -1, -15) FROM mgg WHERE title MATCH 'cat';
SELECT docid,snippet(mgg, '<b>', '</b>', '...', 1, 15) FROM mgg WHERE title MATCH 'cat';
SELECT docid,snippet(mgg) FROM mgg WHERE title MATCH 'cat';

https://m.baidu.com/from=0/bd_page_type=1/ssid=0/uid=0/pu=usm%401%2Csz%40320_1003%2Cta%40iphone_2_8.1_1_12.0/baiduid=7AF9B39A76B4272DFCE4EFDAF75B235D/w=40_10_/t=iphone/l=3/tc?ref=www_iphone&lid=6566359868533776500&order=2&fm=alop&tj=www_normal_2_40_10_title&vit=osres&m=8&srd=1&cltj=cloud_title&asres=1&title=Android%E5%BA%94%E7%94%A8%E7%A8%8B%E5%BA%8F%2CSQLite%2CFTS3%2C%E5%88%9B%E5%BB%BA%E7%9A%84%E8%99%9A%E6%8B%9F%E8%A1%A8%2C%E5%A6%82%E6%9E%9C%E4%B8%8D...&dict=32&wd=&eqid=5b206582a7562000100000015b4f3c3a&w_qd=IlPT2AEptyoA_yiGDUuoDyZsuR0ItJVyoDhPrP_-dP-p&tcplug=1&sec=31333&di=adc1a083bc2fcc32&bdenc=1&tch=124.235.54.379.0.0&nsrc=IlPT2AEptyoA_yixCFOxCGZb8c3JV3T5AAGGQmBRBimaj5SwfbrgHhEsRE0aNWiCGkb8gTCcqR92diGg2jQr81BBr02kgGwh6iuvxfjpgBLKHsALqdU8QwOSFiowz3C&clk_info=%7B%22srcid%22%3A1599%2C%22tplname%22%3A%22www_normal%22%2C%22t%22%3A1531919419484%2C%22xpath%22%3A%22div-a-h3%22%7D&sfOpen=1&from=singlemessage
https://blog.xojo.com/2014/03/14/full-text-search-with-sqlite/?from=singlemessage
https://www.zybuluo.com/wrlqwe/note/447795?from=singlemessage
http://stackoverflow.com/questions/20266698/android-app-sqlite-fts3-create-virtual-table-if-not-exists