从表中选择WHERE列包含字符串中的多个逗号分隔值

I have users table

我有用户表

users
--------------------------------
| user_id | name | hobbies_id  |
--------------------------------
|    1    | John | 1,5,8,12,21 |
|   ...

with hobby_id column which holds ID's of hobbies from other table - hobbies:

与hobby_id列，其中包含来自其他表的爱好ID - 爱好：

hobbies
-----------------------
| hobby_id |   name   |
-----------------------
|    1     | painting |
|    2     |  movies  |
|   ...
|    8     | football |
|   ...

Now I would like to select user(s) (from users table) whose hobbies_id among others is i.e. 1,8,21

现在我想选择其中hobbies_id为1,8,21的用户（来自用户表）

So John from te table above match to mine conditions and should be included in output.

所以上面的表中的约翰与地雷条件匹配，应该包含在输出中。

If it's possible I would like to accomplish it by single query. Anyway I stuck at the very beginning...

如果有可能我想通过单一查询来完成它。无论如何我一开始就坚持了......

SELECT * FROM users WHERE ??

SELECT * FROM users WHERE ??

1 个解决方案

#1

This is too long for a comment.

这个评论太长了。

Fix the data structure by adding a junction table with one row per name and hobby. Here are some reasons why you don't store multiple ids in a string:

通过添加每个名称和爱好一行的联结表来修复数据结构。以下是您不在字符串中存储多个ID的一些原因：

In relational algebra, columns traditionally contain one value.
在关系代数中，列传统上包含一个值。
SQL has a great data structure for lists. It is called a "table" not a "column".
SQL为列表提供了很好的数据结构。它被称为“表”而不是“列”。
Types are important. Integers should be stored as integers and not strings.
类型很重要。整数应存储为整数而不是字符串。
References to other tables should be enforced using foreign key relationships. These are not supported on lists of things in strings.
应使用外键关系强制引用其他表。字符串中的事物列表不支持这些。
The resulting queries are needlessly complex.
结果查询不必要地复杂。

Learn about something called "junction tables".

了解一些叫做“联结表”的东西。

#1