sql_mode（二）宽松模式和严格模式的区别

 

目录

 

设置

一、插入字符串类型

 建表

总结

1、单插 NULL 到 NOT NULL 列（无默认值）

（1）严格模式

（2）宽松模式

2、单插 NULL 到 NOT NULL 列（有默认值）

（1）严格模式

（2）宽松模式

3、批量插入 NULL 到 NOT NULL 列

（1）严格模式

（2）宽松模式

二、插入数字类型

三、更新

---

设置

我这里的数据库实例的sql mode是严格模式，

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

下面通过修改临时会话的sql mode：再打开一个script会话，设置该tab的session为宽松模式:

-- 只保留最基本模式，宽松但仍防止引擎替代
SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';

来对比下宽松模式和严格模式的区别。

一、插入字符串类型

 建表

CREATE TABLE t_user_demo (id varchar(36) NOT NULL,user_id varchar(36) NOT NULL,user_name varchar(36) NOT NULL,PRIMARY KEY (`id`)
) 

CREATE TABLE t_user_demo_1 (id varchar(36) NOT NULL,user_id varchar(36) NOT NULL,user_name varchar(36) NOT null default 'abc',PRIMARY KEY (`id`)
) 

总结

case	严格模式	宽松模式
单插 NULL 到 NOT NULL 列（该列无默认值）	报错	报错
NOT NULL（该列无默认值），单插不指定该列	报错	报错
单插 NULL 到 NOT NULL 列（该列有default值）	报错	报错
NOT NULL（该列有默认值），单插不指定该列	成功，使用默认值	成功，使用默认值
批量插入 NULL 到 NOT NULL 列（该列无默认值）	报错	成功，转成空字符串
批量插入 NOT NULL 列（该列无默认值），不指定列	报错	成功，转成空字符串
批量插 NULL 到 NOT NULL 列（该列有default值）	报错	成功，转成空字符串
批量插入 ，不指定列NOT NULL（该列有默认值）	成功，使用默认值	成功，使用默认值

以下是验证的步骤：

1、单插 NULL 到 NOT NULL 列（无默认值）

（1）严格模式

 报错，插入不成功

不显示插入也报错

（2）宽松模式

显示插入Null同样报错，插入不成功

不显示插入会自动转成空字符串，插入成功

2、单插 NULL 到 NOT NULL 列（有默认值）

（1）严格模式

 报错，插入不成功

不显示指定字段则可以成功

（2）宽松模式

显示插入Null报错，插入不成功

不显示指定字段则可以成功

3、批量插入 NULL 到 NOT NULL 列

（1）严格模式

显示插入为Null，报错，插入不成功

对于有default值的字段不指定，则可以插入成功：

（2）宽松模式

  不管字段是否有默认值，不管是否显示插入Null，都成功插入。可见宽松模式下，单插显示指定Null会失败，只有不指定该字段可以生成默认值；而批量插入，不管该字段是否有默认值，不管有没有显示插入Null，都可以自动转成默认值插入成功。

chatGPT说的转化并不准确，实际上是批插才有转化。

INSERT INTO t_user_demo_1 (id,user_id,user_name)  VALUES 
('66',null, null),('88',null, null);

二、插入数字类型

三、更新