新旧 SQL 与存储过程结果对比工具设计

1. 对应简历段落

这篇文章对应简历中“负责 Oracle 到 OceanBase 迁移中新旧 SQL、存储过程和批处理结果对比工具建设，通过自动化双跑、字段归一化、差异报告和回归验证保障迁移正确性”等经历。

这类经历在面试里很加分，因为它说明你不是靠人工点页面验证迁移，而是把迁移风险工具化。面试官通常会追问：如何采集 SQL？如何绑定参数？如何比较结果集？排序不稳定怎么办？金额和日期精度怎么处理？存储过程有副作用怎么双跑？差异报告如何定位原因？

推荐回答的主线是：我把验证对象分为查询 SQL、报表 SQL、批处理和存储过程；对无副作用查询做自动双跑，对有副作用逻辑使用隔离环境和快照数据；对比时不只比较行数，还比较主键集合、字段值、排序、金额精度、日期格式和空值语义；最后输出可追踪差异报告，帮助开发快速定位是 SQL 改写、数据同步、函数差异还是排序问题。

2. 业务背景

数据库迁移最怕的不是 SQL 报错，而是 SQL 不报错但结果变了。比如：

1. 空字符串与 NULL 差异导致少查客户。
2. ROWNUM 到 LIMIT 改造后分页顺序不稳定。
3. CONNECT BY 到递归 CTE 后少了一层机构。
4. 日期函数改写后边界时间漏算。
5. DECODE 改 CASE WHEN 后 NULL 状态映射错误。
6. 存储过程 Java 化后批处理金额汇总差一分钱。

这些问题靠人工页面测试很难覆盖。老系统有成百上千条 Mapper SQL、报表 SQL、存储过程和定时任务。迁移项目时间紧，如果每条都让测试手工构造数据、点页面、看截图，不现实，也不可靠。

因此需要设计新旧结果对比工具。它的目标不是替代所有测试，而是建立迁移正确性的自动化底座：同一份参数，在 Oracle 执行旧 SQL，在 OceanBase 执行新 SQL，把结果归一化后比较，并输出差异。

3. 核心原理

结果对比工具的核心由五部分组成：用例采集、执行引擎、归一化、差异比较、报告输出。

用例采集要解决“测什么”。来源可以是 Mapper XML、线上 SQL 日志、接口录制、人工配置的高风险 SQL、存储过程清单。每个用例至少包含：

caseId
业务模块
旧 SQL 或旧过程
新 SQL 或新过程
参数列表
主键字段
排序字段
字段类型规则
容忍误差
是否有副作用

执行引擎要解决“怎么跑”。对普通查询，可以直接连接新旧数据源执行。对分页查询，要跑首页、中间页、尾页和边界页。对存储过程或批任务，要在隔离库、影子表或事务回滚模式下执行，避免污染数据。

归一化要解决“怎么比”。不同数据库返回的类型、格式和空值可能不同。比如 Oracle DATE 可能带时分秒，OceanBase 返回 DATETIME；金额可能是 BigDecimal scale 不同；空字符串和 NULL 语义不同。工具要在比较前统一处理。

差异比较要解决“哪里不一样”。不能只比较 JSON 字符串。更稳的是按主键对齐行，再逐字段比较；没有主键的聚合报表，则按维度字段作为业务键；分页结果还要比较顺序。

报告输出要解决“如何定位”。报告要告诉开发：哪条用例失败，参数是什么，少了哪些行，多了哪些行，哪些字段不同，旧值和新值分别是什么，可能原因是什么。

4. 项目落地

我会优先用 Java 实现对比工具，因为项目本身是 Java/MyBatis 技术栈，方便复用数据源、类型处理和 SQL 参数绑定逻辑。

整体设计可以分为几个模块。

CaseLoader 负责读取用例。用例可以先用 YAML 或 JSON 配置，后续再接入 Mapper 扫描。配置示例：

caseId: policy_page_query_001
module: policy
oldSql: oracle/policy_page.sql
newSql: ob/policy_page.sql
params:
  orgId: 10001
  status: PAID
  startTime: 2026-05-01 00:00:00
  endTime: 2026-05-02 00:00:00
keyColumns:
  - policy_no
orderColumns:
  - create_time
  - policy_no
normalizers:
  premium: decimal:2
  create_time: datetime:second
  email: blankAsNull

SqlExecutor 负责执行 SQL。它要支持命名参数或 MyBatis 参数，设置超时时间、最大行数和 fetch size，避免一条错误 SQL 把工具跑死。

ResultNormalizer 负责归一化。比如：

NULL 和空字符串按字段规则处理
BigDecimal 统一 scale
日期统一到秒或毫秒
字符串 trim 规则按字段配置
字段名统一大小写

ResultComparator 负责比较。普通明细按主键对齐，报表按维度键对齐，分页额外比较顺序。比较结果分为：

ONLY_IN_OLD：旧库有，新库没有
ONLY_IN_NEW：新库有，旧库没有
VALUE_DIFF：主键相同，字段值不同
ORDER_DIFF：集合相同，顺序不同
COUNT_DIFF：总数不同
EXEC_ERROR：执行错误
TIMEOUT：执行超时

