MySQL和PHP:多个关键字搜索 [英] MySQL & PHP : Multiple Keyword Search

问题描述

我有一个包含逗号分隔关键字的字符串.例如:

I have a string containing comma separated keywords. For Example:

$keywords = 'keyword1, keyword2, keyword3';

名为tbl_address的我的Table模式如下所示(简化):

My Table schema, named tbl_address is like this ( simplified ) :

id        INT(11)        PRIMARY KEY, AUTO INCREMENT
address   VARCHAR(250)   NOT NULL

假设我必须在PHP中使用MySQLi(而不是PDO).

Assume I have to use MySQLi in PHP ( not PDO ).

这是我目前的做法:

$result = array();
$keyword_tokens = explode(',', $keywords);
foreach($keyword_tokens as $keyword) {
  $keyword = mysqli_real_escape_string(trim($keyword));
  $sql = "SELECT * FROM tbl_address WHERE address LIKE'%$keyword%'";
  // query and collect the result to $result
  // before inserting to $result, check if the id exists in $result.
  // if yes, skip.
}
return $result;

此方法有效，但性能低下.如果关键字很多，它将进行很多查询.

This approach works, but inefficient in performance. If there are a lot of keywords, it will make a lot queries.

我的问题是，有没有更好的方法可以实现相同的目标?也就是说，最简单的方法是返回地址中包含任何关键字ANY的所有记录?

My question is, is there a better way to achieve the same goal ? i.e. what is the simplest way to return all records with the address containing the ANY of the keywords ?

推荐答案

一个简单的REGEXP可能就是您想要的.您必须检查它对自己的效率.

A simple REGEXP might be what you're after. You'd have to check how efficient it is for yourself.

SELECT * FROM tbl_address WHERE field REGEXP 'keyword1|keyword2|keyword3';

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