MySQL列的默认值(default)的研究

文章开始之前先思考一下，假设MySQL中有一张如下的表

CREATE TABLE student (
 `name` VARCHAR(20) NOT NULL COMMENT '姓名',
 `age` INT NOT NULL DEFAULT 18 COMMENT '年龄'
);

其中name以及age列都为NOT NULL，不同的是age有一个DEFAULT 18的子句。接下来分别执行以下四个SQL语句，结果是什么呢

-- 1. 不指定任意一列
INSERT INTO student() VALUES();
-- 2. 只指定name列
INSERT INTO student(name) VALUES('gala');
-- 3. 只指定name列，并且显式赋值为NULL
INSERT INTO student(name) VALUES (NULL);
-- 4. 指定name和age列，age列显式赋值为NULL
INSERT INTO student(name, age) VALUES('gala', NULL);

答案会写在文章最后。

写作背景

工作中碰到了一个需求，其实也很好理解，就是根据name去查询一条数据，如果有那么直接取出进行后续判断；如果没有则去创建一条新的记录。其中有一列为一个计数器，我把该列定义为非空，并且默认为0，SQL大致如下

CREATE TABLE T (
  ..
  `counter` INT NOT NULL DEFAULT 0 COMMENT '计数器',
  ...
)

在程序中执行到创建实体的时候，并没有set该列对应字段的值，因为是Integer类型，如不赋值成员变量默认的应该就为NULL，想着MySQL会把它自动转换成我定义的默认值0。但是执行测试的时候控制台就报错了

Column 'counter' cannot be null

在往上翻，SQL的日志大概如下

INSERT INTO T(..., counter) VALUES (..., NULL);

可是我不是设置了默认值吗，为什么会不起作用。带着这些疑问，我去查阅了MySQL的官方文档，了解了为什跟我想的不一样，以及总结了默认值的用法。

如果你碰到Field XX doesn't have a default value或Column XX cannot be null并且毫无头绪的话，这篇文章可能会对你有帮助

版本说明

阐述问题以及写技术文档时都需要表明所使用工具版本号，因为不同的大版本之间差异性可能会很大，甚至同大版本不同小版本之间也会有很大差异。

本文是参照MySQL 5.7官方网址进行的探究，本地MySQL版本为5.7.34。
其他版本会有或多或少的差异，建议如果非5.7版本还需要自己去官网查看相关内容

官方文档中的说明

数据类型规范可以有显式或隐式的默认值

显式默认值

在定义列时可以显式的指出默认值，如

CREATE TABLE student(
  `age` INT DEFAULT 18
)

DEFAULT子句中默认值必须是一个字面量(MySQL 8.0.13之后并非如此)，但是DATETIME和TIMESTAMP除外，他们的默认值可以是CURRENT_TIMESTAMP()。

隐式默认值

如果没有显式的指出默认值，那么MySQL会按照如下的方式进行处理

• 如果列允许NULL值，则MySQL会显式的使用DEFAULT NULL来定义该列，即会强制的帮你显式的定义默认值DEFAULT NULL。
• 如果列不允许NULL值，那么MySQL也不会为显式的为该列定义默认值。

对于数据进入一个没有显式指定默认值的NOT NULL列来说，如果一个INSERT或REPLACE语句中不包含该列，或一个UPDATE语句将该列设置为NULL，MySQL会根据当时的sql mode来处理该列

• 如果开启了strict mode, 对于有事务的表来说会发生错误，语句将会回滚。对于非事务表来说也会发生错误，但是如果发生在多行语句中，那么在错误发生之前的行都会被插入（因为没有事务）
• 如果未开启strict mode, MySQL会设置当前列的值为该列数据类型的默认值

数据类型的默认值

• 数字类型的默认值为0（AUTO_INCREMENT属性除外）
• 对于除了TIMESTAMP以外的日期和时间类型来说，默认值是适合该类型的“零”值。如果开启了explicit_defaults_for_timestamp系统变量，那么这一条也适用于TIMESTAMP类型。另外，对于第一个TIMESTAMP列，默认值是当前的日期和时间。这里不打算着重介绍“零”值以及日期类型相关内容，有一个大致印象即可。
• 对于除了ENUM的字符串类型来说，默认值是空字符串，对于ENUM来说，默认值是第一个枚举类型

sql_mode与strict_mode

MySQL有一个sql_mode系统变量，可以根据不同的需求进行设置，在5.7中默认值为ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES, NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
官网中说的strict_mode又是什么呢？我们可以看到有一个默认值为STRICT_TRANS_TABLES，还有一个可选值为STRICT_ALL_TABLES。如果sql_mode设置了这两个值之一，那么就为strict mode

查看sql_mode

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

设置sql_mode

SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';

另：通过命令修改sql_mode的值会在MySQL服务重启后自动恢复。如果需要永久修改就需要修改MySQL的配置文件了，如windows下的my.ini

测试

测试文章开头的例子
新建一张表

CREATE TABLE student (
 `name` VARCHAR(20) NOT NULL COMMENT '姓名',
 `age` INT NOT NULL DEFAULT 18 COMMENT '年龄'
);

1.开启strict mode并执行SQL

-- 失败: Field 'name' doesn't have a default value
INSERT INTO student() VALUES();
-- 成功，name为gala，age为默认值18
INSERT INTO student(name) VALUES('gala');
-- 失败：Column 'name' cannot be null
INSERT INTO student(name) VALUES (NULL);
-- 失败：Column 'age' cannot be null
INSERT INTO student(name, age) VALUES('gala8', NULL)

结论：

• 如果一列定义为NOT NULL，并且插入时不包含该列，则MySQL赋值为显式定义的默认值，如果没有则会报错xxx doesn’t have a default value。根据报错信息可以知道，MySQL会处理这个值，只不过不知道他的默认值是什么。
• 如果一列定义为NOT NULL，并且插入时包含该列，则MySQL赋值为显式赋值的值。如果显式赋值为NULL，则会报错Column xx cannot be null。根据报错信息可以知道，与上一条不同，MySQL不会处理这个值，意味着此时显式定义的默认值无效，只会接收显式赋值的值。

2.关闭strict mode并执行sql

-- 成功，name为空字符串，即字符串数据类型默认值，age为18
INSERT INTO student() VALUES();
-- 成功，name为gala，age为18
INSERT INTO student(name) VALUES('gala');
-- 失败：Column 'name' cannot be null
INSERT INTO student(name) VALUES (NULL);
-- 失败：Column 'age' cannot be null
INSERT INTO student(name, age) VALUES('gala8', NULL)

结论：

• 如果一列定义为NOT NULL，并且插入时不包含该列，则MySQL赋值为显式定义的默认值，如果没有则会赋值为该列数据类型相对应的隐式的默认值
• 如果一列定义为NOT NULL，并且插入时包含该列，则MySQL赋值为显式赋值的值。如果显式赋值为NULL，则会报错Column xx cannot be null。同严格模式中的测试场景

总结

1. 列显式指定的默认值不代表该列显式赋值为NULL时的值，而是代表如果该列被忽略时的值(INSERT时未指定该列)
2. 如果列被定义为NOT NULL，并且没有显式的指定默认值，当执行INSERT语句且未指定该列时，在严格模式与非严格模式下处理方式不同。前者是报错，后者是赋值为隐式的默认值，即列数据类型的默认值