ReportWriter 输出 HTML、Markdown 或 Excel 报告。报告中保留 SQL、参数、耗时、差异摘要和样例差异。

5. 示例 SQL 或流程

普通查询对比流程：

1. 读取 case 配置。
2. 连接 Oracle 数据源执行 oldSql。
3. 连接 OceanBase 数据源执行 newSql。
4. 将 ResultSet 转为 List<Map<String, Object>>。
5. 对字段名、日期、金额、空值做归一化。
6. 根据 keyColumns 对齐行。
7. 比较缺失行、新增行和字段差异。
8. 输出报告。

伪代码：

CompareResult compare(SqlCase sqlCase) {
    List<Row> oldRows = oldExecutor.query(sqlCase.oldSql(), sqlCase.params());
    List<Row> newRows = newExecutor.query(sqlCase.newSql(), sqlCase.params());

    List<Row> normalizedOld = normalizer.normalize(oldRows, sqlCase.rules());
    List<Row> normalizedNew = normalizer.normalize(newRows, sqlCase.rules());

    return comparator.compare(
            normalizedOld,
            normalizedNew,
            sqlCase.keyColumns(),
            sqlCase.orderColumns()
    );
}

分页 SQL 对比不仅要看某一页，还要看边界：

1. 查询 count。
2. 生成 pageNo：1、2、中间页、最后一页、超出最后一页。
3. 分别执行新旧分页 SQL。
4. 比较每页主键列表和顺序。
5. 检查上一页最后一条与下一页第一条是否重复或遗漏。

存储过程对比更复杂。如果过程会写表，不能直接在生产双跑。可以采用：

1. 准备同一份快照数据。
2. Oracle 在旧影子库执行过程。
3. OceanBase 或 Java 化逻辑在新影子库执行。
4. 对比过程影响的结果表、日志表、汇总表。
5. 对比行数、金额、状态、错误码和执行日志。
6. 每次执行后重置快照，保证用例可重复。

6. 常见坑

第一个坑是没有主键或业务键。结果集无序时直接逐行比较，会把顺序差异误判为值差异。必须按主键、唯一键或报表维度对齐。

第二个坑是忽略排序。分页和树查询即使集合一样，顺序不同也可能影响业务展示，所以要单独标记 ORDER_DIFF。

第三个坑是金额精度处理粗糙。金额字段不能用 double 比较，要用 BigDecimal，并按业务规则设置 scale 和舍入方式。

第四个坑是日期边界不一致。Oracle 与新库的日期类型、时区、毫秒精度可能不同。比较前要统一精度，并专门测试零点、月底、年底、闰年。

第五个坑是空值归一化过度。不是所有字段都能把空字符串等同 NULL，要按字段配置。

第六个坑是只用测试参数。迁移验证应该尽量采集生产真实参数脱敏后回放，覆盖高频和边界场景。

第七个坑是存储过程双跑污染数据。有副作用逻辑必须使用影子库、事务回滚、快照重置或专门的对账表。

7. 面试追问

面试官可能问：结果集没有顺序怎么比较？

回答：普通查询按主键或业务唯一键对齐；报表按维度字段组成业务键；如果确实没有键，就需要在用例中定义排序字段或把它标记为不可稳定比较。不能直接按返回顺序逐行比。

面试官可能问：分页结果怎么验证？

回答：固定查询条件，比较 count、首页、中间页、尾页、边界页的主键序列和顺序。还要验证排序字段是否唯一，不唯一时追加主键排序。

面试官可能问：存储过程有写操作怎么双跑？

回答：不能在生产直接双跑。要使用同源快照数据，在隔离环境分别执行旧过程和新逻辑，然后对比受影响表和汇总结果。执行后重置快照，保证可重复。

面试官可能问：差异如何定位原因？

回答：工具输出差异类型和样例。比如只在旧库存在可能是条件改写或数据同步问题；字段差异可能是函数、空值、日期精度；顺序差异多半是排序不稳定；执行错误则是语法或参数类型问题。

8. 推荐回答

可以这样回答：

“迁移验证我做了一个新旧 SQL 对比工具。用例来源包括 Mapper、慢 SQL、高风险报表和存储过程清单。工具用同一组参数分别查 Oracle 和 OceanBase，把结果集做字段名、日期、金额、空值归一化，再按主键或业务维度对齐比较。比较结果不只看行数，还区分旧库独有、新库独有、字段值不同、顺序不同和执行异常。分页会专门比较首页、中间页、尾页的主键序列；存储过程这种有副作用的逻辑则在影子库用快照数据执行，对比结果表和汇总表。最后输出差异报告，帮助定位是 SQL 改写、数据同步、函数差异还是排序问题。”

9. 延伸学习路线

第一，学习数据对账设计，掌握行数、主键集合、字段级 hash、金额汇总和抽样明细对比。

第二，学习 JDBC 类型处理，理解 ResultSet 中日期、数字、字符串和 null 的映射。

第三，学习测试数据管理，包括快照、脱敏、回放、影子库和幂等重置。

第四，学习 MyBatis SQL 解析与参数绑定，为自动采集 Mapper 用例打基础。

第五，学习差异报告产品化，把对比结果做成可搜索、可过滤、可追踪的问题清单。