订单列表少了一半,接口没报错,分页也正常。 最后翻 SQL,问题就藏在一行:
left join pay_record p on p.order_id = o.id
where p.status = 'SUCCESS'
这 SQL 第一眼看着没毛病,左连接嘛,订单表在左边,支付记录在右边。结果一跑,没支付记录的订单全没了。
这地方我一般不会先怀疑 Java 分页,也不会先怀疑 MyBatis。先把 SQL 拿出来单独跑,再把 where 里的右表条件挪一挪,结果通常就出来了。
比如订单表:
select
o.id,
o.order_no,
o.user_id,
p.pay_no,
p.status
from t_order o
left join t_pay_record p
on p.order_id = o.id
where o.user_id = 1001;
这个没什么争议。
o.user_id = 1001 是左表条件,放在 where 里很自然。意思是:我只查这个用户的订单,不管有没有支付记录,都要把订单查出来。
但如果改成这样:
select
o.id,
o.order_no,
p.pay_no,
p.status
from t_order o
left
join t_pay_record p
on p.order_id = o.id
where o.user_id = 1001
and p.status = 'SUCCESS';
这就变味了。
left join 本来会把左表数据保留下来,右表匹配不上时,右表字段补 null。可你后面又写了:
p.status = 'SUCCESS'
右表都补成 null 了,当然不可能等于 SUCCESS。于是这些没支付记录的订单,在 where 阶段被干掉了。
这时候它看起来写的是 left join,跑出来的效果更像
inner join。
如果业务要的是“查用户订单,并且只带成功支付记录;没支付也要显示订单”,我会写成这样:
select
o.id,
o.order_no,
p.pay_no,
p.status
from t_order o
left join t_pay_record p
on p.order_id = o.id
and p.status = 'SUCCESS'
where o.user_id = 1001;
注意,p.status = 'SUCCESS' 挪到了 on 后面。
这个语义就稳了: 先限定右表只拿成功支付记录来匹配;匹配不上也没关系,订单还在。
这就是 on 和 where 最大的区别。
on 管的是“怎么匹配右表”。
where 管的是“最终结果要不要留下”。
这句话别背,背了也容易忘。线上排问题时直接看右表字段在哪里过滤。
我之前见过一个查询售后单的接口,代码里大概是这种写法:
public List queryOrders(Long userId, String payStatus) {
String sql = """
select
o.id,
o.order_no,
o.created_at,
p.pay_no,
p.status as pay_status
from t_order o
left join t_pay_record p on p.order_id = o.id
where o.user_id = ?
and p.status = ?
order by o.created_at desc
""";
return jdbcTemplate.query(sql, new OrderViewMapper(), userId, payStatus);
}
测试同学说“未支付订单不展示了”。 开发第一反应是前端状态判断错了,我不太信。因为前端再怎么判断,后端返回条数已经少了。
我一般会先把 SQL 改成这样:
public List queryOrders(Long userId, String payStatus) {
String sql = """
select
o.id,
o.order_no,
o.created_at,
p.pay_no,
p.status as pay_status
from t_order o
left join t_pay_record p
on p.order_id = o.id
and p.status = ?
where o.user_id = ?
order by o.created_at desc
""";
return jdbcTemplate.query(sql, new OrderViewMapper(), payStatus, userId);
}
这里参数顺序也要跟着改,不然又是另一个坑。
如果用 MyBatis,最容易写坏的是动态条件:
<select id="pageOrder" resultType="OrderView">
select
o.id,
o.order_no,
p.pay_no,
p.status as pay_status
from t_order o
left join t_pay_record p
on p.order_id = o.id
where o.user_id = #{userId}
<if
test="payStatus != null and payStatus != ''">
and p.status = #{payStatus}
if>
select>
这个写法只要 payStatus 有值,左连接就被你自己拆了。
我更愿意这样写:
<select id="pageOrder" resultType="OrderView">
select
o.id,
o.order_no,
p.pay_no,
p.status as pay_status
from t_order o
left join t_pay_record p
on p.order_id = o.id
<if test="payStatus != null and payStatus != ''">
and p.status = #{payStatus}
if>
where o.user_id = #{userId}
select>
这个版本至少语义没乱。
当然,不是所有右表条件都必须放 on。
如果业务就是要“只查已经成功支付的订单”,那你写:
where p.status = 'SUCCESS'
也没问题。甚至直接写 inner join 更干脆:
select
o.id,
o.order_no,
p.pay_no
from t_order o
inner join t_pay_record p
on p.order_id = o.id
where o.user_id = 1001
and p.status = 'SUCCESS';
我比较嫌弃那种明明要内连接,非写个 left join,然后在 where 里把右表 null 过滤掉的 SQL。不是不能跑,是后面接手的人会误判业务含义。
还有一种情况也常见:
where p.status <> 'CLOSED'
这个也会过滤掉右表为 null 的记录。因为 null <> 'CLOSED' 不是 true。
如果你确实想保留没匹配到右表的数据,要写清楚:
where p.status <> 'CLOSED'
or p.id is null
但我一般不优先这么写。能放
on 的右表匹配条件,先放 on,SQL 意图更清楚。
再看执行计划时,也别只看有没有走索引。先看返回行数是不是符合业务。
我排这种问题,经常会先临时加两列:
select
o.id,
o.order_no,
p.id as pay_id,
casewhen p.id isnullthen1else0endas no_pay
from t_order o
leftjoin t_pay_record p
on p.order_id = o.id
and p.status = 'SUCCESS'
where o.user_id = 1001;
no_pay = 1 的数据还在,说明左连接语义没被破坏。 如果一条都没了,就去查 where,十有八九是右表条件放错了。
最后记一个现场规则就够了:
左表过滤,放 where。 右表参与匹配,但不想丢左表数据,放
on。 右表条件放 where,通常意味着你接受丢掉右表匹配不上的左表记录。
这不是语法洁癖,是结果集会不会少数据的问题。线上分页少几条,用户不会告诉你 SQL 写得不优雅,只会说订单丢了。