欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MySQL中NOT IN语句对NULL值的处理

程序员文章站 2022-05-10 14:01:24
...

顺便提一下MySQL中正则表达式匹配的简单使用:SELECT COUNT(alarmID)FROM AlarmWHERE (CVE NOT RLIKE

mysql> 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,可以见名知义。

MySQL中NOT IN语句对NULL值的处理