Mysql中LIKE子句的替代 [英] Alternative to LIKE clause in Mysql

问题描述

我的数据库表中有一个文件，其中存储了类别.我以以下格式存储类别:

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

1,12,15

现在，当我尝试搜索类别1中的产品时， 我在查询中使用LIKE子句，例如

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% )

这将为我返回来自1,12和15三个类别的产品.相反，我只想要来自类别1的产品.

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

我也尝试过IN子句，但未找到结果.

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

编辑

面对此问题的另一种方法是使用 REGEXP ，这将导致较短的WHERE子句(这是我用来测试的子句):

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;

这将使您的prod_catg与正则表达式'^1,.*|.*,1$|.*,1,.*' returnig 1 (TRUE)匹配，否则与0 (FALSE)匹配.

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

然后您的WHERE子句如下:

Then your WHERE clause will look like:

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

正则表达式的解释:

^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

您可以在正则表达式中更改要查找的类别(在上面的示例中，尝试将1替换为7)

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

这篇关于Mysql中LIKE子句的替代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！