Oracle 空字符串、函数、日期、序列兼容问题

1. 对应简历段落

这篇文章对应简历中“负责 Oracle 到 OceanBase 信创迁移中的 SQL 兼容性治理，处理空字符串、常用函数、日期条件、序列主键和 Java 参数绑定差异”等经历。

面试官问这类问题，通常不是想听一张函数替换表，而是想确认你是否知道“语法能跑”和“业务语义一致”之间的差距。Oracle 中空字符串等同 NULL，日期函数和隐式转换很宽松，Sequence 使用方式深入业务代码和存储过程。迁移到 OceanBase MySQL 模式后，如果不系统治理，结果可能出现看似偶发的错账、漏数、查不到、重复主键或索引失效。

比较成熟的表达是：我把兼容问题分为数据语义、SQL 函数、日期范围、主键生成四类。先通过扫描和样本数据识别风险字段，再建立函数映射和改写规范；日期条件尽量从“字段套函数”改成“范围查询”；序列则按业务主键策略统一到数据库兼容序列、应用号段或分布式 ID，并在双写阶段避免冲突。

2. 业务背景

老系统长期运行在 Oracle 上，很多代码默认接受 Oracle 的行为。比如用户未填写手机号，Oracle 表中可能存的是 NULL，也可能是应用传入的空字符串 ''，但在 Oracle 里二者通常都会按 NULL 表现。SQL 中常见：

WHERE mobile IS NULL

迁移到 OceanBase MySQL 模式后，NULL 和 '' 是两个不同值。原来能查出的“未填写手机号客户”，迁移后可能少一部分。

函数差异也非常常见。Oracle SQL 里大量使用 NVL、DECODE、TO_DATE、TO_CHAR、TRUNC、SYSDATE、ADD_MONTHS、MONTHS_BETWEEN、SUBSTR、INSTR、|| 字符串拼接。这些函数有的在目标库中没有，有的名字类似但语义细节不同。

日期问题尤其危险。很多历史 SQL 写成：

WHERE TRUNC(create_time) = TO_DATE(:bizDate, 'yyyy-mm-dd')

这在 Oracle 中很常见，但迁移后如果改成：

WHERE DATE(create_time) = :bizDate

虽然结果可能一致，却会让 create_time 上的索引失效，导致生产慢 SQL。

序列问题也很典型。Oracle 中常用：

SELECT seq_order_id.NEXTVAL FROM dual;

如果迁移时改成自增、雪花 ID 或 OceanBase 兼容序列，需要考虑历史最大值、双写冲突、批量插入、事务回滚和 Java 代码取主键方式。

3. 核心原理

3.1 空字符串与 NULL

Oracle 把零长度字符串视为 NULL，这意味着：

INSERT INTO customer(email) VALUES ('');

在 Oracle 中查询出来往往表现为 NULL。因此：

WHERE email IS NULL

可以匹配未填写邮箱。

而 MySQL 模式下，'' 是空字符串，NULL 是未知值，二者不同：

email IS NULL     -- 只匹配 NULL
email = ''        -- 只匹配空字符串

这会影响查询、唯一索引、分组、Java 判空、JSON 序列化和前端展示。迁移时不能简单把所有 IS NULL 改成 IS NULL OR = ''，因为这可能影响索引选择，也可能改变某些字段本来需要区分空和未填的业务语义。

3.2 函数差异

常见函数映射如下：

NVL(expr1, expr2)              -> IFNULL(expr1, expr2) 或 COALESCE(expr1, expr2)
NVL2(expr, v1, v2)             -> CASE WHEN expr IS NOT NULL THEN v1 ELSE v2 END
DECODE(x, a, b, c)             -> CASE WHEN x = a THEN b ELSE c END
SYSDATE                        -> NOW() 或 CURRENT_TIMESTAMP
TO_DATE(str, fmt)              -> STR_TO_DATE(str, fmt) 或应用层传时间参数
TO_CHAR(date, fmt)             -> DATE_FORMAT(date, fmt)
TRUNC(date)                    -> 日期范围查询，避免对字段套函数
ADD_MONTHS(date, n)            -> DATE_ADD(date, INTERVAL n MONTH)
SUBSTR(str, pos, len)          -> SUBSTRING(str, pos, len)
INSTR(str, sub)                -> INSTR(str, sub) 或 LOCATE(sub, str)
a || b                         -> CONCAT(a, b)

