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 valueColumn 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之后并非如此),但是DATETIMETIMESTAMP除外,他们的默认值可以是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语句且未指定该列时,在严格模式与非严格模式下处理方式不同。前者是报错,后者是赋值为隐式的默认值,即列数据类型的默认值
  • 4
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值