使用 mysql MATCH AGAINST 搜索多个单词 [英] search for multiple words using mysql MATCH AGAINST

问题描述

我正在使用这个非常有效的基本 mySQL 查询:

I am using this basic mySQL query which works great:

$sql = "SELECT * FROM `clients` WHERE 
        MATCH(`LNAME`) AGAINST('$c')  OR
        MATCH(`FNAME`) AGAINST('$c')  OR
        MATCH(`MAIL`) AGAINST('$c')  OR
        MATCH(`TEL`) AGAINST('$c')  " 

其中 $c 是搜索查询.现在这适用于所有单个单词/数字，但每当我添加 2 个单词时，不会返回任何结果.

where $c is the search query. Now this works with all single words/numbers but whenever I add 2 words no results are returned.

例如，如果我的数据库在 LNAME 中有 aaaa bbbb 并且我搜索aaaa bbbb"我什么也得不到，但是当我搜索aaaa"或bbbb"时它确实有效.我尝试添加 IN BOOLEAN MODE 但它没有任何区别.

For example, if my database has aaaa bbbb in LNAME and I search for "aaaa bbbb" I get nothing back, however when I search for "aaaa" or "bbbb" it does work. I tried adding IN BOOLEAN MODE but it doesn't make a difference.

有人可以向我解释这是如何工作的吗?$c 由字母、数字和/或@

Could ayone explain to me how this works? $c is composed of letters, numbers and/or a @

非常感谢.

推荐答案

首先，你应该像这样使用 MATCH AGAINST:

First , you should use MATCH AGAINST like this:

$sql = "SELECT * FROM `clients` WHERE MATCH(`LNAME`,`FNAME`,`MAIL`,`TEL`) AGAINST('$c')"

请注意:

短词将被忽略，默认最小长度为 4 个字符.您可以使用变量更改最小和最大字长ft_min_word_len 和 ft_max_word_len

Short words are ignored, the default minimum length is 4 characters. You can change the min and max word length with the variables ft_min_word_len and ft_max_word_len

和:

如果一个词出现在超过 50% 的行中，它将有一个权重为零.这在大型数据集上具有优势，但可以使小测试困难.

If a word is present in more than 50% of the rows it will have a weight of zero. This has advantages on large datasets, but can make testing difficult on small ones.

你可以使用LIKE，它可能会有更好的结果.用法示例:

You can use LIKE and it probably will have better results. Example of usage:

$sql = "SELECT * FROM `clients` WHERE `LNAME` LIKE '%$c%' OR `FNAME` LIKE '%$c%' OR ..."

这篇关于使用 mysql MATCH AGAINST 搜索多个单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！