MySQL，PHP，搜索多个单词 [英] Mysql, PHP, searching for multiple words

问题描述

我正在尝试在表格中搜索特定单词.

I'm trying to search a table for specific words.

说我有一个单词列表:打印机，网络，无线，紧急

Say I have a list of words: printer,network,wireless,urgent

我只想返回所有包含这些单词的行.

I only want to return those rows where all of these words are in it.

SELECT * FROM tickets WHERE concat(subject,body) REGEXP "printer|network|wireless|urgent" 

将返回带有这些单词之一的任何行.我如何做到这一点，以便它只返回其中所有这些单词都位于其中的那些行.

will return any row with any one of these words. How can I make it so that it will only return those rows where all of these words are in it.

谢谢

推荐答案

有两种方法可以做到这一点.第一种是相当明显的方法.假设您有所有需要显示在名为$ necessaryWords的数组中的单词:

There are two ways to do this. The first is the rather obvious approach. Let's say you have all the words that need to appear in an array called $necessaryWords:

$sql = 'SELECT ... FROM ...'; // and so on
$sql .= ' WHERE 1';

foreach ($necessaryWords as $word)
    $sql .= ' AND concat(subject,body) LIKE "%' . $word . '%"'; //Quotes around string

但是，使用%foo%的速度相当慢，因为无法使用索引，因此此查询可能会导致表很大和/或需要的单词过多而导致性能问题.

However, using %foo% is rather slow, as no indexes can be used, so this query might cause performance issues with huge tables and/or a high number of necessary words.

另一种方法是在subject和body上使用FULLTEXT索引.您可以像这样使用全文MATCH IN BOOLEAN MODE:

The other approach would be a FULLTEXT index on subject and body. You could the use the fulltext MATCH IN BOOLEAN MODE like this:

$sql = 'SELECT ... FROM ...'; // and so on
$sql .= ' WHERE MATCH(subject,body) AGAINST("';

foreach ($necessaryWords as $word)
    $sql .= ' +' . $word;
$sql .= '")';

请注意，您的表必须使用MyISAM才能使用FULLTEXT索引.更新:手动.

Note that your table must use MyISAM in order to use FULLTEXT indexes. UPDATE: As of MySQL 5.6, InnoDB supports FULLTEXT indexes as well. I guess this could be the better choice performance wise. Further documentation on the fulltext in boolean mode can be found in the manual.

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