MySQL - 如何使用 LIKE 搜索精确的单词匹配？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5743177/
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 - How to search for exact word match using LIKE?
提问by Mike
I'm using this query to select data:
我正在使用此查询来选择数据：
mysql_query("SELECT * FROM products WHERE product_name LIKE '%".$search."%'");
The only problem is, that it sometimes selects more, than I would like.
唯一的问题是，它有时会选择比我想要的更多的东西。
For example, I would like to select product "BLA", but my query select product "BLABLA" as well. To be clear, if i wanted to select "Product 1", I don't want the query to select "Product 11".
例如，我想选择产品“BLA”，但我的查询也选择产品“BLABLA”。明确地说，如果我想选择“产品 1”，我不希望查询选择“产品 11”。
Does anybody know how to manage that?
有人知道如何管理吗？
Thanks.
谢谢。
回答by James C
Do you just want to search on word boundaries? If so a crude version might be:
您只想搜索单词边界吗？如果是这样，一个粗略的版本可能是：
SELECT * FROM products WHERE product_name LIKE "% foo %";
Or you could be a bit cleverer and look for word boundaries with the following REGEXP
或者你可以更聪明一点，用以下方法寻找单词边界 REGEXP
SELECT * FROM products WHERE product_name RLIKE "[[:<:]]foo[[:>:]]";
回答by PulpDood
Found this question on Google, so I figure that some people may still stumble upon this so here's my pretty inelegant attempt:
在谷歌上发现了这个问题，所以我想有些人可能仍然会偶然发现这个问题，所以这是我非常不雅的尝试：
SELECT * FROM products
WHERE product_name LIKE 'BLA %' #First word proceeded by more words
OR WHERE product_name LIKE '% BLA' #Last word preceded by other words
OR WHERE product_name LIKE '% BLA %' #Word in between other words
OR WHERE product_name = 'BLA'; #Just the word itself
Not sure about the efficiency or if this covers all cases, so feel free to downvote if this is really inefficient or too inelegant.
不确定效率或者这是否涵盖所有情况，所以如果这真的效率低下或太不优雅，请随意投票。
回答by Quassnoi
SELECT *
FROM products
WHERE product_name = 'BLA'
will select exact BLA
将选择准确 BLA
SELECT *
FROM products
WHERE product_name LIKE 'BLA%'
will select BLADDERand BLACKBERRYbut not REBLAND
将选择BLADDER和BLACKBERRY但不REBLAND
To select BLAas the first word of the string, use:
要选择BLA作为字符串的第一个单词，请使用：
SELECT *
FROM products
WHERE product_name RLIKE '^Bla[[:>::]]'
AND product_name LIKE 'Bla%'
The second condition may improve your query performance if you have an index on product_name.
如果您在 上有索引，则第二个条件可能会提高您的查询性能product_name。
回答by Faisal
Try using regular expressions:
尝试使用正则表达式：
SELECT
*
FROM
`products`
WHERE
product_name regexp '(^|[[:space:]])BLA([[:space:]]|$)';
回答by Shakti Singh
Remove LIKEkeyword and use =for exact match
删除LIKE关键字并=用于完全匹配
EDIT
编辑
do not forgot to escape user input using mysql_real_escape_stringotherwise your query will fail if some one enter quotes inside the input box.
不要忘记使用mysql_real_escape_string转义用户输入，否则如果有人在输入框中输入引号，您的查询将失败。
$search=mysql_real_escape_string($search);
mysql_query("SELECT * FROM products WHERE product_name='".$search."'");
回答by wimvds
Then don't use LIKE, but search for equality.
然后不要使用 LIKE，而是搜索相等性。
ie.
IE。
mysql_query("SELECT * FROM products WHERE product_name = '".$search."'");
BTW I hope you sanitize/escape $search before using it in a query.
顺便说一句，我希望您在查询中使用 $search 之前对其进行消毒/转义。
回答by anothershrubery
Use equals (=)?
使用等号 (=)？
mysql_query("SELECT * FROM products WHERE product_name = '".$search."'");
If you are looking to match EXACT words don't use LIKE.
如果您要匹配 EXACT 单词，请不要使用LIKE.
EDIT: That clears things up a bit then. Just add a space after the search term. Or even add the hyphen (-) if that is always in the search term.
编辑：那把事情搞清楚了一点。只需在搜索词后添加一个空格。或者甚至添加连字符 (-)，如果它总是在搜索词中。
mysql_query("SELECT * FROM products WHERE product_name LIKE '".$search." -%'");
回答by Rahul Dhiman
you can use select query like this ,i also use in cakePHPand it's helpful.
你可以像这样使用选择查询，我也在cakePHP 中使用它，它很有帮助。
Select * from `users` where username COLLATE latin1_general_cs LIKE '%$email%'
回答by Shuvo
If you want to search exact word matching from MySql using LIKEthen you use:
如果要使用 MySql 搜索精确的单词匹配，LIKE则使用：
SELECT * FROM tableName WHERE columnName LIKE 'your_query' ;
回答by Pankaj katiyar
try to use regular expression in query
尝试在查询中使用正则表达式
mysql_query("SELECT * FROM products WHERE product_name regexp '".$search."'");
