文章目录
- 写作背景
- 版本说明
- 官方文档中的说明
- 显式默认值
- 隐式默认值
- 数据类型的默认值
- sql_mode与strict_mode
- 查看sql_mode
- 设置sql_mode
- 测试
- 总结
文章开始之前先思考一下,假设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。同严格模式中的测试场景
总结
- 列显式指定的默认值不代表该列显式赋值为NULL时的值,而是代表如果该列被忽略时的值(INSERT时未指定该列)
- 如果列被定义为NOT NULL,并且没有显式的指定默认值,当执行INSERT语句且未指定该列时,在严格模式与非严格模式下处理方式不同。前者是报错,后者是赋值为隐式的默认值,即列数据类型的默认值