MySQL到金仓：零日期与宽松模式数据清洗——历史系统非法日期迁移的检测、修复与回退

每日一句正能量

“有一种欣喜叫触底反弹，有一种快乐叫柳暗花明。”
最深的谷底，往往也是转折的开始。真正的欣喜若狂，不是来自顺境的锦上添花，而是来自绝境后的绝地反击。

主题：非法日期与 SQL 模式 / MySQL → KingbaseES / 历史系统迁移
重点：零日期、部分零日期、宽松sql_mode、检测 SQL、清洗规则、数据校验、增量迁移与回退
适用场景：老 CRM、ERP、会员系统、订单平台、历史数据仓库等长期使用 MySQL 宽松模式的系统。

1. 背景与问题：真正难迁的不是'0000-00-00'，而是它背后的业务含义

老 MySQL 系统中，经常可以看到：

0000-00-00 0000-00-00 00:00:00 2019-00-15 2019-02-31 1970-01-01 9999-12-31

这些值看上去都“可疑”，但它们不是同一种问题。

MySQL 官方文档长期支持一种相对宽松的日期处理方式：在特定sql_mode下，可以允许零月、零日，甚至把'0000-00-00'作为“dummy date”；启用ALLOW_INVALID_DATES后，日期只做有限检查。NO_ZERO_DATE和NO_ZERO_IN_DATE则用于约束这类数据。citeturn862513search8turn862513search4

MySQL 8.4 默认 SQL mode 已包含：

STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE

但历史系统不一定一直使用默认配置。很多十年前上线的系统可能曾经关闭严格模式，或者应用连接初始化时覆盖了 Sessionsql_mode。citeturn862513search2turn862513search4

因此一个老表里出现：

birthday='0000-00-00'

可能代表：

不知道生日

也可能代表：

前端没有填写，旧代码自动塞了默认值

还可能代表：

数据导入脚本失败后被MySQL宽松模式兜成零日期

如果迁移时统一：

0000-00-00→NULL

技术上看似合理，业务上却未必正确。

所以本文的核心原则是：

先识别“异常日期的业务语义”，再决定如何清洗。

2. 环境与数据：先盘点 sql_mode，再盘点日期列

示例环境：

源库：MySQL 5.7/8.0 历史混合环境 目标：KingbaseES V9 MySQL兼容模式/标准日期类型 系统：十年以上历史 CRM + 订单平台 数据量：约12亿行 异常日期分布：多个业务库 迁移方式：全量 + CDC

源表：

CREATETABLElegacy_customer(idBIGINTPRIMARYKEY,birthdayDATENOTNULLDEFAULT'0000-00-00',register_timeDATETIMENOTNULL,last_login_timeDATETIME);

另一个老表：

CREATETABLElegacy_order(order_idBIGINTPRIMARYKEY,paid_atDATETIMENOTNULLDEFAULT'0000-00-00 00:00:00');

2.1 第一步不是扫数据，而是记录 SQL mode

先执行：

SELECT@@GLOBAL.sql_mode,@@SESSION.sql_mode;

如果系统有：

多主 多实例 读写分离 连接池初始化SQL

每个写入节点都要记录。

MySQL 的sql_mode是 Global/Session 可配置项，因此“数据库全局模式”不一定等于某个应用连接真实使用的模式。citeturn862513search4

2.2 重点关注这些模式

STRICT_TRANS_TABLES STRICT_ALL_TABLES NO_ZERO_DATE NO_ZERO_IN_DATE ALLOW_INVALID_DATES

MySQL 官方 FAQ 把启用STRICT_TRANS_TABLES、STRICT_ALL_TABLES或TRADITIONAL视为严格模式；关闭严格模式时，某些不合法或缺失值可能按隐式默认值处理，而不是直接报错。

这意味着：

历史脏数据往往不是偶然，而是数据库配置和应用写入方式共同形成的。

2.3 KingbaseES兼容模式也需要确认

KingbaseES 提供 MySQL 兼容模式，并有sql_mode等兼容参数；官方兼容文档说明 MySQL 模式支持大量 MySQL 数据类型和 SQL 语法。

