MySQL查询:使用通配符进行匹配 [英] Mysql query: match against using wildcard

问题描述

我有一个名为"acts"的表，该表具有3个被索引在一起的列:

I have table named 'acts' which has 3 columns which are indexed together:

act_name, short_description, main_description

在表格中，一行有一个名为红色暴动"的行为

Within the table, one row has an act named 'red riot'

当我执行以下搜索时，结果中会出现红色暴动:

When I execute the following search, red riot appears in the results:

SELECT * from acts where MATCH(act_name, short_description, main_description) AGAINST ('*r*' IN BOOLEAN MODE)

但是，如果我对此进行扩展，然后搜索*re*，则不会返回任何结果:

Yet if I expand on that, and search for *re* it returns no results:

SELECT * from acts where MATCH(act_name, short_description, main_description) AGAINST ('*re*' IN BOOLEAN MODE)

那是为什么?有没有更好的方法可以在查询中使用通配符?

Why is that? Is there a better way to use wildcards in match against queries?

我尝试将*更改为%，但这在任何一个查询中均不返回任何结果.

I have tried changing * to % but this returns no results on either query.

推荐答案

值"re"是MATCH()搜索的停用词.

http://dev.mysql.com/doc/refman/5.5/en/fulltext-stopwords.html

"re"确实是一个停用词，但实际上不起作用的原因是，全文搜索排除了源材料中长度小于系统变量ft_min_word_len(默认值为4)的单词. ).

It's true that "re" is a stopword, but the actual reason this wasn't working was because fulltext searching excludes words in the source material whose length is less than the value of the system variable ft_min_word_len (whose default value is 4).

因此，搜索"red *"将找到包含"redder"和"reddest"而不是"red"的记录.

So searching for "red*" will find records containing "redder" and "reddest", but not "red".

http://sqlfiddle.com/#!2/16d442/1

这篇关于MySQL查询:使用通配符进行匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！