SQLite全文本搜索查询使用连字符-SQLiteFullTextSearchQueriesWithHyphens

I have been using SQLite (3) for a small website. Recently, I have discovered SQLite's full text search (FTS) capability and leveraged it for a simple search feature. However, a user inadvertently discovered that a hyphen ('-') in the search string does the wrong thing. It seems to indicate that the token immediately following should be excluded. In fact, when I change the hyphen to a plus or a space, it does work.

我一直在一个小网站上使用SQLite(3)。最近，我发现了SQLite的全文搜索(FTS)功能，并将其用于一个简单的搜索功能。然而，用户无意中发现搜索字符串中的连字符('-')做错了事情。它似乎表明，紧接着的令牌应该被排除在外。事实上，当我把连字符换成加号或空格时，它确实有效。

My questions: 1) Am I correct in this analysis? I read the SQLite documentation regarding the FTS feature and found no discussion about this. 2) How should I mitigate this? Manually replace hyphens before passing them to SQLite?

我的问题是:1)我的分析正确吗?我阅读了关于FTS特性的SQLite文档，没有发现对此的讨论。2)我该如何缓解这个问题?在将连字符传递给SQLite之前手动替换它们?

A small, concrete example of what I'm seeing:

我看到的一个具体的小例子是:

sqlite> CREATE VIRTUAL TABLE fts_table USING fts4
   ...> ( content TEXT );

sqlite> INSERT INTO fts_table VALUES ("Title: F-1 Race (Game Boy)");
sqlite> INSERT INTO fts_table VALUES ("Title: F-Zero (SNES)");
sqlite> INSERT INTO fts_table VALUES ("Title: F-15 Strike Eagle II (Genesis)");

sqlite> SELECT * FROM fts_table;
Title: F-1 Race (Game Boy)
Title: F-Zero (SNES)
Title: F-15 Strike Eagle II (Genesis)

(This database is related to old video games, as you might have guessed.)

(你可能已经猜到了，这个数据库与旧的电子游戏有关。)

So the website takes the search string from the user and plugs it into a SELECT statement using the MATCH operator. For the search string 'f-zero', the relevant SQL becomes:

因此，网站从用户那里获取搜索字符串，并使用MATCH操作符将其插入到SELECT语句中。对于搜索字符串f- 0，相关的SQL为:

sqlite> SELECT * FROM fts_table WHERE content MATCH 'f-zero';
Title: F-1 Race (Game Boy)
Title: F-15 Strike Eagle II (Genesis)

I.e., it doesn't match the title 'F-Zero'. However, the string 'f+zero' returns the right thing:

即。它与f - 0的标题不匹配。但是，字符串'f+ 0 '返回的是正确的:

sqlite> SELECT * FROM fts_table WHERE content MATCH 'f+zero';
Title: F-Zero (SNES)

Again, I suppose I could substitute '+' or a space for '-' before sending the string to SQLite, but that doesn't really feel like the right solution.

同样，我想在将字符串发送到SQLite之前，我可以将'+'或空格替换为'-'，但这并不是正确的解决方案。

1 个解决方案

The NOT operator (or, if using the standard syntax, a unary "-" operator)

-- Query for all documents that contain the term "database", but do not contain
-- the term "sqlite". Document 1 is the only document that matches this criteria.
SELECT * FROM docs WHERE docs MATCH 'database NOT sqlite';

SELECT * FROM docs WHERE docs MATCH 'database -sqlite';

SELECT * FROM fts_table WHERE content MATCH '"f-zero"';