MySQL 全文搜索 '+this +week' 未按预期工作

Question

我正在尝试对我的数据库使用全文搜索来加快结果的速度，但它似乎无法正常工作:

例如，下面的查询没有返回结果

select * FROM data WHERE MATCH (name) AGAINST ('+this +week' IN BOOLEAN MODE)

当这个返回结果时:

select * FROM data WHERE name like '%this%week%'

我做错了什么？

编辑:有很多行的名称以 'this week' 开头。

Answer 1

这两个表达式不是等价的:

• 全文功能搜索单词，而LIKE带有%特殊字符基本上搜索字段的任何部分

• 另一个区别是全文搜索允许任何顺序的单词，而 LIKE 要求单词遵循模式中给定的顺序

当使用全文搜索时，MySQL 会故意忽略一个预定义的常用词列表，称为 stopwords :正如您所怀疑的，this 就是其中之一.这可能是阻止您的值(value)匹配的原因。您需要从搜索中删除该词。参见 this link获取 MySQL 全文停用词列表(或仅查询 INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD)。

---

考虑以下数据集:

select * from mytable;

| id  | name                    |
| --- | ----------------------- |
| 1   | this happened last week |
| 2   | last week this happened |
| 3   | thishappenedlastweek    |
| 4   | this happens this week  |

使用 LIKE 过滤时:

select * from mytable where name like '%this%week%';

| id  | name                    |
| --- | ----------------------- |
| 1   | this happened last week |
| 3   | thishappenedlastweek    |
| 4   | this happens this week  |

使用全文搜索和停用词 'this'，不会返回任何内容:

select *
FROM mytable
WHERE MATCH (name) AGAINST ('+this +week' IN BOOLEAN MODE);

There are no results to be displayed.

这在删除停用词时会起作用:

select *
FROM mytable
WHERE MATCH (name) AGAINST ('+week' IN BOOLEAN MODE);

| id  | name                    |
| --- | ----------------------- |
| 1   | this happened last week |
| 2   | last week this happened |
| 4   | this happens this week  |

不包括停用词的组合也有效:

select *
FROM mytable
WHERE MATCH (name) AGAINST ('+happened +week' IN BOOLEAN MODE);

| id  | name                    |
| --- | ----------------------- |
| 1   | this happened last week |
| 2   | last week this happened |

Demo on DB Fiddle .