同时 KingbaseES 的标准DATE类型要求输入可被解释为合法日期值，官方文档推荐无歧义 ISO 格式：

YYYY-MM-DD

并明确说明日期解析还可能受DateStyle影响。

迁移设计不应该依赖：

“目标兼容模式也许能接住零日期”

更可靠的是：

在迁移层把非法日期清理成合法、可解释的数据，再进入目标业务表。

3. 复现过程：为什么“一刀切转 NULL”不够专业

3.1 零日期：可能是未知值

例如：

birthday='0000-00-00'

如果业务确认为：

用户未填写生日

最合理目标通常是：

birthday = NULL

同时如果业务必须区分：

未填写

和：

已填写但系统丢失

还需要：

birthday_unknown=true

不能只靠 NULL 承载所有语义。

3.2 零时间：可能代表“尚未发生”

例如：

paid_at='0000-00-00 00:00:00'

很多旧订单系统用它表示：

尚未支付

如果改成：

paid_at=NULL

通常是合理的，但最好同时让：

payment_status='UNPAID'

成为真正业务状态。

这样以后查询：

WHEREpaid_at='0000-00-00 00:00:00'

就可以逐步替换为：

WHEREpayment_status='UNPAID'

这是一次数据语义修复，而不仅是数据库兼容。

3.3 部分零日期：不能猜

MySQL 文档明确提到历史上可以允许：

2010-00-01 2010-01-00

这样的零月或零日形式，NO_ZERO_IN_DATE用来限制它们。citeturn862513search8

假设：

contract_date='2019-00-15'

你不能自动改成：

2019-01-15

因为没有任何证据表明 0 月代表 1 月。

正确做法：

隔离 + 人工或业务规则复核

必要时拆成：

known_year=2019 known_day=15 month_unknown=true

而不是伪造完整日期。

3.4 非法自然日：更不能自动纠偏

例如：

2019-02-31

如果启用了ALLOW_INVALID_DATES，MySQL 只做有限日期检查，因此历史系统可能存下这类值。

迁移时自动：

2019-02-31 → 2019-02-28

是非常危险的。

除非：

有原始业务单据 或 应用代码明确的纠正规则

否则应该进入隔离表。

3.5 合法日期不等于真实业务日期

例如：

1970-01-01 1900-01-01 9999-12-31

它们都是合法日期。

但历史系统常拿它们当：

未设置 最小值 永久有效 无截止日期

所以检测 SQL 不能只找“语法非法日期”。

还要找：

异常高频合法日期

然后回查：

代码 默认值 产品规则 历史文档

再决定是否清洗。

4. 方案实施：建立“原值—规则—目标值”三段式清洗链路

4.1 第一步：把日期列分类

建议做一张清单：

schema table column type nullable default zero_date_count partial_zero_count sentinel_count business_owner cleanup_rule

风险等级：

L1：明确零日期=未知 L2：明确零日期=未发生 L3：合法哨兵日期 L4：部分零日期 L5：非法自然日

优先自动处理：

L1/L2

人工复核：

L3/L4/L5

4.2 第二步：不要直接在源生产表原地 UPDATE

错误方式：

UPDATElegacy_customerSETbirthday=NULLWHEREbirthday='0000-00-00';

这种做法的问题：

无法恢复原值 无法证明清了多少 CDC会产生大量更新 可能影响线上业务逻辑

更推荐：

迁移 staging 层清洗

例如原始字段先作为文本：

birthday_raw

进入中间层。

然后：

valid date → 转DATE 0000-00-00 → 根据规则NULL 非法日期 → quarantine

这样源库不动，风险最低。

4.3 第三步：每条自动清洗必须可审计

建立：

date_cleanup_audit

字段：

source_table source_pk source_column source_value_raw target_value rule_id batch_id created_at

例如：

source=0000-00-00 rule=ZERO_DATE_TO_NULL_UNKNOWN target=NULL

以后业务问：

“这个生日为什么变成 NULL？”

可以追踪到具体规则，而不是回答：

迁移脚本统一改的

4.4 第四步：模糊数据进入隔离表

