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

w_t_y_y

已于 2025-07-01 14:52:25 修改

阅读量381

点赞数 5

CC 4.0 BY-SA版权

分类专栏： # 关系型数据库MySQL 文章标签： sql 数据库

于 2025-06-18 16:59:13 首次发布

本文链接：https://blog.csdn.net/w_t_y_y/article/details/148744212

关系型数据库MySQL 专栏收录该内容

37 篇文章

订阅专栏

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

（1）严格模式

（2）宽松模式

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

（1）严格模式

（2）宽松模式

3、批量插入 NULL 到 NOT NULL 列

设置

我这里的数据库实例的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';
来对比下宽松模式和严格模式的区别。

一、not null

建表

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);

4、默认值对update不起作用

更新并不会转换为默认值。

（1）严格模式——直接报错

（2）宽松模式——不报错

但是全部更新成空字符串了，没有自动转换为default值。

二、超限裁剪

类型

宽松

严格

tinint

INSERT INTO test_int (id, age) VALUES (1, 300);

Query OK, 1 row affected, 1 warning (0.35 sec)

mysql> select * from test_int;

+----+-----+

| id | age |

+----+-----+

| 1 | 127 |

+----+-----+

1 row in set (0.28 sec)

update test_int set age=-129 where id=1;

Query OK, 1 row affected, 1 warning (0.28 sec)

Rows matched: 1 Changed: 1 Warnings: 1

mysql> select * from test_int;

+----+------+

| id | age |

+----+------+

| 1 | -128 |

+----+------+

1 row in set (0.28 sec)

INSERT INTO test_int (id, age) VALUES (2, 300);

ERROR 1264 (22003): Out of range value for column 'age' at row 1

update test_int set age=-129 where id=1;

ERROR 1264 (22003): Out of range value for column 'age' at row 1

varchar

INSERT INTO test_varchar (id, name) VALUES (1, 'ABCDEFGH');

Query OK, 1 row affected, 1 warning (0.29 sec)

mysql> select * from test_varchar;

+----+-------+

| id | name |

+----+-------+

| 1 | ABCDE |

+----+-------+

1 row in set (0.30 sec)

INSERT INTO test_varchar (id, name) VALUES (1, 'ABCDEFGH');

ERROR 1406 (22001): Data too long for column 'name' at row 1

三、类型转换

CREATE TABLE t (id INT NOT NULL);

严格模式，类型转换异常抛错ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'id' at row 1

宽松模式，给近值；

四、相同处理

case	对比		严格	非严格
数字除以0	无差异	SELECT 10 / 0;	-- 返回 NULL，并生成警告	-- 返回 NULL，并生成警告
非法日期	无差异	INSERT INTO test_dates (event_date) VALUES ('0000-00-00');	无效值落库	无效值落库@Hubble Meng
索引长度超限	DDL差异	CREATE INDEX idx_long_text ON test_index (long_text);	报错[2025-06-13 15:22:09] [42000][1071] Specified key was too long; max key length is 3072 bytes	告警 [42000][1071] Specified key was too long; max key length is 3072 bytes