数据库 not in 踩坑记录

hooopo · 2022年02月14日 · 最后由 oatw 回复于 2022年02月14日 · 967 次阅读

在做一些类似黑名单的功能时,会使用 not in 来过滤。比如:

select * from users where id not in (1,2,3);

// 或者

select * from users 
where id not in (
  select user_id from blacklist
)

但是我们经常忽略一个前提条件,这两个查询能够满足需求的前提是:in后面的返回值列表不包含NULL

那么现在来看一下各种情况not in的返回值:

select 
  1 not in (2,3) as "1 not in (2,3)", 
  1 not in (1,3) as "1 not in (1,3)",
  1 not in (1,null) as "1 not in (1, null)", 
  1 not in (2, null) as "1 not in (2, null)";

 1 not in (2,3) | 1 not in (1,3) | 1 not in (1, null) | 1 not in (2, null)
----------------+----------------+--------------------+--------------------
 t              | f              | f                  | (null)

前三个都符合预期,下面来看第四种情况:

select 1 not in (2, null);
 ?column?
----------
 (null)
(1 row)

查了一下资料,返回NULL的原因是:

1 not in (2, null)被翻译成了 1 != 2 and 1 != null => true and null => null

所以,在 not in 相关查询里一定要注意 in 后面是不是有可能包含 NULL 值,否则结果可能不符合预期。

select * from users 
where id not in (
  select user_id from blacklist where user_id is not null
)

ref:https://ossdaily.com/hooopo/not-in-cai-keng-ji-lu-hpa

2333 所以通常习惯性后面补一个 is not null

刚入行就踩到过这个坑😂 😂

大部分是用常量和主键来过滤,没踩过。

SQL 特殊地方,他是一个 三元逻辑系统 true、false、null 逻辑处理的时候,必须要考虑 null,除非这个字段加了 非 null 约束。

他的布尔值运算,也要遵循三元真值表。

(前几天看了,逃。。。

黑名单这个虽然是外键,应该也有非空约束吧。

pynix 回复

黑名单只是一个便于理解的例子 实际情况可以很复杂 你可以理解为 in+ 子查询的都会触发

hooopo 回复

理论上,一个可空子查询的过滤条件一定会加上非空过滤吧。

pynix 回复

马后炮😂

PostgreSQL 和 MySQL 的默认索引结构是个 B+ tree,not in!= 都没有办法利用索引,导致全表扫描。

如果存在被屏蔽的黑名单用户,我是宁可建一个冗余字段 status 然后 select * from users where status = 'active' and xxx = xxx

因为 status 只有几个数值,建了索引效果也不好,一定得跟着一个其他的过滤条件。比如 company_id = xxxxx。

hooopo 回复

没踩过

xiaoronglv 回复

是的 但 not in 其实还好,一般都会跟着其他条件一起,还是走索引的,只是再多了一个 filter。 最可怕的是 or 查询,这个才是索引杀手。

数据库三值逻辑与编程语言二值逻辑的矛盾,记得一本书里好像说过有一种主义叫做数据库无NULL主义……

啊~找到了

https://www.ituring.com.cn/book/tupubarticle/16419?bookID=1880&type=tubook&subject=%E7%AC%AC%202%20%E7%AB%A0%E3%80%80%E6%9F%A5%E8%AF%A2%E5%9F%BA%E7%A1%80

考虑 NULL 时的条件判断也会变得异常复杂,这与我们希望的结果大相径庭。因此,数据库领域的有识之士们达成了“尽量不使用 NULL”的共识。

需要 登录 后方可回复, 如果你还没有账号请 注册新账号