首先为我糟糕的英语道歉。我的场景如下:
我正在开发一个通知服务,向许多用户发送通知消息。我在MySql上有以下3个表
users(user_id)
notifications(notification_id, notification)
notifications_log(notification_log_id, notification_id, user_id)
每当用户读取通知时,我在notifications_log表上插入一条记录,例如,John用户使用user_id = 2
读取notification_id =3
的通知:"This is a notification",然后我在 notifications_lo
g上插入一条记录,使用user_id = 2
和notification_id = 3
。
一切都好,但我必须创建一个查询来获取所有未插入notifications_log上的用户的所有通知。我有的是:
SELECT u.user_id, n.notification_id, n.notification, nl.notification_log_id
FROM users as u
LEFT JOIN notifications_log as nl ON nl.user_id = u.user_id
CROSS JOIN notifications as n
WHERE u.user_id NOT IN (SELECT nl.user_id FROM notifications_log as nl)
AND u.user_id = 1 /* test with user 1 */
如果用户1的notifications_log表中没有记录,查询结果显示为me
user_id | notification | notification_id | notification_log_id
------------------------------------------------------------------------------
- 1 | Notification_1 | 1 | null
- 1 | Notification_2 | 2 | null
但是如果我在notifications_log
上为用户和notification_2插入至少1条记录,那么我得到空结果,我应该得到:
user_id | notification | notification_id | notification_log_id
----------------------------------------------------------------------------
- 1 | Notification_1 | 1 | null
似乎查询将notification_log_id与空notification_log_id…连接到另一条记录
简而言之,我需要的是获取来自特定用户的所有通知,这些通知没有插入到表notifications_log
中提前感谢!
您想要的查询可能是这个:
select n.notification_id, u.user_id
from notifications n
cross join users u
left join notifications_log nl
on n.notification_id = nl.notification_id
and nl.user_id = u.user_id
where nl.notification_log_id is null
演示
这个查询消除了您的派生表,减少了执行时间,并尽可能早地执行交叉连接以减少正在操作的行总数。
但是我建议你重新考虑一下。一旦通知和用户表达到临界质量,这将创建数以百万计的行来过滤。
一个更好的主意是使用notification_inbox表,作为notifications_log表的对应表。创建通知后,将其放在每个用户的收件箱表中。这样,您就可以在单个表上执行一个简单的查询,以确定每个用户的未读通知,而不是潜在的可怕的执行cross join
。
或者,再一次,一个notification_delivery表,而不是收件箱和日志表,它有一个'read'标志。这也将允许有针对性的通知,以及批量发送给所有用户。
似乎你是在正确的轨道上,但应该只是改变user_id到notification_id在倒数第二行:
SELECT u.user_id, n.notification_id, n.notification, nl.notification_log_id
FROM users as u
LEFT JOIN notifications_log as nl ON nl.user_id = u.user_id
CROSS JOIN notifications as n
WHERE n.notification_id NOT IN (SELECT nl.notification_id FROM notifications_log as nl)
AND u.user_id = 1 /* test with user 1 */