mysql 替代like,Mysql中LIKE子句的替代

最新推荐文章于 2023-02-02 16:36:07 发布

易筱昭

最新推荐文章于 2023-02-02 16:36:07 发布

阅读量251

点赞数

文章标签： mysql 替代like

本文介绍了一种在数据库中精确匹配特定类别的方法，避免了使用LIKE子句时出现的匹配多个类别的问题。通过使用特定的LIKE模式或正则表达式（REGEXP），可以有效地定位到属于指定类别的产品。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

I have got a filed in my database table which stores categories. I am storing the categories in the following format:

1,12,15

Now when I try to search for a product from category 1,

I use LIKE clause in my query such as

where (prod_catg LIKE %1,% or prod_catg LIKE %1% or prod_catg LIKE %,1% )

This returns me the products from all the three categories 1,12 and 15. Instead I just want the products from category 1.

I have also tried IN clause but no results found.

Can anyone please suggest me some other alternative.

解决方案prod_catg LIKE '1,%' --matches when 1 is the first category

OR prod_catg LIKE '%,1,%' --matches when 1 is somewhere in the middle

OR prod_catg LIKE '%,1' --matches 1 when is the last category

anyway you had better to refactor your schema by adding a category table and the reference to it on the product (main) table

EDIT

another way to face this problem is using REGEXP which will lead to a shorter WHERE clause (here is what i've used to test):

DECLARE @regexp VARCHAR(100);

SET @regexp = '^1,.*|.*,1$|.*,1,.*';

SELECT

'1,11,15,51,22,31' REGEXP @regexp AS test1,

'51,11,15,1,22,31' REGEXP @regexp AS test2,

'11,15,51,22,31,1' REGEXP @regexp AS test3,

'7,11,15,51,22,31' REGEXP @regexp AS test4,

'51,11,15,7,22,31' REGEXP @regexp AS test5,

'11,15,51,22,31,7' REGEXP @regexp AS test6;

this will match your prod_catg against the Regular Expression '^1,.*|.*,1$|.*,1,.*' returnig 1 (TRUE) if it matches, 0 (FALSE) otherwise.

Then your WHERE clause will look like:

WHERE prod_catg REGEXP '^1,.*|.*,1$|.*,1,.*'

explanation of regexp:

^1,.* --matches 1 at the beginning of a string followed by a `,` and any other char

.*,1$ --matches 1 at the end of a string preceded by a `,` and any other char

.*,1,.* --matches 1 between two `,` which are sourrounded by any other chars

| --is the OR operator

i'm sure this regexp could be much more compact but i'm not that good with regular expressions

obviuosly you can change the category you're looking for in the regular expression (try to replace 1 with 7 on the example above)