migration_invalid_date_quarantine

保存：

batch_id source_table source_pk source_column source_value_raw reason_code review_status

原因码：

ZERO_MONTH ZERO_DAY INVALID_CALENDAR_DATE SENTINEL_REVIEW AMBIGUOUS_BUSINESS_MEANING

目标业务主表只装：

合法 或 经过明确规则清洗

的数据。

这样不会为了“迁移完成率100%”把错误值硬塞到新库。

4.5 第五步：零日期转换成 NULL 时同步修改约束

源：

birthdayDATENOTNULLDEFAULT'0000-00-00'

如果业务已经决定：

未知生日 → NULL

目标必须允许：

birthdayDATENULL

否则清洗规则和 DDL 冲突。

所以迁移不是：

只改数据

而是：

数据语义 + 列约束 + 应用代码

一起改。

4.6 第六步：把“未发生”从日期值迁到状态字段

例如：

paid_at=0000-00-00

建议：

paid_at=NULL payment_status='UNPAID'

查询从：

WHEREpaid_at='0000-00-00 00:00:00'

改成：

WHEREpayment_status='UNPAID'

这会明显降低未来数据库迁移和数据分析歧义。

4.7 第七步：全量和CDC必须用同一套清洗库

这是非常关键的一点。

全量脚本：

0000-00-00 → NULL

但 CDC 实时同步如果还是：

原值直接写目标

切流前就会再次出现不一致。

所以应该把：

normalize_date(source_value, rule_id)

做成统一转换库。

全量：

调用它

CDC：

也调用它

目标应用新写入：

直接禁止非法日期

三条链路必须一致。

4.8 第八步：新系统要收紧输入，不要把历史兼容问题继续带过去

MySQL 8.4 默认已启用严格和零日期限制相关模式。

迁移后的系统应该做到：

应用层参数校验 + 数据库合法日期类型 + 禁止零日期约定

否则你今天清完：

1000万条

明天新业务又继续写：

0000-00-00

迁移治理等于白做。

5. 结果对比：清洗验收必须做到“数量闭合”

清洗前：

zero_date=800万 partial_zero=20万 invalid_calendar=5万 sentinel_review=100万

清洗后不能只说：

目标导入成功

而应该做到数学闭合：

源异常总量 = 自动置NULL + 规则修复 + 隔离待审 + 明确保留

例如：

825万异常 = 780万置NULL + 5万修复 + 40万隔离

每一条都能解释。

5.1 最低校验指标

零日期数量

source zero count

目标 NULL 数量

target null count

隔离数量

quarantine count

修复数量

repaired count

规则分布

rule_id → count

5.2 按业务维度分桶

例如生日：

按用户注册年份 地区 渠道

统计零日期比例。

如果某一年：

90%都是0000-00-00

可能说明那一时期产品根本没采集生日，而不是数据坏了。

这类分析可以帮助确定：

NULL

才是最合理语义。

5.3 业务计算回归

重点验证：

年龄 账龄 保修期 过期判断 合同有效期 日/月报表

例如旧逻辑：

DATEDIFF(CURDATE(),birthday)

遇到零日期可能产生特殊行为。

清洗成 NULL 后：

结果可能变成NULL

应用报表必须相应调整。

5.4 示例结果模板

以上是验收模板示例，不是本文声称的生产数据。

6. 风险与复盘：最危险的不是清不掉，而是清错了

6.1 风险一：把合法哨兵值误删

例如：

9999-12-31

有些系统明确表示：

永久有效

如果统一改 NULL，查询：

WHEREexpiry_date>=CURRENT_DATE

语义会变化。

因此合法哨兵日期必须先问业务。

6.2 风险二：把零日期和未知状态混为一谈

unknown not happened not collected not applicable

四种状态都可能被历史系统塞成：

0000-00-00

迁移时如果全部转 NULL，至少要评估是否需要额外状态字段。

6.3 风险三：源库不同Session的sql_mode不同

应用 A：

STRICT

应用 B：

ALLOW_INVALID_DATES

会造成同一张表写入质量不同。

所以只看：

