SQLite FTS 示例不起作用

Question

我已经下载了最新的 SQLite 3.7.15.2 shell (Win32)，并尝试完全按照 http://sqlite.org/fts3.html#section_3 中的说明执行 FTS 示例之一。

-- Virtual table declaration
CREATE VIRTUAL TABLE docs USING fts3();

-- Virtual table data
INSERT INTO docs(docid, content) VALUES(1, 'a database is a software system');
INSERT INTO docs(docid, content) VALUES(2, 'sqlite is a software system');
INSERT INTO docs(docid, content) VALUES(3, 'sqlite is a database');

-- Return the set of documents that contain the term "sqlite", and the
-- term "database". This query will return the document with docid 3 only.
SELECT * FROM docs WHERE docs MATCH 'sqlite AND database';

但尽管最后一条评论 SELECT 结果是空集。这是 SQLite 中的错误还是只是过时的文档？ (正确的语法是什么？)。

对我来说最重要的是查询

SELECT * FROM docs WHERE docs MATCH '(database OR sqlite) NEAR/5 system';

也不起作用，我的应用程序中需要这种类型的查询。还有其他方法可以编写它吗？

Answer 1

文档中的示例使用 enhanced query syntax 。检查 PRAGMAcompile_options; 是否包含 ENABLE_FTS3_PARENTHESIS。

您的 NEAR 查询不起作用并不是编译选项的问题:

> SELECT * FROM docs WHERE docs MATCH '(database OR sqlite) NEAR/5 system';
Error: malformed MATCH expression: [(database OR sqlite) NEAR/5 system]

问题是，根据文档，NEAR 仅适用于基本搜索表达式:

A NEAR query is specified by putting the keyword "NEAR" between two phrase, term or prefix queries.

因此您必须相应地重写搜索表达式:

> SELECT * FROM docs WHERE docs MATCH '(database NEAR/5 system) OR (sqlite NEAR/5 system)';
a database is a software system
sqlite is a software system