我要评论前阵子有个团队把订单系统点击了解将mysql迁移到金仓kingbasees后left join意外丢失数据的真实案例,这篇文章详细拆解外连接消除的根因、如何用explain定位问题,并提供sql写法与迁移自查清单,帮你提前拦截90%的报表数据丢失从 mysql 搬到金仓 kingbasees（下面统一叫 kes），结构转完了，sql 也改完了，回归一路过。结果上线第二天，财务找过来说对账报表少了好几个客户。
查了一圈，最后定位到一句看起来很普通的查询，问题出在 left join 上。这篇就把这个坑掰开揉碎讲一下——它怎么产生的、在 kes 里怎么亲手验证，以及上线前怎么把它拦住。
一、先看翻车现场
报表背后的 sql 长这样：
select c.cust_name, o.order_no, o.amount from customers c left join orders o on c.cust_id = o.cust_id where o.status = 'paid';
需求其实挺好懂：把所有客户都列出来，每人带上自己已支付（paid）的订单；有些人可能压根没下过单，或者只下过未支付的，那这种人客户信息也得留着，订单那几列空着就行。
sql 里写得明明白白是 left join，按道理左表一行都漏不掉。可一跑——好家伙，没订单的、只有未支付订单的客户，全没了。
当时开发第一反应是，kes 是不是有 bug。
这里先把结论撂下：真不是 kes 的问题。这条 sql 你原封不动扔到 mysql、oracle 里，一样少这几行。根子在 sql 自己的语义上，只不过迁移那阵子做了回归比对，才把这个一直潜伏的 bug 给照出来。
下面慢慢拆。
二、left join 到底保的是什么
很多人有个下意识的认知，觉得只要 sql 里写了 left join，左表的行就稳了。
其实只对了一半。
left join 那句"左表全保留"的承诺，只认它自己 on 后面那个条件。where 不归它管——where 是等连接做完之后，再对结果做的一次筛选，它分不清什么外连接内连接。
可以这么想：left join 就像食堂打饭，你来了我就给你配菜，没菜可配的也给你个空盘子；where 呢，是门口的保安，不管你盘子里有没有菜，不达标就不放进去。
麻烦就在这——当 where 里冒出来一个专门冲着右表（也就是 orders，会被填 null 的那一侧）去的条件时，那些靠 left join 勉强留下、右表是 null 的行，一算 null = 'paid' 得到的是"未知"，自然就被保安挡外头了。
from customers -- 先把左表拿来 left join orders on ... -- 连一下：左表全留，右表没匹配的补 null where o.status = 'paid' -- 再筛：右表是 null 的行，条件算出来 unknown，被过滤
数据就丢在最后这一步。
三、真凶：右表条件触发了"外连接消除"
这现象有个名字，叫外连接消除（outer join elimination），通俗讲就是外连接被优化器偷偷改写成了内连接。
道理其实挺朴素的。只要 where 里出现一个针对右表、并且天生排斥空值的条件——比如 o.status = 'paid'、o.amount > 0、o.order_id is not null——优化器就琢磨：这一侧反正不可能有 null，有的话早被 where 干掉了，那这 left join 跟 inner join 还有啥区别？
于是它顺手改写了一下：
-- 你写的，看着像外连接 select c.cust_name, o.order_no from customers c left join orders o on c.cust_id = o.cust_id where o.status = 'paid'; -- 优化器眼里的等价形式，其实就是内连接 select c.cust_name, o.order_no from customers c inner join orders o on c.cust_id = o.cust_id where o.status = 'paid';
重点在这：这步改写不动结果，一行不多一行不少。优化器没改你的语义，只是把一个挂着外连接名头、其实早没作用的写法还原成本来面目，顺便让执行计划跑得快点。
所以真相就一句——这几行数据本来就该丢，不是 kes 给弄没的。优化器只不过比你坦白，直接告诉你这 left join 压根没起作用。
想通这个，你也就理解了，为啥同一条 sql 在老库 mysql 里也少数据，只是那会儿数据少、又没人挨个对，就一直没被发现。
四、动手验证
讲道理不如动手。我们在 kes 里建张小表，让数据丢一回给你看，再用 explain 把优化器这步操作逮住。
4.1 先备一桌数据
留意一下 3 号客户"王五"，他名下一笔订单都没有，就是待会儿要消失的那位。
create table customers (
cust_id int primary key,
cust_name varchar(50)
);
create table orders (
order_id int primary key,
cust_id int,
amount numeric(10,2),
status varchar(10)
);
insert into customers values (1,'张三'), (2,'李四'), (3,'王五');
insert into orders values
(101, 1, 100.00, 'paid'),
(102, 1, 50.00, 'unpaid'),
(103, 2, 200.00, 'paid');
-- 王五没订单4.2 同一份数据，三种写法
写法 a，纯 left join，不去过滤右表，客户全在：
select c.cust_name, o.order_no, o.status from customers c left join orders o on c.cust_id = o.cust_id;
cust_name | order_no | status -----------+----------+-------- 张三 | 101 | paid 张三 | 102 | unpaid 李四 | 103 | paid 王五 | (null) | (null)
王五保住了，订单那列给他填 null。
写法 b，把 status='paid' 挪到 where 里，这就是翻车的那个写法：
select c.cust_name, o.order_no, o.status from customers c left join orders o on c.cust_id = o.cust_id where o.status = 'paid';
cust_name | order_no | status -----------+----------+-------- 张三 | 101 | paid 李四 | 103 | paid
王五没了，张三那条未支付的也跟着没了。
写法 c，条件放回 on：
select c.cust_name, o.order_no, o.status
from customers c
left join orders o
on c.cust_id = o.cust_id
and o.status = 'paid';
cust_name | order_no | status -----------+----------+-------- 张三 | 101 | paid 李四 | 103 | paid 王五 | (null) | (null)
王五回来了。这才是"列出所有客户、带上已支付订单"该有的样子。
4.3 用 explain 逮现行
写法 b 到底是不是被改成了内连接？explain 一跑就知道。
explain select c.cust_name, o.order_no from customers c left join orders o on c.cust_id = o.cust_id where o.status = 'paid';
query plan
--------------------------------------------------------------
hash join
hash cond: (c.cust_id = o.cust_id)
-> seq scan on customers c
-> hash
-> seq scan on orders o
filter: (status = 'paid'::text)
看第一行，是 hash join，没有 left。
再对比写法 a（不写 where，外连接还活着）：
explain select c.cust_name, o.order_no from customers c left join orders o on c.cust_id = o.cust_id;
query plan
--------------------------------------------------------------
hash left join
hash cond: (c.cust_id = o.cust_id)
-> seq scan on customers c
-> hash
-> seq scan on orders o
这回是 hash left join，带着 left。
信号其实挺明显：只要发现"我明明写的 left join，计划里却是个没 left 的内连接"，基本就能断定——哪个冲着右表的 where 条件，把外连接给消除掉了。嵌套循环和归并连接同理，nested loop left join 会变成 nested loop，merge left join 会变成 merge join。
4.4 再补一锤：看真实行数
要是觉得看节点名还不够直观，那就上 analyze，看实际跑出来的行数：
explain analyze select c.cust_name, o.order_no from customers c left join orders o on c.cust_id = o.cust_id where o.status = 'paid';
query plan
--------------------------------------------------------------
hash join ( ... ) (actual ... rows=2 ...)
hash cond: (c.cust_id = o.cust_id)
-> seq scan on customers c (actual rows=3 ...)
-> hash
-> seq scan on orders o (actual rows=3 ...)
filter: (status = 'paid'::text)
rows removed by filter: 1
左表明明扫出 3 行，最后连接只吐了 2 行，少掉那一行就是王五。actual rows 一比，丢没丢心里就有数了。
五、报表里最容易翻车的地方：left join 配 count
做报表的同学对这种写法肯定不陌生：left join 接一个 count。需求通常长这样——统计每个客户有几笔已支付订单，没买过的也显示个 0。
条件放 on 的时候，是正常的：
select c.cust_name, count(o.order_id) as paid_cnt
from customers c
left join orders o
on c.cust_id = o.cust_id
and o.status = 'paid'
group by c.cust_name
order by c.cust_name;
cust_name | paid_cnt -----------+---------- 张三 | 1 李四 | 1 王五 | 0
count 数的是右表非空的行，王五没匹配上，自然算 0，没问题。
可一旦又把 status='paid' 顺手塞回 where，王五就又消失了，这回连统计成 0 的资格都没了：
select c.cust_name, count(o.order_id) as paid_cnt from customers c left join orders o on c.cust_id = o.cust_id where o.status = 'paid' group by c.cust_name;
cust_name | paid_cnt -----------+---------- 张三 | 1 李四 | 1
这里有个细节，迁移之后要是发现统计数字对不上，先别急着翻数据——先看看 count(*) 和 count(右表某列) 有没有用混。前者数所有行，后者只数右表非空的那部分，口径完全不一样。
六、迁移到 kes 还要注意的几个坑
where 和 on 这事是标准 sql 的通病，换哪家库都一样。但从 mysql 或者 postgresql 搬到 kes，还有几处差异会把这问题放大，让人觉得 left join 更容易丢数据，得单拎出来说说。
mysql 大小写不敏感，kes 默认敏感
这个踩的人最多。mysql 的字符串列默认走大小写不敏感的排序规则（像 utf8mb4_general_ci 这种），下面这条能匹配上 ‘paid’：
where o.status = 'paid' -- mysql 里能命中 'paid'
kes 默认是大小写敏感的，'paid' = 'paid' 直接就不成立。右表匹配不上，补个 null，再被 where 一挡，整行又没了。修起来有几招：
-- 转小写再比 where lower(o.status) = 'paid' -- 或者用不区分大小写的匹配 where o.status ilike 'paid'
更省心的办法是在迁移那阵就把这类枚举值统一成大写或小写，从根上断了歧义。
隐式类型转换，kes 比 mysql 较真
mysql 在连接条件、where 里对跨类型比较特别宽容，一个 int 列跟字符串 ‘123’ 也能比对上：
-- mysql：a.id 是 int，b.code 是 varchar '123'，照样匹配 from a join b on a.id = b.code
kes 在这上面就较真多了，字符型和数值型混着用，轻的匹配率下降，重的直接报错，表现出来还是"右表匹配不上、数据变少"。稳妥起见，显式把类型对齐：
from a join b on a.id = b.code::int
空串不等于 null
有些从 mysql 迁过来的数据，"没填"的地方存的是空字符串，不是 null。你要是写 where o.remark is null，在 kes 里对空串是不命中的——空串它不是 null。排查的时候得把空串也带上：
where o.remark is null or o.remark = ''
顺带提一下从 oracle 来的 (+)
要是源头是 oracle，kes 是兼容 (+) 外连接写法的。但这符号特别容易写错，多条件的时候每个条件都得加 (+)，还不能跟 or、in 搭一起，搞不好就又变成"本想外连接、结果成了内连接"。迁移的时候建议直接全改成 left join ... on (...)，干净，也好维护。
七、修法：条件别放错地方
口诀就一句：想过滤右表、又想保住左表的，条件放 on；真打算从结果里删掉整行的，才放 where。
条件放 on 是最常用的：
select c.cust_name, o.order_no
from customers c
left join orders o
on c.cust_id = o.cust_id
and o.status = 'paid'
and o.amount >= 100;
右表的筛选逻辑要是比较复杂，就先在子查询里筛干净再连，可读性好很多：
select c.cust_name, t.order_no
from customers c
left join (
select cust_id, order_no
from orders
where status = 'paid' and amount >= 100
) t on c.cust_id = t.cust_id;
还有一种情况，业务上希望"右表是空也算满足条件"，那就得显式把 null 处理一下：
select c.cust_name, o.order_no from customers c left join orders o on c.cust_id = o.cust_id where o.status = 'paid' or o.status is null;
八、上线前的自查清单
迁移的回归流程里，把下面这些事安排上，后面能省掉大量排查时间。
先把所有 left join 过一遍，看 where 里有没有引用右表的列；有的话，确认是不是真打算因为这个条件丢掉左表的行。核心那几条报表 sql，顺手拿 explain 跑一下，只要计划里 left join 变成了不带 left 的内连接，就重点复核。
别只盯着"跑不报错"——同一份数据，迁移前后对核心 sql 做结果比对，行数和抽样内容都得对上。这块最容易被忽略，但也最能提前把问题兜住。
数据层面的几个点也别落下：mysql 那边大小写不敏感的列，在 kes 这边给个明确的大小写策略；join 和 where 里做比较的两边，类型显式对齐，别让隐式转换偷偷改命中率；空串和 null 要摸一遍，确认 is null 不会漏掉空串数据。
聚合那块单独提一下，count(*) 和 count(右表列) 别用混，前者数所有行，后者只数右表非空的部分。要是源头是 oracle，(+) 统一改成标准 left join。
sql 多到一条条看不过来的话，先让脚本把嫌疑大的挑出来，再人工细看：
# 扫一遍代码，把带 left join 的语句都列出来
grep -rnie "left[[:space:]]+(outer[[:space:]]+)?join" \
src/ --include="*.sql" --include="*.xml" --include="*.java"
# mybatis 的 xml 里最爱藏这种 sql，单独盯一下
grep -rnie "left[[:space:]]+(outer[[:space:]]+)?join" \
src/main/resources/mapper/ --include="*.xml"写在最后
说到底，left join 丢数据这事，根子是过滤条件放错了地方——写在了 where 里，又恰好作用在会被填 null 的那一侧，于是 kes 做了外连接消除，把外连接改成了内连接，左表没匹配上的行就跟着没了。
这不是 kes 的锅，标准 sql 就这么定义的，mysql、postgresql、oracle 都一个样，只是迁移时的回归测试把它抖了出来。
排查的时候，explain 是最好用的家伙：连接节点从 hash left join 变成 hash join，就是外连接被消除的信号。
真正的坑，除了 where 和 on，主要集中在大小写敏感、隐式类型转换、空串和 null、还有 (+) 这几样上，按前面那份清单逐个过一遍，基本就稳了。
迁移遇到问题，别上来就怀疑数据库，先 explain 看一眼——多数时候，优化器比咱们的直觉要诚实。
到此这篇关于mysql/postgresql 迁移金仓 kes：left join 丢数据排查与避坑指南的文章就介绍到这了,更多相关mysql/pl 迁移kes left join丢数据排查内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论