MySQL & PHP : 多关键字搜索
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16034367/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL & PHP : Multiple Keyword Search
提问by Raptor
I have a string containing comma separated keywords. For Example:
我有一个包含逗号分隔关键字的字符串。例如：
$keywords = 'keyword1, keyword2, keyword3';
My Table schema, named tbl_addressis like this ( simplified ) :
我的表模式，命名tbl_address是这样的（简化）：
id INT(11) PRIMARY KEY, AUTO INCREMENT
address VARCHAR(250) NOT NULL
Assume I have to use MySQLiin PHP ( not PDO).
假设我必须MySQLi在 PHP 中使用（不是PDO）。
Here is my current approach:
这是我目前的方法：
$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.
这种方法有效，但性能低效。如果关键字很多，就会产生很多查询。
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 ?
我的问题是，有没有更好的方法来实现相同的目标？即返回地址包含任意关键字的所有记录的最简单方法是什么？
回答by Jimbo
A simple REGEXPmight be what you're after. You'd have to check how efficient it is for yourself.
一个简单的REGEXP可能就是你所追求的。你必须自己检查它的效率。
SELECT * FROM tbl_address WHERE field REGEXP 'keyword1|keyword2|keyword3';
SELECT * FROM tbl_address WHERE field REGEXP 'keyword1|keyword2|keyword3';
回答by Strawberry
SELECT * FROM user;
+---------+----------+
| user_id | username |
+---------+----------+
| 101 | Adam |
| 102 | Ben |
| 103 | Charlie |
| 104 | Dave |
+---------+----------+
SELECT *
FROM user
WHERE FIND_IN_SET(username,'adam,ben,dave') > 0;
+---------+----------+
| user_id | username |
+---------+----------+
| 101 | Adam |
| 102 | Ben |
| 104 | Dave |
+---------+----------+
回答by arraintxo
You only need an 'OR', nothing else...
你只需要一个“或”，别的什么都没有...
<?php
$result = array();
$keyword_tokens = explode(',', $keywords);
$keyword_tokens = array_map('mysqli_real_escape_string', $keyword_tokens);
$sql = "SELECT * FROM tbl_address WHERE address LIKE'%";
$sql .= implode("%' or address LIKE '%", $keyword_tokens) . "'";
// query and collect the result to $result
// before inserting to $result, check if the id exists in $result.
// if yes, skip.
return $result;
edit: Just to be sure you also trim the keywords
编辑：只是为了确保您还修剪了关键字
<?php
$result = array();
$keyword_tokens = explode(',', $keywords);
$keyword_tokens = array_map(
function($keyword) {
return mysqli_real_escape_string(trim($keyword));
},
$keyword_tokens
);
$sql = "SELECT * FROM tbl_address WHERE address LIKE'%";
$sql .= implode("%' OR address LIKE '%", $keyword_tokens) . "'";
// query and collect the result to $result
// before inserting to $result, check if the id exists in $result.
// if yes, skip.
return $result;
Also, you should also pass the db resource link to the mysqli_real_escape_string()function...
此外，您还应该将 db 资源链接传递给mysqli_real_escape_string()函数...
回答by Vaishu
Make single query
进行单一查询
$keywordary = explode(',', $keywords);
foreach($keywordary as $keyword) {
$keys = trim($keyword);
$other .=" or address like '%$keys%'";
}
$sql = "SELECT * FROM tbl_address WHERE address LIKE'%$keyword%' $other";
execute query;
return $result;
回答by B.F.
Try WHERE IN clause:
试试 WHERE IN 子句：
$keyword = (array)explode(',', $keywords);
for($i=0;$i=count($keyword);$i++){
$keyword[$i]=mysqli_real_escape_string(trim($keyword[$i]),'\'" ');
}
//This is what I suggest.
$query='SELECT * FROM tbl_address WHERE address IN ("'.implode('","',$keyword).'")';
Successfully tested on MySQL 5.1.
在 MySQL 5.1 上成功测试。
回答by Robert
The best way is to use fulltext search.
最好的方法是使用全文搜索。
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
if you don't want to use fulltext you can use OR in your WHERE condition
如果您不想使用全文，您可以在 WHERE 条件中使用 OR
SELECT * FROM tbl_address WHERE address LIKE '%$keyword%' OR adress LIKE '%$keyword2%'
回答by mukund
Best way is just create search string WHERE clause and append it to query and run it once.
最好的方法是创建搜索字符串 WHERE 子句并将其附加到查询并运行一次。
$result = array();
$keyword_tokens = explode(',', $keywords);
$where = '';$i=0
foreach($keyword_tokens as $keyword) {
$where.= " address LIKE'%".mysqli_real_escape_string(trim($keyword))."%' OR ";
}
// trim last OR with substr_replace
substr_replace($where, "OR", -1, 1);
$sql = "SELECT * FROM tbl_address WHERE $where";
return $result;
回答by Codesen
Hi create a query with union and execute in the end of the loop
您好，创建一个带有 union 的查询并在循环结束时执行
$result = array();
$keyword_tokens = explode(',', $keywords);
$sql = '';
foreach($keyword_tokens as $keyword) {
$keyword = mysqli_real_escape_string(trim($keyword));
if (!empty($sql)) $sql .= " UNION ";
$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.
}
Execute the query here.
在此处执行查询。