真正要注意的是 null 传播和类型转换。比如 Oracle 的 DECODE 对 NULL 比较有特殊行为，MySQL 的 CASE WHEN x = NULL 永远不是 true，必须写 x IS NULL。再比如字符串拼接中任一字段为 NULL，CONCAT 的结果可能受数据库实现影响，最好显式 COALESCE(field, '')。

3.3 日期条件

日期迁移的原则是：能在应用层完成解析，就不要依赖数据库隐式转换；能用范围查询，就不要对字段套函数。

不推荐：

WHERE DATE(create_time) = :bizDate

推荐：

WHERE create_time >= :startTime
  AND create_time < :endTime

这样既能表达“某一天”的业务语义，也能利用 create_time 索引。

3.4 序列与主键

Oracle Sequence 是独立对象，不依赖表。它的值获取通常不参与事务回滚，即使事务失败，序列值也可能已经消耗。这对业务一般没问题，因为主键只要求唯一，不要求连续。

迁移时有几种选择：

第一，使用 OceanBase 兼容序列，改造成本低，适合保留原 SQL 形态。

第二，改成数据库自增，适合单表简单主键，但要关注分布式环境下自增步长和冲突。

第三，改成应用层分布式 ID 或号段服务，适合多库、多租户、双写和跨系统引用。

无论哪种方式，都要确认历史最大值和新生成值不会重叠。

4. 项目落地

落地时我会先建立兼容性扫描清单。扫描 Mapper XML、存储过程、视图、函数、报表 SQL 和 Java 字符串拼接 SQL，提取高频函数和风险语句。比如：

