MySQL SELECT电子邮件，其中字段包含值

I would like to do a simple select - but the condition is a bit tricky for me (because I'm a SQL-beginner).

我想做一个简单的选择 - 但这个条件对我来说有点棘手(因为我是一个SQL初学者)。

I got this table:

我拿到了这张桌子:

userid | email             | newsletters
     1 | test@example.com  | 1,2
     2 | test2@example.com | 1

Now I would like to get all email-addresses of users, which want to get newsletter "2".

现在我想获得所有希望收到简报“2”的用户的电子邮件地址。

This would be:

这将是:

email | newsletters
test@example.com | 1,2

And of course: In another query all users, which are subscribing newsletter number 1:

当然:在另一个查询中,所有用户都订阅了第1号通讯:

Result:

email | newsletters
test@example.com | 1,2
test2@example.com | 1

What would be the correct sql-query? I think this should be the right beginning, but I don't know which condition I have to use:

什么是正确的SQL查询?我认为这应该是正确的开始,但我不知道我必须使用哪种条件:

SELECT email FROM users WHERE newsletter CONDITION?

Could you please help me out? :-)

你能帮帮我吗? :-)

2 个解决方案

#1

This will do the work assuming number of newsletter can't be higher than 9:

这将做的工作假设通讯数量不能高于9:

SELECT email FROM users WHERE newsletters LIKE '%2%'

If you'd like to have more of them then table normalization would be very helpful.

如果您想要更多它们,那么表格规范化将非常有用。

EDIT: @sgeddes in comments has great proposition to make it working for any number of newsletters:

编辑:@sgeddes在评论中提出了很好的建议,使其适用于任何数量的新闻简报:

SELECT email FROM users WHERE concat(',',newsletters,',') LIKE '%,2,%'

#2

Use a regular expression if you really want to do this, but I think you need to redesign your table structure. Instead of storing the newsletters per user in the User table, you should create a bridge table between User and Newspaper like this:

如果你真的想这样做,请使用正则表达式,但我认为你需要重新设计表结构。您应该在User和Newspaper之间创建一个桥接表,而不是将每个用户的新闻简报存储在User表中,如下所示:

User table
userid | email            
     1 | test@example.com 
     2 | test2@example.com

Newspaper table
paperid | name
      1 | the Sun
      2 | the Mirror

UserNewspaper Bridge table
userid | paperid     (represents, not part of table)
     1 | 1           (test@example.com receives the Sun)
     1 | 2           (test@example.com receives the Mirror)
     2 | 1           (test2@example.com receives the Sun)

To get all email-addresses of users that want paperid 2 you'd write this:

要获取想要paperid 2的用户的所有电子邮件地址,您需要写下:

select a.email
from   User a,
       UserNewspaper b
where  a.userid = b.userid
and    b.paperid = 2

To get all email-addresses of users that want the Mirror you'd write this:

要获取想要镜像的用户的所有电子邮件地址,您可以写下:

select a.email
from   User a,
       UserNewspaper b,
       Newspaper c
where  a.userid = b.userid
and    b.paperid = c.paperid
and    c.name = 'the Mirror'

#1