两个表之间的MySql CROSS JOIN并与另一个表匹配 [英] MySql CROSS JOIN between two tables and match with another
问题描述
首先很抱歉我的英语不好.我的场景如下:
first of all sorry for my bad english. The scenario i have is the following:
我正在开发一种通知服务，可以向许多用户发送通知消息.我在 MySql 上有以下 3 个表
I am developing a notification services that sends notifications messages to many users. I have the following 3 tables on MySql
users(user_id)
notifications(notification_id, notification)
notifications_log(notification_log_id, notification_id, user_id)
每次用户阅读通知时，我都会在通知日志表中插入一条记录，例如.user_id = 2 的 John 用户使用 notification_id =3 读取通知:这是一条通知"，然后我在 notifications_lo 上插入一条记录g 使用 user_id = 2 和 notification_id = 3.
Every time that a user read a notification, i insert a record on notifications_log table, ex. John user with user_id = 2 read the notification with notification_id =3: "This is a notification", and then i insert a record on notifications_log with user_id = 2 and notification_id = 3.
一切正常，但我必须创建一个查询来获取所有未插入到notifications_log 的用户的所有通知.我有的是:
There is all ok, but i have to create a query to get all the notifications for all the users that not are inserted on notifications_log. What i have is:
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表没有记录，查询结果显示
If there is no records on notifications_log table of user 1, query results show me
user_id | notification | notification_id | notification_log_id
------------------------------------------------------------------------------
- 1 | Notification_1 | 1 | null
- 1 | Notification_2 | 2 | null
但是如果我在 notifications_log 上为 user 和 notification_2 插入至少 1 条记录，那么我得到的结果是空的，我应该得到:
But if i insert at least 1 record on notifications_log for user and notification_2, then i get empty results, and i should get:
user_id | notification | notification_id | notification_log_id
----------------------------------------------------------------------------
- 1 | Notification_1 | 1 | null
似乎查询将notification_log_id 加入到另一个notification_log_id 为空的记录中...
It seems that the query joins the notification_log_id to the other record with null notification_log_id...
简而言之，我需要的是从特定用户那里获取所有通知，这些通知没有插入到表通知日志中
In short, what I need it is get all the notifications from a especific user that there are not on inserted on the table notifications_log
提前致谢！
推荐答案
你想要的查询大概是这个:
The query you want is probably this one:
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
此查询消除了您的派生表，减少了执行时间，并尽早执行交叉联接以减少正在操作的总行数.
This query eliminates your derived table, reducing the execution time, and performs the cross join as early as possible to reduce the total number of rows being operating on.
但我建议完全重新考虑这一点.一旦通知和用户表达到临界质量，这将创建数以百万计的行进行过滤.
But i'd suggest rethinking this altogether. Once notifications and users table reaches critical mass this is going to create millions upon millions of rows to filter.
一个更好的主意是有一个 notification_inbox 表，作为你的 notification_log 表的对应表.创建通知后，将其放置在每个用户的收件箱表中.这样您就可以对单个表执行简单的查询，以确定每个用户的未读通知，而不是潜在地执行可怕的cross join.
A better idea would be to have a notification_inbox table, as a counterpart to your notifications_log table. When a notification is created, place it in the inbox table for each user. That way you can perform a simple query on a single table to determine unread notifications per user, rather than a potentially horrendously performing cross join.
或者，一个notification_delivery 表，而不是具有读取"标志的收件箱和日志表.这也将允许有针对性的通知，以及向所有用户批量发送.
Alternatively again, a single notification_delivery table, rather than inbox and log tables, which has a 'read' flag. This would also allow targeted notifications, as well as bulk delivery to all users.
这篇关于两个表之间的MySql CROSS JOIN并与另一个表匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
