mysql - 2 个 SQL 查询的奇怪结果

Question

查询#1:

SELECT SUM(size) 
FROM RepoSize s 
LEFT JOIN VirtualRepo v ON s.repo_id = v.repo_id 
WHERE v.repo_id IS NULL;

+----------------+
| SUM(size)      |
+----------------+
| 61550890457198 |
+----------------+
1 row in set (0.32 sec)

查询#2:

SELECT SUM(size) 
FROM RepoSize 
WHERE repo_id NOT IN (SELECT repo_id FROM VirtualRepo);

+----------------+
| SUM(size)      |
+----------------+
| 61551148262106 |
+----------------+
1 row in set (0.45 sec)

我以为这两个 SQL 查询会返回相同的结果，但事实是第二个值比第一个大，repo_id 是两个表中的主键。

表结构:

mysql> desc RepoSize;
+---------+---------------------+------+-----+---------+-------+
| Field   | Type                | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+-------+
| repo_id | char(37)            | NO   | PRI | NULL    |       |
| size    | bigint(20) unsigned | YES  |     | NULL    |       |
| head_id | char(41)            | YES  |     | NULL    |       |
+---------+---------------------+------+-----+---------+-------+

mysql> desc VirtualRepo;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| repo_id     | char(36) | NO   | PRI | NULL    |       |
| origin_repo | char(36) | YES  | MUL | NULL    |       |
| path        | text     | YES  |     | NULL    |       |
| base_commit | char(40) | YES  |     | NULL    |       |
+-------------+----------+------+-----+---------+-------+

Answer 1

由于 repo_id 是两个表中的主键，因此不会因为字段中的空值而出现差异。所以理想情况下，除非数据在执行之间发生变化，否则两个查询都应该给出相同的结果。因此，差异很可能是因为两者之间的数据发生了变化。