MySQL Case--Strict mode与NOT NULL
事故回溯
某业务流程操作为：
1、循环扫描某张待处理请求表，查看是否有请求等待处理。 2、找到待处理请求后，申请相关资源进行处理，并将处理结果插入到处理结果表中。 3、将该请求从待处理请求表中移除。
由于业务需求变更，研发同事申请向处理结果表上新增一个NOT NULL的列，但没有为该列设置默认值，数据库运行在严格模式下，DDL执行成功后，业务系统未及时上线，仍使用原有代码继续执行，在执行到步骤2向处理结果表插入记录时抛出异常，导致流程操作失败并循环重试，知道所有资源被耗光，导致严重的业务问题。
在该事故中，一个主要的问题就是DBA使用MySQL Inception对SQL进行语法检查，未发现NOT NULL的列没有默认值，同时对该问题存在理解偏差。
在SQL Server中，不允许在有记录的数据表上增加"NOT NULL且没有默认值"的列，但在MySQL上是否存在例外呢？
SQL Mode 与 NOT NULL
MySQL允许设置不同的SQL MODE(GLOBAL级别或SESSION级别)来兼容MySQL早期版本或兼容非标准SQL，比较常用的SQL MODE两种是严格模式(strict mode)和非严格模式(non-strict mode)，两者差异较大，需要慎重设置。
在MySQL 官方文档中有如下关于SQL MODE和NOT NULL的描述：
Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.) Strict mode also affects DDL statements such as CREATE TABLE. Strict SQL mode applies to the following errors, represent a class of errors in which an input value is either invalid or missing. A value is invalid if it has the wrong data type for the column or might be out of range. A value is missing if a new row to be inserted does not contain a value for a NOT NULL column that has no explicit DEFAULT clause in its definition. Changing a NULL column to NOT NULL in non-strict mode is prohibited to prevent converting NULL values to default non-NULL values, for which there are no corresponding values in the referenced table. The operation is permitted in strict mode, but an error is returned if any such conversion is required. https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict
准备测试环境
# 删除已有测试表 drop table if exists tb1001; # 创建测试表 create table tb1001( id int auto_increment primary key, c1 int ); # 插入测试数据 insert into tb1001(c1) select 1; insert into tb1001(c1) select 2; insert into tb1001(c1) select 3; # 查看最新数据 select * from tb1001; +----+------+ | id | c1 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | +----+------+
严格模式下NOT NULL限制
设置SESSION级别严格模式
set session sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
测试1、增加不带默认值的NOT NULL列，测试通过，对已有记录，会付给该类型的初始值(PS：时间戳列比较特殊，会自动生成默认值，且不同版本存在差异)
alter table tb1001 add c2 int not null, add c3 varchar(10) not null, add c4 datetime not null, add c5 timestamp not null; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 select * from tb1001; +----+------+----+----+---------------------+---------------------+ | id | c1 | c2 | c3 | c4 | c5 | +----+------+----+----+---------------------+---------------------+ | 1 | 1 | 0 | | 0000-00-00 00:00:00 | 2019-08-07 22:31:53 | | 2 | 2 | 0 | | 0000-00-00 00:00:00 | 2019-08-07 22:31:53 | | 3 | 3 | 0 | | 0000-00-00 00:00:00 | 2019-08-07 22:31:53 | +----+------+----+----+---------------------+---------------------+ 3 rows in set (0.00 sec)
测试2、插入记录但不显式设置"NOT NULL且没有默认值"列的值，测试失败，在严格模式下，必须为"NOT NULL且没有默认值"的列在插入时显式指定值
insert into tb1001(c1) select 4; ERROR 1364 (HY000): Field 'c2' doesn't have a default value
insert into tb1001(c1,c2) select 4,4; ERROR 1364 (HY000): Field 'c3' doesn't have a default value
insert into tb1001(c1,c2,c3) select 4,4,4; ERROR 1364 (HY000): Field 'c4' doesn't have a default value
insert into tb1001(c1,c2,c3,c4) select 4,4,4,now(); Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0
测试3、插入记录时显式将"NOT NULL且没有默认值"的列设置为NULL，测试失败，在严格模式下，插入记录时不能将"NOT NULL且没有默认值"的列显式赋值为NULL
insert into tb1001(c1,c2,c3,c4) select 5,NULL,NULL,NULL; ERROR 1048 (23000): Column 'c2' cannot be null insert into tb1001(c1,c2,c3,c4) select 5,5,NULL,NULL; ERROR 1048 (23000): Column 'c3' cannot be null insert into tb1001(c1,c2,c3,c4) select 5,5,'5',NULL; ERROR 1048 (23000): Column 'c4' cannot be null insert into tb1001(c1,c2,c3,c4) select 5,5,'5',NOW(); Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0
测试4、更新记录时显式将"NOT NULL且没有默认值"的列设置为NULL，测试失败，在严格模式下，更新记录时不能将"NOT NULL且没有默认值"的列显式赋值为NULL。
UPDATE tb1001 SET C1=11, C2=NULL, C3=NULL, C4=NULL WHERE ID=1; ERROR 1048 (23000): Column 'c2' cannot be null UPDATE tb1001 SET C1=11, C2=11, C3=NULL, C4=NULL WHERE ID=1; ERROR 1048 (23000): Column 'c3' cannot be null UPDATE tb1001 SET C1=11, C2=11, C3='11', C4=NULL WHERE ID=1; ERROR 1048 (23000): Column 'c4' cannot be null UPDATE tb1001 SET C1=11, C2=11, C3='11', C4=NOW() WHERE ID=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
非严格模式下NOT NULL限制
设置SESSION级别非严格模式
set session sql_mode='ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
测试1、增加不带默认值的NOT NULL列，测试通过，对已有记录，会付给该类型的初始值(PS：时间戳列比较特殊，会自动生成默认值，且不同版本存在差异)
alter table tb1001 add c2 int not null, add c3 varchar(10) not null, add c4 datetime not null, add c5 timestamp not null; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 select * from tb1001; +----+------+----+----+---------------------+---------------------+ | id | c1 | c2 | c3 | c4 | c5 | +----+------+----+----+---------------------+---------------------+ | 1 | 1 | 0 | | 0000-00-00 00:00:00 | 2019-08-07 22:47:37 | | 2 | 2 | 0 | | 0000-00-00 00:00:00 | 2019-08-07 22:47:37 | | 3 | 3 | 0 | | 0000-00-00 00:00:00 | 2019-08-07 22:47:37 | +----+------+----+----+---------------------+---------------------+ 3 rows in set (0.00 sec)
测试2、插入记录但不显式设置"NOT NULL且没有默认值"列的值，测试通过，在非严格模式下，如果对"NOT NULL且没有默认值"的列在插入时未指定显式值则使用类型初始值。
insert into tb1001(c1) select 4; Query OK, 1 row affected, 3 warnings (0.00 sec) Records: 1 Duplicates: 0 Warnings: 3 insert into tb1001(c1,c2) select 4,4; Query OK, 1 row affected, 2 warnings (0.00 sec) Records: 1 Duplicates: 0 Warnings: 2 insert into tb1001(c1,c2,c3) select 4,4,4; Query OK, 1 row affected, 1 warning (0.00 sec) Records: 1 Duplicates: 0 Warnings: 1 insert into tb1001(c1,c2,c3,c4) select 4,4,4,now(); Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 select * from tb1001 where c1=4; +----+------+----+----+---------------------+---------------------+ | id | c1 | c2 | c3 | c4 | c5 | +----+------+----+----+---------------------+---------------------+ | 4 | 4 | 0 | | 0000-00-00 00:00:00 | 2019-08-07 22:50:01 | | 5 | 4 | 4 | | 0000-00-00 00:00:00 | 2019-08-07 22:50:01 | | 6 | 4 | 4 | 4 | 0000-00-00 00:00:00 | 2019-08-07 22:50:01 | | 7 | 4 | 4 | 4 | 2019-08-07 22:50:01 | 2019-08-07 22:50:01 | +----+------+----+----+---------------------+---------------------+ 4 rows in set (0.00 sec)
测试3、插入记录时显式将"NOT NULL且没有默认值"的列设置为NULL，测试通过，在非严格模式下，插入记录时可以将"NOT NULL且没有默认值"的列显式赋值为NULL，实际会存储列类型的初始值。
insert into tb1001(c1,c2,c3,c4) select 5,NULL,NULL,NULL; Query OK, 1 row affected, 3 warnings (0.00 sec) Records: 1 Duplicates: 0 Warnings: 3 insert into tb1001(c1,c2,c3,c4) select 5,5,NULL,NULL; Query OK, 1 row affected, 2 warnings (0.00 sec) Records: 1 Duplicates: 0 Warnings: 2 insert into tb1001(c1,c2,c3,c4) select 5,5,'5',NULL; Query OK, 1 row affected, 1 warning (0.00 sec) Records: 1 Duplicates: 0 Warnings: 1 insert into tb1001(c1,c2,c3,c4) select 5,5,'5',NOW(); Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 select * from tb1001 where c1=5; +----+------+----+----+---------------------+---------------------+ | id | c1 | c2 | c3 | c4 | c5 | +----+------+----+----+---------------------+---------------------+ | 8 | 5 | 0 | | 0000-00-00 00:00:00 | 2019-08-07 22:54:05 | | 9 | 5 | 5 | | 0000-00-00 00:00:00 | 2019-08-07 22:54:05 | | 10 | 5 | 5 | 5 | 0000-00-00 00:00:00 | 2019-08-07 22:54:05 | | 11 | 5 | 5 | 5 | 2019-08-07 22:54:05 | 2019-08-07 22:54:05 | +----+------+----+----+---------------------+---------------------+ 4 rows in set (0.00 sec)
测试4、更新记录时显式将"NOT NULL且没有默认值"的列设置为NULL，测试失败，在非严格模式下，更新记录时可以将"NOT NULL且没有默认值"的列显式赋值为NULL，实际会存储列类型的初始值。
UPDATE tb1001 SET C1=11, C2=11, C3='11', C4='1911-11-11 11:11:11' WHERE ID=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 ## 更新前数据 select * from tb1001 where id=1; +----+------+----+----+---------------------+---------------------+ | id | c1 | c2 | c3 | c4 | c5 | +----+------+----+----+---------------------+---------------------+ | 1 | 11 | 11 | 11 | 1911-11-11 11:11:11 | 2019-08-07 22:58:49 | +----+------+----+----+---------------------+---------------------+ 1 row in set (0.00 sec) UPDATE tb1001 SET C1=1, C2=NULL, C3=NULL, C4=NULL WHERE ID=1; Query OK, 1 row affected, 3 warnings (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 3 ## 更新后数据 select * from tb1001 where id=1; +----+------+----+----+---------------------+---------------------+ | id | c1 | c2 | c3 | c4 | c5 | +----+------+----+----+---------------------+---------------------+ | 1 | 1 | 0 | | 0000-00-00 00:00:00 | 2019-08-07 22:59:34 | +----+------+----+----+---------------------+---------------------+ 1 row in set (0.00 sec)
总结
1、无论在严格模式还是非严格模式，都允许在已有记录的表上新增"NOT NULL且没有默认值"的列。 2、在严格模式下，不允许在插入时对"NOT NULL且没有默认值"的列未指定值或指定NULL值，不允许更新时对"NOT NULL且没有默认值"的列指定NULL值。 3、在非严格模式下，在插入时对"NOT NULL且没有默认值"的列未指定值或指定NULL值，或更新时对"NOT NULL且没有默认值"的列指定NULL值，都将使用列类型的初始值来替换NULL值。
