MySQL 严格模式

设置严格模式

为当前会话设置严格模式：

执行SET sql_mode = 'STRICT_TRANS_TABLES' 或者SET sql_mode = 'STRICT_ALL_TABLES'。

全局设置严格模式：

执行SET global sql_mode = 'STRICT_TRANS_TABLES' 或者SET global sql_mode = 'STRICT_ALL_TABLES'。

或者在 MySQL 配置文件中配置以下内容，再重启：

[mysqld]
sql_mode = 'STRICT_TRANS_TABLES'  ; 或者配置成 STRICT_ALL_TABLES

设置非严格模式：SET sql_mode='' 。

查询当前的 SQL 模式

SHOW VARIABLES LIKE 'sql_mode';

SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;

STRICT_TRANS_TABLES 和 STRICT_ALL_TABLES 的区别

唯一的区别是：
对于不支持事务的表，若开启 STRICT_TRANS_TABLES，MySQL 会尝试将一个不合法的字段值转换成一个值最近的合法值插入表中；而开启 STRICT_ALL_TABLES 后，则表现为不写入数据，且抛出错误。

因为现在绝大部分用的 InnoDB 引擎，是支持事务的，所以基本不用关心这种区别。

严格模式和非严格模式的区别

严格模式下不能在无符号整数字段插入负值

非严格模式下，会存储为 0 。
严格模式下，报错。

验证：

在 mysql 5.6.40 版本验证。

创建表：

CREATE TABLE `test_table` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `num` bigint unsigned NOT NULL,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB CHARSET=utf8mb4;

插入负数、正数：

INSERT INTO `test_table` (num) VALUES(-1);
INSERT INTO `test_table` (num) VALUES(99);
INSERT INTO `test_table` (num) VALUES(-999);

查询数据：

mysql> select * from test_table;
+----+-----+
| id | num |
+----+-----+
| 1  | 0   |
| 2  | 99  |
| 3  | 0   |
+----+-----+

** 严格模式下呢？会报错。见下面的测试：**

mysql> SET sql_mode = 'STRICT_TRANS_TABLES';
mysql> INSERT INTO `test_table` (num) VALUES(-1);
(1264, u"Out of range value for column 'num' at row 1")

严格模式下，无默认值的 not null 字段在插入数据时必须指定值

在字段未指定默认值的场景中：

非严格模式下，若不插入数据会存储字段类型的默认值。
严格模式下，报错。

验证：

在 mysql 5.6.40 版本验证。

创建表：

CREATE TABLE `test_table` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `num` bigint unsigned NOT NULL,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB CHARSET=utf8mb4;

插入数据时仅指定 id：

INSERT INTO `test_table` (id) VALUES(1);

查询数据：

mysql> select * from test_table;
+----+-----+
| id | num |
+----+-----+
| 1  | 0   |
+----+-----+

严格模式下呢？会报错。见下面的测试：

mysql> SET sql_mode = 'STRICT_TRANS_TABLES';
mysql> INSERT INTO `test_table` (id) VALUES(2);
(1364, u"Field 'num' doesn't have a default value")

严格模式下，插入字符串不能超出定义长度

对于下表：

CREATE TABLE `test_table` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` char(4),
  PRIMARY KEY(`id`)
) ENGINE=InnoDB CHARSET=utf8mb4;

在非严格模式下执行

INSERT INTO `test_table` (name) VALUES('12345');

会成功插入数据，但是内容被截断，只剩4个字符：

mysql> select * from test_table;
+----+------+
| id | name |
+----+------+
| 1  | 1234 |
+----+------+

但是严格模式下会报错：

(1406, u"Data too long for column 'name' at row 1")