MySQL SQL 在同一字段中搜索多个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16240041/
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
SQL search multiple values in same field
提问by Nir Tzezana
I'm building a simple searchalgorithm and I want to break my string with spaces, and search my database on it, like so:
我正在构建一个简单的搜索算法，我想用空格打破我的字符串，并在其上搜索我的数据库，如下所示：
$search = "Sony TV with FullHD support";
$search = explode( ' ', $search );
SELECT name FROM Products WHERE name LIKE %$search[1]% AND name LIKE %$search[2]% LIMIT 6
Is this possible?
这可能吗？
回答by Glitch Desire
Yes, you can use SQL INoperator to search multiple absolute values:
是的，您可以使用 SQLIN运算符来搜索多个绝对值：
SELECT name FROM products WHERE name IN ( 'Value1', 'Value2', ... );
If you want to use LIKEyou will need to use ORinstead:
如果要使用LIKE，则需要OR改用：
SELECT name FROM products WHERE name LIKE '%Value1' OR name LIKE '%Value2';
Using AND(as you tried) requires ALL conditions to be true, using ORrequires at least one to be true.
Using AND(as you try) 要求所有条件都为真， usingOR要求至少有一个条件为真。
回答by codingbiz
Try this
尝试这个
Using UNION
使用 UNION
$sql = '';
$count = 0;
foreach($search as $text)
{
if($count > 0)
$sql = $sql."UNION Select name From myTable WHERE Name LIKE '%$text%'";
else
$sql = $sql."Select name From myTable WHERE Name LIKE '%$text%'";
$count++;
}
Using WHERE IN
使用 WHERE IN
$comma_separated = "('" . implode("','", $search) . "')"; // ('1','2','3')
$sql = "Select name From myTable WHERE name IN ".$comma_separated ;
回答by user2662006
This will works perfectly in both cases, one or multiple fields searching multiple words.
这在两种情况下都可以完美运行，一个或多个字段搜索多个单词。
Hope this will help someone. Thanks
希望这会帮助某人。谢谢
declare @searchTrm varchar(MAX)='one two three four';
--select value from STRING_SPLIT(@searchTrm, ' ') where trim(value)<>''
select * from Bols
WHERE EXISTS (SELECT value
FROM STRING_SPLIT(@searchTrm, ' ')
WHERE
trim(value)<>''
and(
BolNumber like '%'+ value+'%'
or UserComment like '%'+ value+'%'
or RequesterId like '%'+ value+'%' )
)
回答by Volfegan
This has been partially answered here: MySQL Like multiple values
这已在这里得到部分回答： MySQL Like multiple values
I advise against
我反对
$search = explode( ' ', $search );
$search = 爆炸（' ', $search ）;
and input them directly into the SQL query as this makes prone to SQL inject via the search bar. You will have to escape the characters first in case they try something funny like: "--; DROP TABLE name;
并将它们直接输入到 SQL 查询中，因为这很容易通过搜索栏进行 SQL 注入。如果他们尝试一些有趣的事情，您将必须首先转义字符：“-; DROP TABLE name;
$search = str_replace('"', "''", search );
$search = str_replace('"', "''", search );
But even that is not completely safe. You must try to use SQL prepared statements to be safer. Using the regular expression is much easier to build a function to prepare and create what you want.
但即使这样也不是完全安全的。您必须尝试使用 SQL 准备好的语句以更安全。使用正则表达式更容易构建一个函数来准备和创建你想要的东西。
function makeSQL_search_pattern($search) {
search_pattern = false;
//escape the special regex chars
$search = str_replace('"', "''", $search);
$search = str_replace('^', "\^", $search);
$search = str_replace('$', "\$", $search);
$search = str_replace('.', "\.", $search);
$search = str_replace('[', "\[", $search);
$search = str_replace(']', "\]", $search);
$search = str_replace('|', "\|", $search);
$search = str_replace('*', "\*", $search);
$search = str_replace('+', "\+", $search);
$search = str_replace('{', "\{", $search);
$search = str_replace('}', "\}", $search);
$search = explode(" ", $search);
for ($i = 0; $i < count($search); $i++) {
if ($i > 0 && $i < count($search) ) {
$search_pattern .= "|";
}
$search_pattern .= $search[$i];
}
return search_pattern;
}
$search_pattern = makeSQL_search_pattern($search);
$sql_query = "SELECT name FROM Products WHERE name REGEXP :search LIMIT 6"
$stmt = pdo->prepare($sql_query);
$stmt->bindParam(":search", $search_pattern, PDO::PARAM_STR);
$stmt->execute();
I have not tested this code, but this is what I would do in your case. I hope this helps.
我还没有测试过这段代码，但这就是我在你的情况下会做的。我希望这有帮助。
