【MySQL实践】一个隐蔽的问题导致 Field ‘xxx‘ doesn‘t have a default value

1.背景

最后在公司的后台系统上遇到了一个线上问题，是在插入某个表数据的时候出现了数据库的插入异常，即：java.sql.SQLException: Field 'xxx' doesn't have a default value，这其实是一个比较常见的异常，一般在字段不能为null时，如果没有显式的指定对应字段的值就会抛出这个异常。

但是今天遇到的这个异常原因还不是这样的，实际原因有点隐蔽，所以在这里记录一下今天遇到的这个问题。

2.问题处理

下面以一张Demo表为例来进行说明，有一个可以为null的字段与一个不能为null的字段。

create table demo
(
    id     bigint auto_increment comment '主键' primary key,
    name   varchar(64) null comment '名称',
    status tinyint     not null comment '状态'
);

问题排查：
就像在背景中提到的，当字段不能为null时，很容易触发上述的异常，于是在第一时间检查DDL，发现字段name可以为空。这就奇怪了，正常情况下不会出现这个问题，于是怀疑这种可以为空的字段，在某种特殊情况会导致没有默认值的异常。

于是通过搜索引擎和GPT提出了我的问题，当时问题聚焦于null和非null字段上，得到的结果基本上都指向了可以为null的字段不会出现这个问题。

转换思路：
搜索无果后，尝试转换了一下思路，再重新关注一下问题的描述 Field 'xxx' doesn't have a default value，当时灵光一闪，提示已经非常清晰了没有默认值。经过测试发现，在建表和新增字段的时候，对于可以为null的字段，即使是没有显示的指定一个deafult，也会自动分配一个默认值null。
也就是说，下面两个ddl是等价的。

# 不显示指定默认值
alter table demo
    add age int null comment '年龄';
# 显示指定默认值null
alter table demo
    add age int null comment '年龄' default null;

既然问题没有出现在字段的创建上，那多半是后期在对数据库修数的时候，删除了字段的默认值。于是去查看了一下数据库的工单申请列表，果然发现了端倪，有这么一个ddl：

alter table demo
    alter column name drop default;

问题复现：
尝试在测试环境执行这条sql，并通过insert语句在不显示指定name字段的情况下，插入一条数据：

insert into demo (status) values ( 1);

执行结果如下：

问题解决：
问题已经定位复现后，再重新设置字段的默认值即可：

alter table demo
    alter column name set default null;

3.思考与总结

就像标题上说的，这个问题的隐蔽之处在于，即使是将字段的默认值drop了，在各类可视化界面中也看不出来，例如下图红框中的字段，其中name没有默认值，age字段有默认值null，但是根本看不出来：

这种情况可以，可以使用SHOW CREATE TABLE demo;获取原始的建表语句：

CREATE TABLE `demo` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(64) COMMENT '名称',
  `status` tinyint(4) NOT NULL COMMENT '状态',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4

这里就可以看到name和age字段的默认值区别了。

---

最后是一些思考和总结：

• 如果没有显式的指定字段默认值，在创建字段的时候会自动将默认值指定为null。
• not null字段，无法自动指定默认值，需要显示的指定。
• 可以使用SHOW CREATE TABLE demo;查看原始的建表语句，能直接看到默认值是否存在。

最后，如果需要修改默认值，将期设置为一个具体的值（哪怕设置为null），尽可能的不要使用drop default删除字段默认值，以此来避免一些意料之外的问题。