本文介绍了如何从 FTS3 表搜索中排除列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这样的桌子:
CREATE VIRTUAL TABLE t USING FTS3(hidden, text1, text2)
我希望用户能够搜索 'text1' 和 'text2' 列,因此查询是
I would like user to be able to searh over 'text1' and 'text2' columns, so the query is
SELECT docid FROM t WHERE t MATCH ?
可能的请求是:
SELECT docid FROM t WHERE t MATCH 'foo'
SELECT docid FROM t WHERE t MATCH 'text1:foo OR text2:bar'
问:如何从搜索中排除隐藏"列,以便用户无法通过隐藏值找到行?
Q: how can I exclude 'hidden' column from search, so that user can't find rows by hidden value?
我将使用隐藏"列来引用带有附加信息的辅助表中的行.
I am going to use 'hidden' column to refer to rows in secondary table with additional information.
推荐答案
FTS3 表获得一个免费的 64 位整数列,称为 docid,该列未编入索引.只需将附加数据放在一个单独的表中,其中该表的主键与 FTS3 表的 docid 相同.
An FTS3 table gets a free 64 bit integer column called docid which is not indexed. Simply put additional data in a separate table where the Primary Key for that table is the same as the docid for the FTS3 table.
CREATE VIRTUAL TABLE t USING FTS3(text1, text2);
CREATE TABLE additional (id INTEGER PRIMARY KEY, hidden VARCHAR);
INSERT INTO t (docid, text1, text2) VALUES (243, 'foo', 'bar');
INSERT INTO additional VALUES (243, 'bas');
SELECT docid, text1, text2, hidden FROM t JOIN additional ON t.docid = additional.id WHERE t MATCH 'foo';
这篇关于如何从 FTS3 表搜索中排除列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!