MySQL - 如何使用LIKE搜索精确的单词匹配?

时间:2022-09-19 18:54:09

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.

谢谢。

10 个解决方案

#1


32  

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[[:>:]]";

#2


6  

SELECT  *
FROM    products
WHERE   product_name = 'BLA'

will select exact BLA

将选择确切的BLA

SELECT  *
FROM    products
WHERE   product_name LIKE 'BLA%'

will select BLADDER and BLACKBERRY but not REBLAND

将选择BLADDER和BLACKBERRY而不是REBLAND

To select BLA as 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上有索引,则第二个条件可能会提高您的查询性能。

#3


3  

Try using regular expressions:

尝试使用正则表达式:

SELECT 
    *
FROM
    `products`
WHERE
    product_name regexp '(^|[[:space:]])BLA([[:space:]]|$)';

#4


2  

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.

不确定效率或是否涵盖所有情况,所以如果效率非常低或太优雅,请随意投票。

#5


0  

Then don't use LIKE, but search for equality.

然后不要使用LIKE,而是搜索相等。

ie.

即。

mysql_query("SELECT * FROM products WHERE product_name = '".$search."'");

BTW I hope you sanitize/escape $search before using it in a query.

顺便说一下,我希望你在查询中使用它之前清理/转义$ search。

#6


0  

To exact match you should use:

要完全匹配,您应该使用:

mysql_query("SELECT * FROM products WHERE product_name = '" . $search . "'");

#7


0  

Remove LIKE keyword and use = for exact match

删除LIKE关键字并使用=进行完全匹配

EDIT

编辑

do not forgot to escape user input using mysql_real_escape_string otherwise 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."'");

#8


0  

Use equals (=)?

使用equals(=)?

mysql_query("SELECT * FROM products WHERE product_name = '".$search."'"); 

If you are looking to match EXACT words don't use LIKE.

如果你想匹配精确的单词不要使用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." -%'"); 

#9


0  

try to use regular expression in query

尝试在查询中使用正则表达式

mysql_query("SELECT * FROM products WHERE product_name regexp '".$search."'");

#10


0  

you can use select query like this ,i also use in cakePHP and it's helpful.

你可以使用像这样的选择查询,我也在cakePHP中使用它,这很有帮助。

Select * from `users` where username COLLATE latin1_general_cs LIKE '%$email%'

#1


32  

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[[:>:]]";

#2


6  

SELECT  *
FROM    products
WHERE   product_name = 'BLA'

will select exact BLA

将选择确切的BLA

SELECT  *
FROM    products
WHERE   product_name LIKE 'BLA%'

will select BLADDER and BLACKBERRY but not REBLAND

将选择BLADDER和BLACKBERRY而不是REBLAND

To select BLA as 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上有索引,则第二个条件可能会提高您的查询性能。

#3


3  

Try using regular expressions:

尝试使用正则表达式:

SELECT 
    *
FROM
    `products`
WHERE
    product_name regexp '(^|[[:space:]])BLA([[:space:]]|$)';

#4


2  

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.

不确定效率或是否涵盖所有情况,所以如果效率非常低或太优雅,请随意投票。

#5


0  

Then don't use LIKE, but search for equality.

然后不要使用LIKE,而是搜索相等。

ie.

即。

mysql_query("SELECT * FROM products WHERE product_name = '".$search."'");

BTW I hope you sanitize/escape $search before using it in a query.

顺便说一下,我希望你在查询中使用它之前清理/转义$ search。

#6


0  

To exact match you should use:

要完全匹配,您应该使用:

mysql_query("SELECT * FROM products WHERE product_name = '" . $search . "'");

#7


0  

Remove LIKE keyword and use = for exact match

删除LIKE关键字并使用=进行完全匹配

EDIT

编辑

do not forgot to escape user input using mysql_real_escape_string otherwise 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."'");

#8


0  

Use equals (=)?

使用equals(=)?

mysql_query("SELECT * FROM products WHERE product_name = '".$search."'"); 

If you are looking to match EXACT words don't use LIKE.

如果你想匹配精确的单词不要使用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." -%'"); 

#9


0  

try to use regular expression in query

尝试在查询中使用正则表达式

mysql_query("SELECT * FROM products WHERE product_name regexp '".$search."'");

#10


0  

you can use select query like this ,i also use in cakePHP and it's helpful.

你可以使用像这样的选择查询,我也在cakePHP中使用它,这很有帮助。

Select * from `users` where username COLLATE latin1_general_cs LIKE '%$email%'