MySQL 忽略用户通过检查“看到"已经看到的行桌子 [英] MySQL omitting rows a user has already seen from checking a "seen" table

问题描述

我正在尝试找出如何最好地编写语句，以省略用户 (uuid_user) 的返回行 (maintable)已经看过 (seentable).

I'm trying to figure out how best to write a statement that omits returning rows (maintable) that a user (uuid_user) has already seen (seentable).

• 问:我应该看到"吗?表是每个用户的单独表吗?

目前，我将它作为一个 seen 表供所有用户使用

Currently, I have it as a single seen table for all users

用户已经看到的行存储在每个 uuid_user 的 seentable 中.这是我的表格的简化版本.uuid 用作加入的键.uuid_user 标识特定用户看到的行.

Rows that a user has already seen are stored in seentable for each uuid_user. Here is a simplified version of my tables. uuid is used as the key to join. uuid_user identifies the particular user's seen rows.

不太好:

SELECT * 
  FROM maintable m 
 LEFT JOIN seentable s 
    on m.uuid = s.uuid 
 WHERE s.uuid IS NULL and s.uuid_user = '[user]'

maintable
uuid (char36)
lng,lat (POINT)
timestamp (timestamp)

seentable 
uuid (char36)
uuid_user (char36)
timestamp (timestamp)

推荐答案

您需要将 uuid_user 上的限制从 WHERE 子句移到 ON<连接的/code> 子句:

You need to move the restriction on uuid_user from the WHERE clause to the ON clause of the join:

SELECT * 
FROM maintable m 
LEFT JOIN seentable s 
    ON m.uuid = s.uuid AND s.uuid_user = '[user]'
WHERE s.uuid IS NULL;

以上就是你想要的逻辑.现在，当 uuid 值与 和 匹配时，当看到的表中的 uuid_user 值与某个值匹配时，两个表中的记录之间就会成功连接.请注意，如果 uuid_user 值 不 匹配任何记录，它不会被过滤掉，这就是您当前查询会发生的情况.

The above is the logic you want here. Now, a successful join between records from the two tables occurs whenever the uuid values match and when the uuid_user value from the seen table matches some value. Note that should the uuid_user value not match for any record, it would not be filtered off, which is what will happen with your current query.

这篇关于MySQL 忽略用户通过检查“看到"已经看到的行桌子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！