sqlite - 我可以在 FTS 查询中使用 AND 语句吗？

Question

在虚拟表中的索引列上使用 AND 语句执行查询时，是否仍然可以享受 SQLite 中 FTS3/4 的优势？示例:

SELECT title, ID, creationDate FROM documents WHERE type=1 AND status=2 AND searchableFields MATCHES '"john doe"';

或者

SELECT title, ID, creationDate FROM documents WHERE type=1 AND status=2 AND searchableFields CONTAINS 'john doe';

列type和status在虚拟表中建立索引。

Answer 1

FTS 表中的所有列都是文本列，并且是 FTS 索引的； documentation说:

If column names are explicitly provided for the FTS table as part of the CREATE VIRTUAL TABLE statement, then a datatype name may be optionally specified for each column. This is pure syntactic sugar, the supplied typenames are not used by FTS or the SQLite core for any purpose.

您不应该在 FTS 表上执行类似 type=1 的搜索:

FTS tables can be queried efficiently using SELECT statements of two different forms:

• Query by rowid. If the WHERE clause of the SELECT statement contains a sub-clause of the form "rowid = ?", where ? is an SQL expression, FTS is able to retrieve the requested row directly using the equivalent of an SQLite INTEGER PRIMARY KEY index.
• Full-text query. If the WHERE clause of the SELECT statement contains a sub-clause of the form " MATCH ?", FTS is able to use the built-in full-text index to restrict the search to those documents that match the full-text query string specified as the right-hand operand of the MATCH clause.

If neither of these two query strategies can be used, all queries on FTS tables are implemented using a linear scan of the entire table. If the table contains large amounts of data, this may be an impractical approach.

您不应将 FTS 表视为表，而应将其视为索引。

如果要存储非文本数据，应该使用单独的普通表，并单独查询:

SELECT title, ID, creationDate
FROM documents
WHERE type=1
  AND status=2
  AND ID IN (SELECT docid
             FROM documents_FTS
             WHERE searchableFields MATCH '"john doe"');

为了避免存储相同的数据两次，您可以使用 contentless or external content tables .