NVL(
DECODE(
TO_DATE(
TO_CHAR(
TRUNC(
SYSDATE
NEXTVAL
||
IS NULL
= ''

然后按影响面分组处理。

空字符串问题先做字段分级。手机号、证件号、邮箱、推荐人编号这类“未填写”语义明确的字段，统一清洗为 NULL，应用层使用统一的 blank-to-null 处理。备注、扩展字段、外部系统原文这类字段，不轻易清洗，避免破坏历史含义。

函数问题建立改写规范。简单函数可以批量替换，但 DECODE、日期、拼接、空值判断必须人工复核。特别是涉及金额、状态和统计口径的 SQL，要用双跑结果验证。

日期问题统一推动应用层传入 startTime/endTime。例如页面传 bizDate=2026-05-01，Controller 或 Service 转成 [2026-05-01 00:00:00, 2026-05-02 00:00:00)，Mapper 只做范围查询。这样可以减少数据库方言差异。

序列问题则要和主键策略一起治理。如果系统已有统一 ID 服务，就把新增代码逐步迁移到 ID 服务；老 SQL 中暂时保留数据库序列时，要检查序列起始值：

SELECT MAX(order_id) FROM policy_order;

然后让新序列起点大于历史最大值，并预留双写期间的号段空间。

5. 示例 SQL 或流程

空字符串改造前：

SELECT customer_id, customer_name
FROM customer
WHERE email IS NULL;

如果字段规则决定空字符串也表示未填写，可以临时兼容：

SELECT customer_id, customer_name
FROM customer
WHERE email IS NULL OR email = '';

但更推荐迁移前治理数据：

UPDATE customer
SET email = NULL
WHERE email = '';

日期改造前：

SELECT COUNT(*)
FROM policy_order
WHERE TRUNC(create_time) = TO_DATE(:bizDate, 'yyyy-mm-dd');

日期改造后：

SELECT COUNT(*)
FROM policy_order
WHERE create_time >= :startTime
  AND create_time < :endTime;

DECODE 改造前：

SELECT DECODE(status, '1', '有效', '0', '无效', '未知') AS status_name
FROM customer;

改造后：

SELECT CASE
           WHEN status = '1' THEN '有效'
           WHEN status = '0' THEN '无效'
           ELSE '未知'
       END AS status_name
FROM customer;

序列改造示例：

-- Oracle
SELECT seq_policy_order.NEXTVAL FROM dual;

-- 应用层号段服务伪流程
1. Java 服务向 id_segment 表申请 order_id 号段。
2. 本地内存按步长递增发号。
3. 号段耗尽后再次申请。
4. 新旧库双写阶段按不同号段或同一 ID 服务发号，避免冲突。

6. 常见坑

第一个坑是只做函数名替换，不验证 null 语义。NVL 改 IFNULL 大多简单，但 DECODE、拼接、空字符串、NULL 比较经常有隐藏差异。

第二个坑是日期字段套函数。DATE(create_time)、TO_CHAR(create_time) 放在 where 条件左侧会破坏索引使用，高并发列表和报表会明显变慢。

第三个坑是依赖数据库隐式转换。Oracle 对字符串转日期、数字转字符串比较宽松，迁移后可能报错或走错比较规则。Java 侧应使用明确类型参数绑定。

第四个坑是序列起点没校准。新库序列从 1 开始，历史数据最大主键已经很大，插入时会主键冲突。双写阶段尤其危险。

第五个坑是把空字符串全量清洗为 NULL。有些字段业务上确实区分“空文本”和“未设置”，比如备注字段、外部原始报文、扩展属性。要按字段规则治理。

第六个坑是忽略唯一索引差异。NULL 与空字符串在唯一索引中的表现不同，可能导致原来允许的数据迁移后冲突，或者原来冲突的数据迁移后被放过。

7. 面试追问

面试官可能问：Oracle 空字符串为什么容易导致迁移问题？

回答：Oracle 中 '' 通常按 NULL 处理，而 MySQL 模式会区分 '' 和 NULL。因此 IS NULL 查询、唯一约束、Java 判空、分组统计都可能变化。处理方式要按字段语义做数据治理或 SQL 兼容。

面试官可能问：日期函数为什么不要直接改成 DATE(create_time)？

回答：这样会对字段套函数，导致普通 BTree 索引难以利用。更好的写法是应用层算好开始和结束时间，SQL 使用 create_time >= :startTime AND create_time < :endTime。

面试官可能问：DECODE 怎么迁移？

回答：大多数可改为 CASE WHEN。但要注意 NULL 比较，不能写 x = NULL，要写 x IS NULL。复杂 DECODE 要结合业务状态码验证结果。

面试官可能问：序列迁移怎么避免主键冲突？

回答：先查历史最大值，设置新序列或号段起点大于最大值；双写阶段统一由同一 ID 服务发号，或按库分配不同号段；切换前验证序列当前值、表最大值和应用发号策略一致。

8. 推荐回答

可以这样回答：

“兼容性改造我主要分四类处理。第一类是空字符串和 NULL，Oracle 会把空字符串当成 NULL，迁移到 OceanBase MySQL 模式后必须按字段语义治理，不能全局替换。第二类是函数，比如 NVL、DECODE、TO_DATE、TRUNC，简单函数建立映射，复杂函数用 CASE WHEN 或应用层处理并做结果对比。第三类是日期，我把大量 TRUNC(create_time) 改成开始结束时间范围查询，避免索引失效。第四类是序列，迁移前校准历史最大值，双写期间统一 ID 来源，避免主键冲突。上线前通过 SQL 扫描和新旧结果对比，把语法兼容、语义一致和性能影响一起验证。”

9. 延伸学习路线

第一，整理 Oracle 与 MySQL/OceanBase 常用函数差异，重点看空值、日期、字符串、数字精度。

第二，学习数据库三值逻辑，理解 NULL 与普通值比较为什么不是 true 或 false。

第三，系统学习时间处理，掌握半开区间 [start, end)、时区、日期格式化和索引友好的写法。

第四，研究主键生成方案，包括 Sequence、自增、UUID、雪花 ID、号段模式，以及它们在分布式系统中的取舍。

第五，做兼容性改造时建立自动化扫描和双跑验证，不依赖人工肉眼检查。