@@GLOBAL.sql_mode

不够。

要查：

应用连接初始化 连接池配置 数据库代理

6.4 风险四：在线直接UPDATE导致CDC风暴

千万级零日期：

UPDATE...

会带来：

redo/binlog 锁 复制延迟 CDC洪峰

因此更推荐迁移 staging 层清洗，而不是源生产表一次性原地修。

6.5 风险五：日期字符串解析受格式影响

KingbaseES 官方 DATE 文档说明日期输入可能受DateStyle影响，因此推荐：

YYYY-MM-DD

这类无歧义 ISO 格式。

迁移文件不要使用：

03/04/2020

这种格式。

6.6 风险六：MySQL兼容模式不是继续保留脏数据的理由

KingbaseES MySQL 兼容模式的目标是降低迁移成本，官方文档也明确强调兼容数据类型、SQL 语法和常见生态能力。
但：

兼容

不应该被理解成：

继续保留历史上所有不合理数据习惯

对于零日期这类典型技术债，迁移窗口反而是最适合治理的时候。

回退方案：一定保留“原始值证据”

最推荐：

raw value + target value + rule_id + batch_id

一起保存。

如果发现：

某规则错误

可以：

按rule_id+batch_id找出全部受影响记录

回退。

双列过渡

例如：

birthday_raw birthday

灰度期：

应用读birthday 迁移审计保留birthday_raw

如果问题：

切回旧字段/旧库

回退触发条件

异常数量不闭合 业务报表差异 > 0 生日/账龄等计算错误 错误规则命中量异常 隔离数据超预期 CDC出现新零日期

回退动作

1. 停止当前清洗批次 2. 固化batch_id 3. 找出该批次所有audit记录 4. 恢复raw值或切回旧读取路径 5. 修正规则 6. 小批次重跑 7. 重新完成数量闭合验证

最终复盘

MySQL 到 KingbaseES 的零日期迁移，本质上是一次数据质量治理。

完整流程应该是：

识别sql_mode → 检测异常日期 → 识别业务语义 → 规则清洗 → 模糊数据隔离 → 目标严格落库 → 数量闭合 → 应用回归 → 可追溯回退

如果只记住一句话：

0000-00-00不是一个日期问题，而是一个“历史系统曾经不知道该填什么”的业务语义问题。

真正专业的迁移，不是把它换成另一个合法日期，而是把“未知、未发生、无效、待确认”这些含义重新表达清楚。

附录 A：源库检测 SQL

SELECT@@GLOBAL.sql_mode,@@SESSION.sql_mode;SELECTCOUNT(*)FROMlegacy_customerWHEREbirthday='0000-00-00';SELECTCOUNT(*)FROMlegacy_orderWHEREpaid_at='0000-00-00 00:00:00';

附录 B：建议清洗映射

0000-00-00 + 业务=未知 → NULL 0000-00-00 + 业务=未发生 → NULL + status YYYY-00-DD → quarantine 非法自然日 → quarantine 合法哨兵日期 → business review

附录 C：审计表

CREATETABLEdate_cleanup_audit(table_nameVARCHAR(128),pk_valueVARCHAR(128),column_nameVARCHAR(128),source_value_rawVARCHAR(64),target_valueDATE,rule_idVARCHAR(64),batch_idVARCHAR(64),created_atTIMESTAMP);

附录 D：最低验收清单

[ ] GLOBAL sql_mode已记录 [ ] SESSION sql_mode已核对 [ ] DATE/DATETIME/TIMESTAMP列已盘点 [ ] zero date已统计 [ ] partial-zero已统计 [ ] invalid calendar date已统计 [ ] sentinel date已统计 [ ] 每条规则已有业务Owner确认 [ ] raw值已保留 [ ] quarantine表已建立 [ ] 全量和CDC使用同一清洗规则 [ ] 异常数量已闭合 [ ] 业务报表回归通过 [ ] 应用已禁止新写零日期 [ ] 回退脚本已演练

转载自：https://blog.csdn.net/u014727709/article/details/163728745
欢迎 👍点赞✍评论⭐收藏，欢迎指正