如何使多列mysql全文搜索的部分单词匹配 [英] How to make a multiple column mysql fulltext search where partial words are matched

问题描述

  $ searchArray = explode（， $ searchVal）; 
 $ query =SELECT * FROM users WHERE; 
 $ i = 0; 
 foreach（$ searchArray as $ word）{
 if（$ i！= 0）$ query。=OR; 
 $ query。=MATCH（`first_name`，`last_name`，`email`）AGAINST（'。$ word。*'IN BOOLEAN MODE）; 
 $ i ++; 
 
 
 
 
 
 
假设我在表格中有这两行：
  id | last_name | first_name |电子邮件
 1 |史密斯|约翰| [e-mail] 
 2 |史密斯| Bob | [e-mail] 
  
如果我输入John S，只有第一个结果显示哪个是如果我输入John Smith，只有第一个结果显示哪一个是所需的行为。
 
 
 
如果我输入Smith J，即使Bob不匹配，两个结果都会显示。
 
 
 
如果输入Smith John尽管Bob不匹配。
 
 
 
最后，如果我输入Jo S，尽管部分匹配Jo和S，但没有结果返回。 / p> 
 
 
任何人都可以帮我修复我的查询，以处理顺序的所需功能不重要，部分结果匹配吗？如果它可以按照最佳匹配进行排序（即单词的最长部分，从第一个字母开始，而不是中间部分，最高列数），这也是一个巨大的帮助。
 
 
 
 更新： 
 
 
 
只想发布基于解决方案的最终代码。我的循环创建多个匹配语句不正确，因为我的ft_min_word_len。
 
 
 
我的代码现在是： 
 
 
  $ searchArray = explode（，$ searchVal）; 
 $ query =SELECT * FROM users WHERE MATCH（`first_name`，`last_name`，`email`）AGAINST（'; 
 $ i = 0; 
 foreach（$ searchArray as 
 $ query。=+。$ word。*; 
} 
 $ query。='IN BOOLEAN MODE）; 
  
 
 
解决方案
在布尔模式下，需要字符串存在只是得分更高），用 + 完成。前缀匹配以结尾 * 完成。这似乎是你想要的，所以搜索：
  + John * + S * 
 + John * + Smith * 
 + Smith * + J * 
 + Jo * + S * 
  
请注意，全文索引无法帮助您搜索单词中的任何位置。所以像 * mith * 这样的东西肯定会失败：它们意味着要与索引中的字符1匹配。
 
 
 
如果你还想按照匹配值排序，例如，在  Johnny Smithson 之前需要 John Smith  / code>，你会这样做：
  SELECT * FROM用户
 WHERE MATCH（.. （匹配BOOLEAN模式）
 ORDER BY MATCH（.. fields ..）AGAINST（'匹配'BOOLEAN模式）DESC; 
  
除非添加所有单词> =  ft_min_word_len 再分开： 
 
 
  + John * + S * John 
 + John * + Smith * John Smith 
 + Smith * + J * Smith 
 + Jo * + S * 
  
最后一个是<默认的4个字符，所以我们不能为默认的mysql添加排序参数，但是您可以设置 ft_min_world_len 不同的值。
 
I currently have a single search field searching against multiple columns using this code:
$searchArray = explode(" ", $searchVal);
$query="SELECT * FROM users WHERE ";
$i=0;
foreach ($searchArray as $word) {
    if ($i != 0) $query .= " OR ";
    $query .= " MATCH (`first_name`, `last_name`, `email`) AGAINST ('".$word."*'  IN BOOLEAN MODE)";
    $i++;
}
Lets say I have these two rows in the table:
id | last_name | first_name | email
1  | Smith     | John       | [e-mail]
2  | Smith     | Bob        | [e-mail]
If I type in "John S", only the first result shows which is the desired behavior.

If I type in "John Smith", only the first result shows which is the desired behavior.

If I type "Smith J", both results show even though Bob is not a match.

If I type "Smith John", both results show even though Bob is not a match.

Lastly, if I type "Jo S", no results are returned despite the partial match on "Jo" and "S".

Can anyone help me fix my query to deal with the desired functionality of the order not being important and partial results matching? If it can be sorted by the best matches (i.e. the longest part of the word, starting from the first letter only not a section in the middle, in the highest number of columns), that would be a huge help also.

UPDATE:

Just wanted to post the final code that worked based on the solution. My loop creating multiple match statements was incorrect as was my ft_min_word_len.

My code is now:
$searchArray = explode(" ", $searchVal);
$query="SELECT * FROM users WHERE  MATCH (`first_name`, `last_name`, `email`) AGAINST ('";
$i=0;
foreach ($searchArray as $word) {
    $query .= "+".$word."* ";
}
$query .= "' IN BOOLEAN MODE)";

解决方案
In boolean mode, requiring strings to be present (instead of just scoring higher), is done with +. prefix matching is done with an ending *. This seems to be what you want, so search for:
+John* +S*
+John* +Smith*
+Smith* +J*
+Jo* +S*
Note that Full Text indexes cannot help you searching 'anywhere in a word'. so something like *mith* is bound to fail: they're meant to match from character 1 in an index.

If you also want to order them by match values, and for instance, need John Smith before Johnny Smithson, you'd do this:
 SELECT * FROM user 
 WHERE MATCH(..fields..) AGAINST ('match' IN BOOLEAN MODE)
 ORDER BY MATCH(..fields..) AGAINST ('match' IN BOOLEAN MODE) DESC;
Which you will see will get you nowhere unless you add all the words >= ft_min_word_len again separately:
+John* +S* John
+John* +Smith* John Smith
+Smith* +J* Smith
+Jo* +S*
For the last one, both are < the default 4 characters, so we can't add sorting params for that in default mysql, but you could set ft_min_world_len differently is desired.

                        
这篇关于如何使多列mysql全文搜索的部分单词匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！