MySql 连接 2 个没有单个公共(public)字段的表以排除记录

Question

使用 MySql 并且需要从 temp_comments 中选择(然后插入)数据到评论中——除非评论已经存在于评论表中。这是一个例子的一些测试数据

comments table
id | apn   | account   | comment
--------------------------------
1  | 100   |           | todays comment
2  | 101   |           | yesterdays comment
3  | 109   |           | todays comment
4  | 122   |           | more comments
5  |       | 34550     | todays comment
6  |       | 12433     | another comment


temp_comments table
id | apn   | account   | comment
-----------------------------------------
1  | 92    |           | todays comment
2  | 99    |           | another comment
3  | 100   |           | more comments
4  | 109   |           | todays comment
5  |       | 34588     | new comment

在这种情况下，除了来自 temp_comments 的 id#4 之外的所有内容都应该被选择并插入到评论中(因为对于该记录，apn | account | comment 都与评论表中的一行相匹配)。

我尝试在尝试插入之前让 select 语句起作用:

select apn, account, comment
from temp_comments
where not exists( select apn, account, comment
from temp_comments
except
select apn, account, comment
from comments)

因为 EXCEPT 在 mySQL 中不起作用，这会弹出一个错误。不确定如何使用连接，因为所有 3 个字段都需要匹配才能排除它。一旦我让 SELECT 工作，我需要插入评论，例如:

INSERT into comments
apn, account, comments
SELECT (and put my working SELECT statement HERE)

Answer 1

使用 LEFT JOIN 查找一个表中所有在另一个表中没有匹配项的行。

SELECT tc.apn, tc.account, c.comment
FROM temp_comments AS tc
LEFT JOIN comments AS c ON c.apn <=> tc.apn AND c.account <=> tc.account AND c.comment <=> tc.comment
WHERE c.id IS NULL

或使用NOT EXISTS:

SELECT apn, account, comment
FROM temp_comments AS tc
WHERE NOT EXISTS
    (SELECT *
     FROM comments AS c
     WHERE c.apn <=> tc.apn AND c.account <=> tc.account AND c.comment <=> tc.comment)