上周我们公司来了个新技术总监,第一天例会上就丢下一句话:“以后谁在 SQL 里写in或not in,直接走人。” 一屋子人全愣了,心里都嘀咕:这哥们是不是太较真了?结果他慢慢解释了下,大家才发现这不是耍威风,而是有些坑真能要命。
为什么 in/not in 会出事
你平时写查询的时候,是不是随手来一句:
select * from user where id in (1,2,3,4,5);
这种小数据量当然没毛病,但要是你in的后面跟了几千个 ID,或者干脆是个子查询,那数据库可能直接把你搞趴下。
更坑的是not in,假设你这么写:
select * from user
where id not in (select user_id from order);
如果order表里user_id有一条是null,那整条语句直接查不出来任何结果。你以为是“没下过单的用户一个都没有”,其实是被null给玩了。
更靠谱的写法
大多数情况下,可以用exists或not exists来替代,逻辑清楚也更安全。
比如找“没有下过单的用户”:
select * from user u
where not exists (
select 1 from order o where o.user_id = u.id
);
或者用left join:
select u.* from user u
left join order o on u.id = o.user_id
where o.user_id is null;
这俩写法效果一样,但比not in靠谱多了。
在 Java 里怎么避免
我自己踩过坑,当时用 JPA 写了个接口:
@Query("select u from User u where u.id in :ids")
List<User> findByIds(@Param("ids") List<Long> ids);
调用的时候一传就是上千个 ID,结果 SQL 拼出来一长串,数据库直接崩溃。后来我就改成分批查询,或者干脆用exists。
比如用 JPA 可以写成这样:
@Query("select u from User u where exists (" +
"select 1 from Order o where o.userId = u.id and o.status = :status)")
List<User> findUsersWithOrder(@Param("status") String status);
这里就不再依赖in,而是明确用exists来过滤。
如果你用 MyBatis,常见的in写法是这样的:
select * from user where id in
<foreach item="id" collection="list" open="(" separator="," close=")">
#{id}
</foreach>
这个也能用,但一定要记得控制 list 的大小,别一次丢几千个 ID 进去。
更好的方式是用join:
select u.* from user u
left join orders o on u.id = o.user_id
where o.user_id is null
这样不仅可读性高,数据库优化器也更容易走索引。
技术总监的潜台词
其实他说的“走人”不是开玩笑,而是提醒我们:写 SQL 不能只看能跑通,得想清楚在大数据量、高并发的场景下会不会掉坑。
简单总结:
in/not in小数据量可以用,但要谨慎
exists/not exists更稳妥,尤其是子查询里可能有null的情况
join也很好用,表达更直观
在Java里记得别一次塞太多参数,必要时分批