MySQL - 无法连接时显示/计数"normal"连接记录

Question

获得了2个表 - 联系人和消息：

contact_id  |   contact_email
1           |   [e-mail]
2           |   [e-mail]
3           |   [e-mail]
message_id  |   message_recipients
1           |   1,2,3
2           |   3

message_recipients字段包含分配给联系人消息的ID。每条消息可以分配一个或多个ID，因此它们由,符号分开。

我需要显示所有联系人，并将消息计数分配给每个联系人。由于message_recipients字段可能包含多个ID，因此我无法运行SELECT * FROM contacts, messages WHERE contacts.contact_id=messages.message_recipients这样的查询，因为它无法正常工作。

如果我运行SELECT * FROM contacts FULL JOIN messages，它将从contacts表中返回许多重复的行。当然，我可以运行 SELECT * FROM contacts FULL JOIN messages GROUP BY contact_id，但是这个只能从 messages表返回第一消息。

我知道，要计算每个联系人分配给多少消息，我可能需要从每行爆炸message_recipients字段到数组中，并使用if (in_array($contact_id, $message_recipients_array)) {$total++;}或类似代码。现在，我主要关注的是如何通过编写尽可能简单的查询来完成所有内容。

Answer 1

修复您的表结构。不要将多个值存储在一个单元格中。参见归一化

目前，您可以使用FIND_IN_SET：

select c.contact_id,
    c.contact_email,
    count(*) no_of_messages
from messages m
join contacts c on find_in_set(c.contact_id, m.message_recipients) > 0
group by c.contact_id,
    c.contact_email

但这将是 slow ，因为它无法在contact_id或message_recipient上使用任何索引。

要实际解决问题，请勿在消息表中包含conferent_id。

您应该将单个收件人存储在一个单独的映射表中，并与以下结构有很多关系。

messages_recipients (
    id int PK,
    message_id int FK referring message(message_id),
    message_recipient_id int FK referring contacts(contact_id)
)

那么您要做的就是：

select c.contact_id,
    c.contact_email,
    count(*) no_of_messages
from messages_recipients m
join contacts c on c.contact_id = m.message_recipient_id
group by c.contact_id,
    c.contact_email

此查询是Sargable，并且会更快。

Answer 2

修复您的数据结构！在字符串中存储ID是一个非常糟糕的主意。为什么？

• 数字应作为数字而不是字符串存储。
• SQL不提供很好的字符串功能。
• 外键约束应适当表达。
• 查询优化器不能使用索引或分区。
• SQL有一个很好的存储列表的方法：它称为"表"。

有时候，我们被人们真正，非常糟糕的设计决策所困扰。MySQL确实提供了一种可以执行您想要的方法find_in_set()。这是围绕不良数据布局的缺点的入侵：

select . . .
from contacts c join
     messages m
     on find_in_set(c.contact_id, m.message_recipients) > 0