代码之家 › 专栏 › 技术社区 › Chuck Vose

查找数据库中丢失的数据

referential-integrity null mysql

Chuck Vose · 技术社区 · 14 年前

我需要找出一些聪明的MySQL代码片段,它可以让我轻松地看到两个表,表中的ID(如果存在)或空(如果不存在)。

我有一个用户表和一个遗留表,在手动比较之外,我无法弄清楚如何将它们显示在一个表中,以便进行比较。我想看到的是这样的:

+----------------------------+
| user_id | email     | uid  |
| 14      | me@me.com | 26   |
| 16      | ug@ug.com | NULL |
+----------------------------+

我知道有一种方法可以包括空值或空值,但我不确定它是什么。这是我到目前为止混乱的SQL查询,是的,我知道在嵌套select中执行嵌套select是很可怕的:

select uid from users where mail IN (
    select email from legacy_users where id NOT IN (
        select sourceid from migrate_map_users
    )
);

这里有三张桌子, legacy_users => migrate_map_users => users . 中间是一个M2M,连接两个。传统用户和用户都有一个电子邮件栏。以及他们自己的ID版本。

谢谢大家!

3 回复 | 直到 14 年前

Oded 14 年前

你需要了解 join types ,特别是左连接和外连接:

SELECT u.uid, u.mail, lu.id
FROM users u
LEFT OUTER JOIN legacy_users lu 
    ON u.email = lu.mail
WHERE lu.id NOT IN
   (
        SELECT sourceid 
        FROM migrate_map_users
    );

左外部联接将确保返回左表中的所有记录,无论右表中是否有对应的记录。

Sage 14 年前

select u.uid, u.mail, l.email, l.id
from users u
left outer join legacy_users
    on u.mail = l.email

--两个问题让你去

select u.uid, u.mail, l.email, l.id
from users u
left outer join legacy_users
    on u.mail = l.email
Where l.id is null

select l.email, l.id, u.uid, u.mail
from legacy_users l
left outer join users u
    on l.email = u.mail
Where u.uid is null

Chuck Vose 14 年前

多亏了奥德的回答,这就是我最后得出的结论:

SELECT * 
FROM (
  SELECT id, mail, uid 
  FROM users  
  LEFT OUTER JOIN 
    legacy_users lu ON users.mail = lu.email 
  UNION DISTINCT 
  SELECT id, email, uid 
  FROM users  
  RIGHT OUTER JOIN 
    legacy_users lu ON users.mail = lu.email
) j 
WHERE uid IS NULL 
OR id IS NULL;

这也让我可以对结果做一个“在哪里”。奖金。

注意,它在左连接中使用邮件,在右连接中使用电子邮件。由于邮件在正确的外部联接中不存在,我们必须使用来自旧用户的电子邮件列,反之亦然。