1. 先把这两个参数的分工划清楚上周帮一个朋友看一个老系统的迁移问题导数脚本跑到一半直接崩了报错是ERROR 1292 (22007): Incorrect date value: 0000-00-00 for column birthday at row 1。他们的源库是跑了七八年的老版本实例sql_mode 几乎是空的目标库是新建的默认就把NO_ZERO_DATE和NO_ZERO_IN_DATE带上了。两边对同一个值的态度完全相反数据自然过不去。这类问题我在不同项目里见过太多次而且每次的解释都很模糊——大家只知道关掉就好了却说不清关掉的到底是一个还是两个以及关掉之后会顺手放开哪些别的行为。NO_ZERO_DATE和NO_ZERO_IN_DATE属于 MySQL 的sql_mode体系专门用来约束日期类型列的取值合法性。它们的名字长得像双胞胎很多人第一反应是这俩是一个东西吧实际上分工完全不同一个守的是整条日期是零一个守的是日期里有一部分是零。搞混这两个参数轻则改完参数发现问题还在重则把一整批脏数据洗成了另一个样子的脏数据。这篇东西写给正在做版本升级、数据迁移、或者被这两个报错卡住的同行从行为边界、验证脚本、清理顺序、参数落地一路讲到底新手能照着抄老手能补上几个盲点。1.1 一句话说透一个管整条为零一个管部分为零先给结论后面再展开。NO_ZERO_DATE管的是0000-00-00这种年月日全部是零的完整零日期以及带时间部分的0000-00-00 00:00:00。你可以把它理解成这条记录有没有日期这件事本身就不成立。NO_ZERO_IN_DATE管的是年份不为零、但月份或日期部分是零的情况典型值就是2023-00-15、2023-01-00、2023-00-00。这类值的特征是年份是有的但是月或者日没填全。用生活化的说法NO_ZERO_DATE检查的是这个人有没有生日NO_ZERO_IN_DATE检查的是这个人生日写了年份但月份那一栏空着。两条规则可以单独开关互不依赖。你把NO_ZERO_DATE关掉2023-00-15依然会被NO_ZERO_IN_DATE拦住反过来把NO_ZERO_IN_DATE关掉0000-00-00照样过不去。这里有个真正容易出错的地方0000-00-00只归NO_ZERO_DATE管NO_ZERO_IN_DATE对它不生效。官方文档在描述NO_ZERO_IN_DATE时明确写的是年份部分不为零、而月或日为 0 的日期也就是说年份为零的那种情形压根不在它的职责范围内。我见过有人在只开了NO_ZERO_IN_DATE的环境里试图插入0000-00-00发现能插进去然后就断定这个参数没用——完全是把职责认错了。至于0000-01-01这种年份为零但月日完整的组合它落在两个参数的中间地带文档里没有单独列一条说明。我的建议是把它当作非法值处理别去赌具体小版本的行为后面第 3 节的探针脚本就是用来把你自己那套环境的行为一次性摸清的。1.2 为什么它们是半开关必须搭着严格模式用这是整篇内容里最关键的认知也是最容易翻车的地方NO_ZERO_DATE和NO_ZERO_IN_DATE单独存在时不会阻止任何写入只会产生一条警告。MySQL 的日期校验是两段式的。第一段判断这个值合不合法由NO_ZERO_DATE、NO_ZERO_IN_DATE、ALLOW_INVALID_DATES这几个模式值负责第二段判断遇到不合法的值要怎么办由STRICT_TRANS_TABLES或STRICT_ALL_TABLES决定。只有两段都触发最终才会抛出错误、中止语句。如果严格模式没开MySQL 的处理方式是记一条 warning然后把这个值静默改写成零日期存进去。所以会出现一种很迷惑的现象明明SHOW VARIABLES LIKE sql_mode里能看到NO_ZERO_DATE但业务插入0000-00-00依然成功只是SHOW WARNINGS里多了一行。排查的人对着参数看半天找不到原因问题就出在严格模式被关掉了。严格模式还有一个细节值得单独拎出来。STRICT_TRANS_TABLES对支持事务的表是彻底严格的任何一行出错整条语句回滚但对非事务表行为没有那么干脆——如果错误出现在第一行语句中止如果错误出现在后面的行前面的行已经写进去了后面的行继续以警告方式处理。也就是说在 MyISAM 上一次批量插入可能只写进去一部分。我见过有团队因为这个原因数据表里出现了一半合法一半零日期的混乱状态。STRICT_ALL_TABLES则是所有表一律严格出错即中断代价是批量写入时的失败成本更高。参数组合遇到0000-00-00遇到2023-00-15无严格模式、无这两个参数静默接受静默接受无严格模式、开着两个参数接受 warning接受 warning严格模式、无这两个参数静默接受零日期是特例不受严格模式管辖拒绝报 1292严格模式 NO_ZERO_DATE拒绝报 1292拒绝报 1292严格模式 NO_ZERO_IN_DATE静默接受拒绝报 1292表格里第三行是个反直觉的点0000-00-00在只有严格模式、没有NO_ZERO_DATE的时候是能存进去的。因为 MySQL 把完整零日期当成一个合法的特殊值来处理它不参与常规的日期合法性校验。这个特性坑过不少人——升级到高版本以为万事大吉结果严格模式开着零日期照样往里塞直到有一天要做日期区间查询才发现数据是坏的。还有一个常被拉来当救兵的参数ALLOW_INVALID_DATES它放开的是2004-04-31这种月份日期超出当月天数的值也就是格式合法但语义不存在的日期。它不放开零值部分别指望用它来解决零日期问题。1.3 各版本默认值差异速查版本默认值的差异是这类问题在迁移时集中爆发的根本原因。我整理了一份对照方便你判断自己手上那两套库为什么行为不一致。版本默认sql_mode是否含NO_ZERO_DATE/NO_ZERO_IN_DATE默认是否含严格模式5.5 及更早不含不含sql_mode默认为空5.6不含不含5.7含含STRICT_TRANS_TABLES8.0含含STRICT_TRANS_TABLES5.6 到 5.7 是一个明显的分水岭。5.7 把ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_ENGINE_SUBSTITUTION一并设为默认这几乎是 MySQL 历史上对老应用兼容性冲击最大的一次默认值调整。很多 5.5、5.6 时代的应用升到 5.7 之后集中爆出一批日期问题源头就在这里。8.0 的默认值和 5.7 基本一致只是去掉了已经移除的NO_AUTO_CREATE_USER。另外官方文档对NO_ZERO_DATE、NO_ZERO_IN_DATE、ERROR_FOR_DIVISION_BY_ZERO这几个值给出过提示大意是建议把它们和严格模式一起使用未来这些检查会内化为严格模式本身的固有行为、不再作为独立的开关存在。这个说法在不同版本里措辞略有不同但传达的方向是一致的别把希望寄托在以后这个参数会被删掉所以不用管上脏数据该清还是得清。顺便提一个极易混淆的名字NO_AUTO_VALUE_ON_ZERO。它管的是自增列的零值处理跟日期一点关系没有。但因为它名字里同样带NO和ZERO在mysqldump生成的脚本文件头部经常出现很多人扫一眼就以为这不就是那个日期参数吗然后在错误的方向上排查了半天。看到ZERO先确认它修饰的是日期还是自增。2. 四种典型报错现场还原参数本身没什么复杂的真正耗时间的是从一条报错反推到是哪个参数在起作用。下面这四种场景是我这些年遇到频率最高的把它们都认全了日常排查基本够用。2.1 建表就失败ERROR 1067 Invalid default value这是最早期、最好定位的一种。你写了一句很普通的建表语句给日期列加了个历史遗留的默认值CREATE TABLE user_profile ( id INT NOT NULL AUTO_INCREMENT, nickname VARCHAR(64) DEFAULT NULL, birthday DATE NOT NULL DEFAULT 0000-00-00, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;执行下去直接返回ERROR 1067 (42000): Invalid default value for birthday原因就是NO_ZERO_DATE加上严格模式在建表阶段就对默认值做了合法性检查。注意这个检查不只作用于新建表ALTER TABLE ... ADD COLUMN、MODIFY COLUMN同样会触发。有些团队在做分库分表扩列的时候被打了个措手不及明明历史表里就有这个默认值新建一张同构的表却死活建不出来。应对这类报错正确做法是把默认值改成NULL或者一个语义上站得住的真实日期而不是去关参数。因为建表阶段能被拦下说明这是一个新写的定义不存在历史数据包袱没有任何理由继续沿用零日期。如果确实受限于某个第三方组件的硬性要求必须保留这个默认值那也建议改成1970-01-01这类明确的时间起点并在字段注释里写清楚业务上表示未填写。用哨兵值替代零日期至少能让后续的日期函数计算不出错。2.2 导数失败ERROR 1292 Incorrect date value数据迁移时最常见的一条ERROR 1292 (22007): Incorrect date value: 0000-00-00 for column birthday at row 1这里有个非常隐蔽的陷阱同一个.sql文件用命令行客户端导入可能成功用应用代码逐条写却失败。原因是导出工具比如mysqldump在生成的脚本头部通常会插入一行会话级设置把sql_mode临时改掉通常是设成只含NO_AUTO_VALUE_ON_ZERO的极简组合目的是保证自增列的零值能原样恢复。这一改顺带把NO_ZERO_DATE也给去了于是脚本里的零日期畅通无阻。而应用代码走的是普通连接继承的是服务端默认sql_mode自然就撞墙。我排查这类环境不一致问题的固定动作是先把出问题的那份导出文件头部 50 行打出来看一眼确认里面有没有SET SQL_MODE之类的语句。这一步能省掉大量无谓的猜测。另一个高频原因是导数工具本身的参数。很多工具有--force、忽略错误继续这类选项开了之后错误被吞掉表面上导入成功实际上是跳过了几百行。所以导数完成后一定要做行数比对和零日期扫描别只看退出码。2.3 应用层读数据抛异常这一类严格来说不是 MySQL 报的错但它和这两个参数紧密相关。零日期在库里存着没人管某天应用升级了数据库驱动读取时直接抛java.sql.SQLException: Value 0000-00-00 can not be represented as java.sql.Date或者java.sql.SQLException: Cannot convert value 0000-00-00 00:00:00 from column 5 to TIMESTAMP.本质是驱动层做了严格映射发现这个值无法转换成语言原生的日期对象。这类问题通常不是全量爆发的而是单点、偶发因为只有踩到那条脏数据的用户才会崩监控上看起来像幽灵 bug。处理思路有两条。短期可以在连接串上配置零日期的转换策略让驱动把它映射成null或者抛出一个可控的默认值先止血。但我必须强调这只是止血——因为在参数保持默认的高版本环境里新写入根本进不来零日期能读到的都是历史遗留而只要历史遗留还在它就会像地雷一样不定期引爆。真正该做的是把历史数据清干净然后把这个连接参数也一起删掉避免它掩盖新的问题。2.4 分区表落不进分区ERROR 1526这个场景相对小众但杀伤力极大。按时间做 RANGE 分区的表零日期不属于任何一个分区的区间ERROR 1526 (HY000): Table has no partition for value 0更难受的是这类表往往出现在日志、流水、账单这类核心业务上而零日期的来源通常是业务时间没取到代码里默认写了个空。一旦写入失败链路直接断比静默存进去的危害还大——至少静默存进去不会影响主流程。处理这类问题的顺序不能错先把分区边界和MAXVALUE兜底分区检查一遍确认有没有兜底再把写入侧的时间取值逻辑修掉保证拿不到时间的时候要么报错、要么写当前时间绝不写零日期。只改参数不改代码等于把雪崩点从数据库挪到了业务层。3. 自己动手做一份行为探针网上的结论经常互相打架因为不同小版本、不同参数组合下行为确实有细微差异。与其背结论不如花十分钟在自己环境里跑一遍。我维护了一份探针脚本每次接触新环境的第一件事就是把它跑一遍把结果记在环境文档里后面所有排查都以这份实测为准。3.1 探针脚本的设计思路脚本要覆盖四个维度一是四种典型日期值完整零日期、月为零、日为零、年份为零二是三种时间类型DATE、DATETIME、TIMESTAMP的差异三是多种sql_mode组合四是有没有IGNORE修饰符。为什么要单独测TIMESTAMP因为它和DATE、DATETIME的行为不完全一致。TIMESTAMP的范围本身有限且受explicit_defaults_for_timestamp影响——这个参数在 5.7 默认是OFF8.0 默认是ON直接影响TIMESTAMP列的隐式默认值和NULL处理。建表时我会显式写上NULL DEFAULT NULL把隐式行为固定下来避免测试结果被这个参数干扰。至于IGNORE修饰符它是很多人不知道的后门INSERT IGNORE会把严格模式下的这类错误降级成警告并继续写入。这意味着如果业务代码里到处是INSERT IGNORE你就算把参数配得再严零日期照样能进库。这一点在第 5 节还会再提。3.2 完整可执行脚本下面这份可以直接粘贴执行注意在一个测试实例上跑脚本会建一个临时库。DROP DATABASE IF EXISTS probe_zero_date; CREATE DATABASE probe_zero_date DEFAULT CHARSET utf8mb4; USE probe_zero_date; CREATE TABLE t_probe ( id INT NOT NULL AUTO_INCREMENT, d DATE NULL DEFAULT NULL, dt DATETIME NULL DEFAULT NULL, ts TIMESTAMP NULL DEFAULT NULL, PRIMARY KEY (id) ) ENGINEInnoDB; SET old_mode SESSION.sql_mode; -- 场景一完全关闭 SET SESSION sql_mode ; INSERT INTO t_probe (d, dt, ts) VALUES (0000-00-00, 0000-00-00 00:00:00, 0000-00-00 00:00:00), (2023-00-15, 2023-00-15 10:00:00, 2023-00-15 10:00:00), (2023-01-00, 2023-01-00 10:00:00, 2023-01-00 10:00:00), (0000-01-01, 0000-01-01 10:00:00, 0000-01-01 10:00:00); SHOW WARNINGS; SELECT mode-empty AS scene, * FROM t_probe; TRUNCATE TABLE t_probe; -- 场景二只有两个零日期参数没有严格模式 SET SESSION sql_mode NO_ZERO_DATE,NO_ZERO_IN_DATE; INSERT INTO t_probe (d, dt, ts) VALUES (0000-00-00, 0000-00-00 00:00:00, 0000-00-00 00:00:00), (2023-00-15, 2023-00-15 10:00:00, 2023-00-15 10:00:00), (2023-01-00, 2023-01-00 10:00:00, 2023-01-00 10:00:00); SHOW WARNINGS; SELECT mode-zero-only AS scene, * FROM t_probe; TRUNCATE TABLE t_probe; -- 场景三只有严格模式没有零日期参数 SET SESSION sql_mode STRICT_TRANS_TABLES; INSERT INTO t_probe (d, dt, ts) VALUES (0000-00-00, 0000-00-00 00:00:00, 0000-00-00 00:00:00), (2023-00-15, 2023-00-15 10:00:00, 2023-00-15 10:00:00); SHOW WARNINGS; SELECT mode-strict-only AS scene, * FROM t_probe; TRUNCATE TABLE t_probe; -- 场景四三者全开 SET SESSION sql_mode STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE; INSERT INTO t_probe (d, dt, ts) VALUES (0000-00-00, 0000-00-00 00:00:00, 0000-00-00 00:00:00); SHOW WARNINGS; INSERT INTO t_probe (d) VALUES (2023-00-15); SHOW WARNINGS; -- 场景五带上 IGNORE INSERT IGNORE INTO t_probe (d, dt) VALUES (0000-00-00, 0000-00-00 00:00:00); SHOW WARNINGS; SELECT mode-strict-ignore AS scene, * FROM t_probe; -- 场景六建表默认值 SET SESSION sql_mode STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE; CREATE TABLE t_default_probe ( id INT NOT NULL AUTO_INCREMENT, d DATE NOT NULL DEFAULT 0000-00-00, PRIMARY KEY (id) ) ENGINEInnoDB; SET SESSION sql_mode old_mode;3.3 结果怎么读跑完脚本把自己环境的结果和下面这几条对照。我按最容易看错的顺序排列。第一场景一和场景二的区别只在于有没有 warning。两边的数据都能进去2023-00-15进去之后大概率被存成了2023-00-00因为 MySQL 会把非法部分归零。这就是为什么很多人以为参数没生效——数据确实进去了只是被改写了。这一步一定要看SHOW WARNINGS的输出不要只看SELECT结果。第二场景三最能暴露问题。只有严格模式时2023-00-15会被拒绝但0000-00-00会不会过答案通常是可以。原因前面说过完整零日期被视为特殊值不走常规合法性校验严格模式对它没有管辖权。这条如果和你预期不一样说明你之前对严格模式的理解需要修正。第三场景四的第一次插入应该报1292第二次插入也应该报1292但报错的触发参数不同——第一次是NO_ZERO_DATE第二次是NO_ZERO_IN_DATE。你可以通过只启用其中一个参数来交叉验证是哪个在拦。第四场景五会给出一个让你意外的结果IGNORE一加错误变警告零日期照样落库。这是个非常重要的观察很多参数明明配了却没用的案例根子就在这里。第五场景六应该直接报1067。如果不报说明你的会话级sql_mode设置没生效去确认一下是不是被连接池的初始化 SQL 覆盖了。至于0000-01-01脚本里也放进去了跑完看看你自己环境里它是被接受、被改写还是被拒绝记在环境文档里就行。这种边缘组合我倾向于不去做统一结论因为不同小版本的处理细节确实存在差异实测比背结论靠谱。4. 生产环境落地的四步处理法知道参数怎么工作是第一步怎么在一个正在跑的系统上把它处理干净才是真正的工程问题。我的经验是顺序不能乱一定是先看数据、再想清洗、然后改代码、最后才动参数。反过来做就是在给自己制造事故。4.1 第一步全库扫一遍零日期动手之前先摸清底数。第一步永远是量化到底有多少张表、多少个日期列、多少行脏数据。先枚举所有日期类型的列SELECT table_schema, table_name, column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE data_type IN (date, datetime, timestamp) AND table_schema NOT IN (mysql, sys, information_schema, performance_schema) ORDER BY table_schema, table_name, ordinal_position;拿到清单后直接生成扫描语句省得手写几十条SELECT CONCAT( SELECT , table_schema, ., table_name, AS tbl, , column_name, AS col, COUNT(*) AS bad_rows FROM , table_schema, ., table_name, WHERE , column_name, IS NOT NULL AND (YEAR(, column_name, ) 0 OR MONTH(, column_name, ) 0 OR DAYOFMONTH(, column_name, ) 0) UNION ALL ) AS scan_sql FROM information_schema.columns WHERE data_type IN (date, datetime, timestamp) AND table_schema NOT IN (mysql, sys, information_schema, performance_schema);把生成的一堆UNION ALL语句拼起来执行就能一次性拿到每张表每个列的脏数据量。这里有个技巧值得单独说判空日期不要用WHERE d 0000-00-00。在启用了NO_ZERO_DATE的严格模式下字面量0000-00-00在解析阶段就可能直接报 1292扫描脚本自己先崩了什么也扫不出来。用YEAR()、MONTH()、DAYOFMONTH()这三个函数判断是更稳的路子——它们作用在列上而不是字面量上而且对0000-00-00统一返回 0天然就是零日期的判据。另外DAYOFMONTH比DAY更不容易和关键字打架。还有一种更保守的写法是WHERE CAST(d AS CHAR) LIKE 0000-00-00%把值当字符串比完全绕开日期语义。缺点是没法走索引全表扫一遍大表上做好心理准备。扫描时别只看零日期顺手也扫一遍格式合法但语义不存在的值比如2023-02-30。这类值在ALLOW_INVALID_DATES开着的时候能进去清理时可以一起处理。4.2 第二步想清楚数据怎么洗扫出数据之后最要命的决策来了这些零日期应该变成什么。我见过三种做法各有代价我把踩过的坑摊开说。置为NULL是我最推荐的方案语义上最准确——没填就是NULL不是某个特定的日期。前提有两个列本身允许NULL不允许就得先ALTER COLUMN并且应用层所有读这个字段的地方都能处理NULL。第二点是真正的成本所在尤其是那些做了大量日期运算的老代码一个NULL传进去聚合函数、日期加减、ORDER BY全都可能出问题。所以这个方案落地前一定要拿代码扫描工具把字段的引用点全捞出来过一遍。改成哨兵值比如1970-01-01或1000-01-01好处是应用层几乎不用改坏处是它引入了新的语义歧义。业务上怎么区分真实发生在一九七零年的时间和这里表示没填大概率区分不了。而且哨兵值会污染统计算平均年龄、算时间跨度、做同比环比这些值全都会参与运算把报表算歪。迁到独立字段或独立表比如加一列birthday_unknown标记或者把这类记录抽到一张附属表。这是最干净的方案代价是改造量最大适合那些无论如何都不能接受数据失真的核心系统。我的实际做法通常是分表分级核心业务表用NULL方案老老实实改代码日志、流水、中间表这类可以容忍失真的直接改成哨兵值但必须在表注释里写清楚语义。绝对不要用的哨兵值是9999-12-31我见过有人这么干结果所有BETWEEN区间查询都会把这条记录捞出来ORDER BY date DESC的时候它永远排在第一条报表和列表页全乱套。清洗语句本身也需要注意执行方式。大表上直接UPDATE会锁很久建议按主键分批UPDATE order_record SET paid_at NULL WHERE id BETWEEN 1 AND 50000 AND paid_at IS NOT NULL AND (YEAR(paid_at) 0 OR MONTH(paid_at) 0 OR DAYOFMONTH(paid_at) 0);每批之间 sleep 一下把压力摊开。别忘了把UPDATE前的数据备份出来SELECT ... INTO OUTFILE或者导一份只有主键和该列的临时表都行万一洗错了还有退路。4.3 第三步改代码与建表规范数据是存量代码是增量。存量清完不清增量过两个月又长回来了。代码侧要做三件事。第一统一时间取值入口。我发现零日期的产生源头高度集中要么是 DTO 字段没赋值导致默认空串被写进去要么是 ORM 把null转成了0000-00-00要么是从上游系统接了个空字符串直接透传。把这几个入口堵住能解决八九成的新增问题。具体做法是在写入前做一次校验拿不到合法日期就抛异常或者走兜底逻辑不要静默写零。第二清理INSERT IGNORE和类似的宽容修饰符。前面探针脚本已经证明了IGNORE会把零日期错误降级成警告参数配得再严也白搭。同理ON DUPLICATE KEY UPDATE里的日期赋值、批量插入的容错逻辑都要检查一遍。这类为了稳而加的容错往往是数据质量的最大漏洞。第三更新建表规范。日期列一律DEFAULT NULL不允许DEFAULT 0000-00-00业务上表达必填的日期列用NOT NULL但不给零日期默认值让写入方必须显式传值。规范写进 DDL 审核清单新表建的时候自动检查比事后清理便宜太多。建表规范里还建议加一条日期列不要用TIMESTAMP表达业务时间除非明确需要自动时区转换。TIMESTAMP有 2038 年的上限也有explicit_defaults_for_timestamp带来的隐式行为差异跨版本迁移时特别容易出问题。业务上的发生时间、创建时间用DATETIME更稳只有确实需要数据库自动维护行更新时间的时候才用TIMESTAMP。4.4 第四步参数怎么调才算调对终于到参数了。我得先把态度摆出来这两个参数不建议关尤其是长期关。它们是最后一道防线关了之后零日期可以自由进出等于把数据质量的守门人撤了。前面三步做扎实绝大多数情况下根本不需要动参数。但现实里总有做不到的时候——老系统改不动、供应商组件不配合、迁移窗口只有几个小时。这时候按下面的层次来从影响最小往最大排。会话级临时调整是最安全的影响范围只有当前连接SET SESSION sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION;导数脚本、一次性修复脚本用这个跑完连接断开就恢复不会污染其他业务。缺点是容易忘写脚本的时候要养成开头设、结尾还原的习惯。全局动态调整影响所有新建连接已有连接不受影响SET GLOBAL sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION;这个操作在大多数版本上不需要重启立即生效。但要注意两点一是连接池里的存量连接还带着旧值需要滚动重启应用才能真正统一二是重启实例后会丢失必须配合配置文件。写进配置文件才是永久的[mysqld] sql_mode STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION改完重启生效。这里有个我踩过的坑配置文件里的值会完全替换默认值不是追加。所以你要是只写sql_mode NO_ENGINE_SUBSTITUTION那严格模式、ONLY_FULL_GROUP_BY全都没了比原来更危险。每次改这里都要把完整的值写全并且在改完之后用SELECT GLOBAL.sql_mode复核一遍。还有一个平台层面的注意事项如果用的是托管数据库服务参数通常通过参数组管理改完之后要确认变更是否已经下发到实例有些服务需要显式重启或者等待几分钟生效。变更前后各查一次GLOBAL.sql_mode做对比别凭感觉。最后再强调一遍不要用SET sql_mode 来解决问题。这一步确实能让所有报错消失但你同时关掉了整数溢出检查、字符串超长截断检查、除零检查等等一整套保护。我见过有团队为了绕过一个日期报错把sql_mode清空三个月后发现一批金额字段被静默截断损失比日期问题大得多。为了一个字段的兼容性放弃整套约束性价比极低。5. 速查表与几条用事故换来的经验5.1 问题速查表把高频问题整理成表遇到报错先对号入座能省掉一轮摸索。报错/现象大概率原因快速验证处理方向ERROR 1067 Invalid default value建表默认值写了0000-00-00且开着NO_ZERO_DATE 严格模式SELECT SESSION.sql_mode默认值改NULL别关参数ERROR 1292 Incorrect date value插入0000-00-00或YYYY-00-DD看报错里的值形态定是哪个参数清数据 修写入逻辑插入成功但SHOW WARNINGS有内容数据被改写没开严格模式两个参数只产生警告SHOW WARNINGS紧跟插入语句补上STRICT_TRANS_TABLES参数明明开着却拦不住存在INSERT IGNORE、或连接池初始化 SQL 覆盖了sql_modeSELECT SESSION.sql_mode在应用连接上执行清理宽容修饰符检查连接池配置.sql文件能导入应用写入失败导出文件头部设置了会话级sql_mode看导出文件前 50 行导出与导入两端统一策略驱动抛can not be represented as java.sql.Date读到了历史零日期直接SELECT该行短期配转换策略长期清数据ERROR 1526 Table has no partition for value 0分区表无兜底分区写入零日期查分区定义加MAXVALUE兜底 修写入逻辑ALTER TABLE改列失败修改默认值或类型时触发校验看报错列名先清该列数据再改结构5.2 那些文档里不会写的细节最后再倒几条纯经验都是文档里查不到、但实战中反复验证过的东西。导数脚本里养成显式声明会话sql_mode的习惯。不要依赖服务端默认值也不要依赖导出文件里带的设置。脚本开头显式写一句SET SESSION sql_mode ...把行为钉死。这样脚本在开发、测试、生产三套环境跑出来的结果完全一致省掉无数为什么本地能过线上不能过的扯皮。跨版本迁移时先把两端的sql_mode拉出来 diff 一下而不是等报错。这个动作只需要两分钟能提前发现九成的日期类迁移问题。我现在的迁移检查清单第一条就是这个位置排在数据量评估之前。TIMESTAMP列在 5.7 和 8.0 上的隐式行为差异会伪装成日期问题。5.7 默认explicit_defaults_for_timestamp OFF第一列TIMESTAMP会自动带上DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP8.0 默认ON就没有这个隐式行为了。升级后如果发现有列的自动更新突然不工作了或者NULL写进去的表现变了先查这个参数别往零日期上想。扫描零日期时不要在大表上用LIKE %0000%。我试过一次几千万行的表跑了四十多分钟还没出结果因为前置通配符导致全表扫加逐行字符串匹配。用YEAR()/MONTH()/DAYOFMONTH()那套写法或者干脆按主键分片扫效率差着数量级。清洗数据前先跑一遍SELECT ... FOR UPDATE确认锁的范围尤其是清洗和业务写入可能并发的表。我吃过一次亏深夜清洗一张订单表没注意同一时间有个定时任务在写这张表结果清洗脚本把定时任务刚写进去的一条正常记录的日期也改成了NULL因为那条记录的月份恰好是 0——当然这是我清洗条件写错了但并发确实放大了问题。后来我改成清洗前先按时间范围框定只处理清洗开始之前的老数据。最后一条也是最实在的一条把日期列禁止零日期写进团队的 DDL 规范并且用自动化工具在 CI 阶段卡住。人工把关靠不住参数配置又容易被临时改动只有工具在合并前拦一道才是真的拦住了。规范落地之后再去看NO_ZERO_DATE和NO_ZERO_IN_DATE你会发现它们从经常要处理的麻烦变成了偶尔触发一次的安全网——这才是这两个参数本该扮演的角色。