MySQL严格模式报错 Field doesn‘t have a default value 的排查与根治

做后端的兄弟十有八九都被这句报错砸过脸Field xxx doesnt have a default value。早上一上线就收到测试的截图说订单保存失败打开日志一看整条INSERT被人拦腰截断亏就亏在这个“默认值”上。这个错看着吓人实际上就三种东西凑一起才触发字段不允许为空、字段没有默认值、MySQL开了严格模式。但这背后牵扯出的问题可不少表结构设计是否规范、ORM映射是否完整、数据同步链路是否对齐甚至新旧版本的MySQL行为差异都能在这里翻车。本文就围绕这个报错把原理、复现、止血方案、永久性解法连同一线踩坑的经历都讲透适合刚入门的开发也适合被这类问题反复折磨的DBA和数据工程师。1. 这个报错的本质一次严格模式下的“安全检查”1.1 三个条件缺一不可先给结论这条报错的完整触发条件是三个条件同时成立目标表的某个字段定义了NOT NULL不允许为空该字段没有显式指定DEFAULT默认值MySQL的sql_mode中开启了严格模式STRICT_TRANS_TABLES或STRICT_ALL_TABLES当你执行的INSERT语句恰好没给这个字段传值MySQL的严格模式就开始较真你没给值我又不能存NULL那这一行数据到底怎么落库人家干脆报错终止不猜你的意图。把这三个条件拆开看很多同学就明白了。你可能会疑惑“我明明没有写NOT NULL啊为什么还会报错”这里有个常见混淆点如果你连字段类型后面的NULL/NOT NULL都没写默认是允许NULL的这时不填值会存成NULL不会报错但一旦你显式写了NOT NULL甚至通过工具生成了NOT NULL又漏写默认值就会掉进这个坑。这里我举个生活化的类比这就好比公司门禁系统每个人的工牌都必须有“姓名”这一栏表结构规定不能空着可发卡模板里又没有预填“姓名”的默认值你拿着一个没写名字的工牌去刷卡门禁不会放你进去而是直接提示“缺少姓名”。报错不是系统坏了是它在执行你定下的规则。1.2 严格模式的前世今生sql_mode这个东西很多新手根本不知道它的存在直到被这条报错教育一顿。在MySQL 5.6及更早的版本中默认的sql_mode是空字符串是一种“什么都能凑合”的宽松状态。你给一个NOT NULL字段不传值MySQL不会报错而是自动存入一个隐式的默认值。对于整数类型存0对于字符串类型存空字符串对时间类型存0000-00-00。听起来很美好对吧副作用也很大程序里的BUG很容易被掩盖你以为数据保存成功了实际上存进去一堆默认的假值等到统计报表、金额计算的时候才发现数据全乱了。MySQL 5.7以后默认sql_mode带上了一个叫STRICT_TRANS_TABLES的选手。只要这个开关在对于InnoDB这类支持事务的表任何非法的、截断的、缺失的数据都会引发报错并回滚该条语句。当初很多人从5.6升级到5.7、8.0业务代码一行没改上线后突然大面积报这个错原因就在这。可以理解为MySQL从“你给啥我存啥”变成了“你给啥我得先验货”。严格模式固然让开发初期难受但它拦下了大量潜在的脏数据。正确的态度不是想着关掉它而是把表结构设计干净。2. 排查流程从日志到表结构四步定位2.1 第一步翻日志确认报错上下文日志里通常长这样### Error querying database. Cause: com.mysql.cj.jdbc.exceptions.MariaDBException: Field status doesnt have a default value关键是先看是哪个字段叫“status”然后看是哪条SQL触发的最好把PREPARE打印出来的完整SQL捞出来。很多时候是类似INSERT INTO t_order (order_no, customer_id, amount, create_time) VALUES (?, ?, ?, NOW())你会发现语句里压根没有status这个字段而表的status列又是NOT NULL、没默认值于是严格模式毫不留情地开罚单。如果日志是MyBatis打的可以看到 Parameters:后的参数列表和SQL对照一下很容易定位缺了哪个字段。2.2 第二步确认表结构定位到表名之后直接看结构SHOW CREATE TABLE t_order\G注意结果里字段定义部分重点检查三样东西有没有NOT NULL、有没有DEFAULT、字段类型是什么。我建议把NOT NULL且无DEFAULT的字段列个清单这就是潜在雷区。用一条查询就能筛SELECT TABLE_NAME, COLUMN_NAME, IS_NULLABLE, COLUMN_DEFAULT, DATA_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA your_db AND IS_NULLABLE NO AND COLUMN_DEFAULT IS NULL AND COLUMN_NAME NOT IN (id);这条SQL能一次性查出库里所有“不允许为空且没有默认值”的字段建议在测试库先跑一遍熟悉一下排查问题的时候效率极高。2.3 第三步确认当前sql_mode登录MySQL执行SELECT global.sql_mode; SELECT session.sql_mode;如果结果里带有STRICT_TRANS_TABLES或STRICT_ALL_TABLES恭喜严格模式是开着的。这两个值的区别在于后者对MyISAM这类非事务引擎的表也强制报错前者只对事务表严格。2.4 第四步检查代码里的插入路径表结构确认是雷区、sql_mode严格模式也在接下来就是找哪条代码路径没有给这个字段赋值。常见坑点实体类里该字段没有配置MyBatis的insert语句是手写的、漏了字段字段在代码里被TableField(updateStrategy FieldStrategy.NOT_NULL)这类配置搞成了“非空才更新”插入时值恰好为null框架干脆不往INSERT语句里带同步任务从数据源拉取后映射器把字段名写错了源字段status没对上目标表status建议直接在代码里搜索对应字段名然后顺着调用链检查赋值逻辑。如果实在查不到哪条SQL触发的可以打开MySQL通用日志临时抓包SET GLOBAL general_log ON; SET GLOBAL log_output TABLE;然后查mysql.general_log表看具体是哪个客户端IP、什么时间发的哪条INSERT。这个小技巧在“找不到是哪来的SQL”时特别好用。注意抓完后记得关掉不然日志涨飞了。3. 解决方案对比改表、改SQL还是改配置3.1 方案一给字段加上合理的默认值这是我最推荐的根治手段。既然是字段没有默认值那就补上。语法很简单ALTER TABLE t_order MODIFY COLUMN status tinyint NOT NULL DEFAULT 0 COMMENT 订单状态0待支付1已支付2已完成;或者只改默认值不碰其他定义ALTER TABLE t_order ALTER COLUMN status SET DEFAULT 0;选择哪个默认值是个技术活。整数状态字段一般给业务意义上的“初始状态”比如订单刚创建是0字符串字段如果是类型标记可以给时间字段建议给DEFAULT CURRENT_TIMESTAMP但注意ON UPDATE CURRENT_TIMESTAMP用于记录更新时间那种场景别乱加。有个容易翻车的点给已有表加默认值前先想清楚存量数据的“语义”。如果你的status列之前靠程序每条都显式写入现在补默认值0历史数据不受影响因为默认值只对后续INSERT生效。但如果业务上有的记录其实没有“初始状态”概念硬给一个0反而是埋雷。设计是妥协的艺术不能为了过报错随便拍一个值。3.2 方案二显式补全INSERT语句的字段如果这个字段在业务流程里一定是“每行都必须有明确值”那就别绕开直接在INSERT语句里带上。比如INSERT INTO t_order (order_no, customer_id, status, create_time) VALUES (?, ?, ?, NOW());程序代码里就补一个setStatus(...)的赋值。这个方案的好处是不改表结构风险最小坏处是容易漏你得保证所有写入路径都补齐包括批处理、导入任务、同步任务。否则过一阵子又冒出来报错还换了个字段名。3.3 方案三修改sql_mode去掉严格模式这是很多人上网一搜就采用的“速效救心丸”实际操作很简单SET GLOBAL sql_mode ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION; SET SESSION sql_mode ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION;注意两点第一SET GLOBAL只对新连接生效当前连接和已存在的连接池连接不会立刻生效所以最好SET SESSION也执行一遍第二这个修改不会持久化MySQL一重启就打回原形要彻底关掉得改my.cnf或my.ini里[mysqld]段落[mysqld] sql_mode ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION这里我劝大家一句别急着关严格模式。严格模式能拦住三种很恶心的数据0000-00-00的日期、超出字段长度的字符串被静默截断、数值类型塞进空字符串。你把严格模式关了系统是不会再报这个错了可数据埋雷的隐患会转移到业务深处等到几个月后对账不平再回头查成本高了十倍不止。我个人只在生产事故“先恢复再说”的千钧一发时刻才会临时关掉业务恢复稳定后一定逆操作加回来。3.4 三个方案怎么选来直接给判断逻辑场景推荐方案理由字段自带初始状态业务上不传也合理方案一补默认值一劳永逸不改代码字段每行必须有明确业务值方案二SQL补字段保证业务语义真实老系统一批表历史包袱重短期没空逐张改方案三临时改sql_mode止血用事后再补表结构全公司多个库统一规范组合改表固定sql_mode模板治本4. 生产实战四个高频翻车场景实录4.1 老表加列引发的“批量任务全挂”有个真实案例印象很深。某次业务加需求要给订单表加一列platform_source开发写变更SQL时为了“看起来规范”直接写了platform_source varchar(20) NOT NULL忘记给默认值也忘记当时的存量脚本有没有跑。第二天凌晨定时批量脚本开始INSERT新订单每条都报Field platform_source doesnt have a default value大批任务重试失败监控告警炸了一宿。事后复盘发现变更脚本在测试库执行通过了因为测试库的sql_mode是宽松的到了生产库生产库严格模式开着问题立刻现形。这是个很典型的“测试环境没对齐sql_mode”的坑。我的建议是所有线上变更脚本必须自己先过一遍严格模式的库最好直接拿生产备份导出的测试库来验证。4.2 数据同步链路字段不齐用Canal订阅binlog、用DataX做离线同步的场景同样容易被这个报错卡住。Canal的机制是把上游MySQL的binlog事件解析之后写入下游如果你下游库的表结构比上游多了某个NOT NULL无默认值字段同步客户端在映射时没有给这个新字段赋值就会报错任务直接卡死。排查这类问题不要一上来就怀疑组件坏了。先对比源表和目标表的SHOW CREATE TABLE把两颗表的字段差异列出来再看同步任务里的字段映射配置确认每一个NOT NULL字段都能从上游拿到值。如果是下游自己加的字段最省事的做法就是给个默认值同步链路立刻恢复。4.3 ORM框架“弃字段”行为Java生态最常中招的是MyBatis-Plus这种“字段非空才插入”的动态SQL。场景是这样的表里新增了remark字段NOT NULL没默认值实体类也加了字段但业务代码在某条分支没有setRemark(...)MP生成的INSERT语句里干脆没有这一列严格模式立刻报错。有意思的是如果表里字段允许NULL这种“没赋值就不生成SQL列”的行为丝毫不会暴露问题一旦字段是NOT NULL隐藏BUG立即现形。我的排查建议全局搜一遍实体类的TableField注解看有没有insertStrategy、updateStrategy配置同时重点检查新字段是否所有写入路径都赋值了。4.4 DDL迁移工具生成的“残缺”变更很多团队用Flyway或Liquibase管理数据库版本。在给表加字段时如果脚本模板是通用的ALTER TABLE t_user ADD COLUMN age int NOT NULL;这句在旧数据上执行可能会有问题因为MySQL对存量行会填入隐式默认值0但在严格模式 无默认值的情况下行为会随版本和sql_mode变化。有些版本允许执行只是存量行悄悄填了0有些配置下DDL直接失败。环境差异很大所以Flyway脚本里我强烈建议显式写默认值ALTER TABLE t_user ADD COLUMN age int NOT NULL DEFAULT 0 COMMENT 年龄;这样无论新旧版本、宽松严格模式DDL都能稳定执行。迁移工具本身不会帮你“补”默认值它只会按你给的DDL去执行因此写脚本的人必须自己想清楚每个新列在存量数据上应该是什么值。5. 事前防御表结构规范与变更管理5.1 把“NOT NULL必须有DEFAULT”写进规范最省心的办法是让报错永远不出现。我们在团队里定了一条硬规则凡是NOT NULL字段必须同时提供DEFAULT值。时间字段给DEFAULT CURRENT_TIMESTAMP整型给业务初始值字符串给或固定枚举值。哪怕业务上“每条都必须显式填”也照样给个占位默认值兜底毕竟兜底永远比报错强。这条规则不只是嘴上说说我在评审表结构DDL时会逐列过一遍在编写建表SQL时也习惯用模板开头CREATE TABLE t_example ( id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 主键, gmt_create datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, gmt_modified datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 修改时间, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT示例表;这样的建表模板天然免疫这个报错。字段的默认值最好是“有业务含义的值”而不是为了应付报错随便写的占位符。一个合理的默认值能让代码更健壮哪怕程序偶尔漏赋值数据也不算离谱。5.2 sql_mode的基线管理sql_mode是数据库服务级配置应该在初始化、版本升级时统一设置。我在自建或云上MySQL都会把sql_mode写死成ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION这是MySQL 8.0的默认模板语义清晰、安全性高。如果是公司统一运维建议在CMDB或配置管理工具里记录sql_mode基线任何实例不一致都能对比出来。测试环境与生产环境严格对齐这样就不会出现“开发库不报错、生产库报错”的尴尬。5.3 变更脚本的“过三关”自查我给自己定的DBA变更自查清单是这样第一关每一条ADD COLUMN后面是否都带DEFAULT不带就枪毙第二关每一条MODIFY COLUMN是否改变了字段的NULL属性如果改成NOT NULL必须确认没有破坏存量数据第三关变更是否在严格模式测试库上真实执行过不能只在宽松环境过这三关看起来繁琐但能拦住九成以上线上事故。毕竟这条报错虽然不难解但出现的时间点往往在最忙的发布窗口一次事故耽误的可是整个迭代节奏。6. 常见问题速查表与最后一点私货问题现象直接原因最快止血根治手段INSERT报Field xxx doesnt have a default valueNOT NULL字段无默认值严格模式临时去掉STRICT_TRANS_TABLES补DEFAULT或补SQL字段手写SQL一直报另一个库不报两个库sql_mode不同对齐sql_mode基线统一配置管理新加列后线上任务全挂变更脚本漏DEFAULT紧急ALTER补默认值变更自查三关同步组件任务卡死目标表多出NOT NULL无默认值列给列加默认值映射配置补齐字段MyBatis插入不报错但值变默认严格模式已关隐式默认值生效无这是隐患重新开启严格模式最后分享一点容易被忽视的经验这类报错的“故障面”往往不是字段本身而是字段背后跑批脚本、同步任务、ORM配置这些链路。当你看到这条报错别只盯着SQL改两行花十分钟顺着数据流看一遍把这行数据从哪里来、经过哪些层、到哪个表落库都走通。很多看起来偶发的报错其实是在提醒你某条链路的表结构已经和业务语义脱节了。把字段默认值补齐不难难的是建立“每次变更都对全链路负责”的习惯。我在实际项目中把这个报错当作“表结构健康度的警报器”——只要它出现就一定说明某个写入口对表结构的定义不完整。处理掉它远不只是修一条INSERT而是把整条数据生产链路上的隐患顺带清干净。这大概就是排查MySQL报错最让人着迷的地方一个看似细小的错误信息最后总能逼着你把整条链路看得更透。