Question

mysql 查询问题 关于 not in

mysql> SELECT 10050001 NOT IN (
-> SELECT `CampusID`
-> FROM `USERS`
-> );
+----------------------------------------------------+
| 10050001 NOT IN (
SELECT `CampusID`
FROM `USERS`
) |
+----------------------------------------------------+
| NULL |
+----------------------------------------------------+
为什么会出现这样的结果呢？

Answer 1

MySQL中NOT IN语句对NULL值的处理2007-07-23 16:01mysql> SELECT COUNT(name) FROM CVE WHERE name NOT IN ('CVE-1999-0001', 'CVE-1999-0002');
+-------------+
| count(name) |
+-------------+
| 17629 |
+-------------+
1 row in set (0.02 sec)

mysql> SELECT COUNT(name) FROM CVE WHERE name NOT IN ('CVE-1999-0001', 'CVE-1999-0002', NULL);
+-------------+
| count(name) |
+-------------+
| 0 |
+-------------+
1 row in set (0.01 sec)

当在子查询中出现NULL的时候，结果就一定是0了。查了一下手册，确实有这样的说法。所以最后实际采用了这样的查询：
SELECT COUNT(DISTINCT name)
FROM CVE
WHERE name NOT IN (SELECT cveID FROM cve_sig WHERE cveID IS NOT NULL)

顺便提一下MySQL中正则表达式匹配的简单使用：
SELECT COUNT(alarmID)
FROM Alarm
WHERE (CVE NOT RLIKE '^CVE-[0-9]{4}-[0-9]{4}$' OR CVE IS NULL)
当然，RLIKE也可以写作REGEXP，我个人倾向于使用RLIKE，因为拼写接近LIKE，可以见名知义。