mysql表名 多个单词,MySQL，PHP，搜索多个单词

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.

Thanks,

解决方案

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

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.

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 .= '")';

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.