Hello I have a table with articles and the articles have a column category. The categories are filled like 1,4,6,8
你好我有一个文章表,文章有列类。类别填写如1,4,6,8
How can i check if there is a product which has in it for example category 5
我如何检查是否有产品,例如类别5
I tried something like
我试过类似的东西
select * from article where category in(5);
But that doesn't work. If I use like than i will have a problem with for example 1 and 10. So how can I do this in one mysql query?
但这不起作用。如果我使用喜欢,那么我将遇到例如1和10的问题。那么如何在一个mysql查询中执行此操作?
4 个解决方案
#1
- Storing CSV in a column you need to query is a bad idea - you should use a separate table.
- IN is not for CSVs - it's for listing values for a single column
- Those arguments aside, you can use
FIND_IN_SET()
在需要查询的列中存储CSV是个坏主意 - 您应该使用单独的表。
IN不适用于CSV - 它用于列出单个列的值
抛开那些论点,你可以使用FIND_IN_SET()
For example:
SELECT * FROM article WHERE FIND_IN_SET('5', category) != 0;
#2
You could do it with select * from article where category='5' or category like '5,%' or category like '%,5' or category like '%,5,%'
您可以使用select * from category of category ='5'或类别如'5,%'或类别如'%,5'或类别'%,5,%'来实现
But you really don't want to do that.
但你真的不想这样做。
Instead, what you're after is
相反,你所追求的是
create table article (
id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
headline VARCHAR(50) NOT NULL,
body VARCHAR(50) NOT NULL
);
create table articlecategory (
article_id INTEGER NOT NULL,
category_id INTEGER NOT NULL,
PRIMARY KEY (article_id, category_id)
);
And then
SELECT
article.*
FROM
article,articlecategory
WHERE
articlecategory.article_id=article.id AND
articlecategory.category_id=5;
#3
try this:
select *
from article
where category like '%,5,%' or category like '5,%' or category like '%,5'
#4
well, that is not the best solution database wise, to have a column with comma separated values. Instead you could have a separate table with two columns, one for the category and one with a article id. Then you could do:
好吧,这不是最好的解决方案数据库,有一个逗号分隔值的列。相反,您可以拥有一个包含两列的单独表,一列用于类别,另一列用于文章ID。然后你可以这样做:
select * from article, category_articles where category_article.category_id = category.id and category_article.category = 5
Take a look at this for more on database normalization, which this is kinda related to
看看这个有关数据库规范化的更多信息,这有点与之相关
But if that is not an option you could try using a delimiter and store the data like this: C1C10C, then you could use:
但如果这不是一个选项,您可以尝试使用分隔符并存储如下数据:C1C10C,然后您可以使用:
select * from article where category like '%C1C%'
which would not mach 10, but will match the 1.
哪个不会马赫10,但会匹配1。
#1
- Storing CSV in a column you need to query is a bad idea - you should use a separate table.
- IN is not for CSVs - it's for listing values for a single column
- Those arguments aside, you can use
FIND_IN_SET()
在需要查询的列中存储CSV是个坏主意 - 您应该使用单独的表。
IN不适用于CSV - 它用于列出单个列的值
抛开那些论点,你可以使用FIND_IN_SET()
For example:
SELECT * FROM article WHERE FIND_IN_SET('5', category) != 0;
#2
You could do it with select * from article where category='5' or category like '5,%' or category like '%,5' or category like '%,5,%'
您可以使用select * from category of category ='5'或类别如'5,%'或类别如'%,5'或类别'%,5,%'来实现
But you really don't want to do that.
但你真的不想这样做。
Instead, what you're after is
相反,你所追求的是
create table article (
id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
headline VARCHAR(50) NOT NULL,
body VARCHAR(50) NOT NULL
);
create table articlecategory (
article_id INTEGER NOT NULL,
category_id INTEGER NOT NULL,
PRIMARY KEY (article_id, category_id)
);
And then
SELECT
article.*
FROM
article,articlecategory
WHERE
articlecategory.article_id=article.id AND
articlecategory.category_id=5;
#3
try this:
select *
from article
where category like '%,5,%' or category like '5,%' or category like '%,5'
#4
well, that is not the best solution database wise, to have a column with comma separated values. Instead you could have a separate table with two columns, one for the category and one with a article id. Then you could do:
好吧,这不是最好的解决方案数据库,有一个逗号分隔值的列。相反,您可以拥有一个包含两列的单独表,一列用于类别,另一列用于文章ID。然后你可以这样做:
select * from article, category_articles where category_article.category_id = category.id and category_article.category = 5
Take a look at this for more on database normalization, which this is kinda related to
看看这个有关数据库规范化的更多信息,这有点与之相关
But if that is not an option you could try using a delimiter and store the data like this: C1C10C, then you could use:
但如果这不是一个选项,您可以尝试使用分隔符并存储如下数据:C1C10C,然后您可以使用:
select * from article where category like '%C1C%'
which would not mach 10, but will match the 1.
哪个不会马赫10,但会匹配1。