三十五、MySQL基础系列笔记之SQL Mode

什么是SQL Mode（SQL模式）

SQL Mode是一组MySql支持的基本语法、数据校验的规则。

SQL Mode解决如下问题

• 设置SQL Mode，用于完成不同严格程度的数据校验，有效的保障数据准确性；
• 设置SQL Mode为ANSI模式，用于保证大多数SQL符合标准的SQL语法，可应用于不同数据库之间的迁移；
• 在不同数据库之间进行数据迁移之前，通过设置SQL Mode可以使MySQL上的数据更方便地迁移到目标数据库中。

SQL Mode

举个栗子用于理解SQL Mode，来看看如下字段，长度为2

create table t2(name varchar(2));

MySQL5.7之前的版本，对于插入3个字是不会报错的，可以插入进去，但是MySQL5.7之后的版本则会直接报错。来看实际案例。

mysql> insert into t2 (name) values ('你好');
Query OK, 1 row affected (0.00 sec)

# 可以看到，一旦超过3个字将直接报错
mysql> insert into t2 (name) values ('你好ya');
ERROR 1406 (22001): Data too long for column 'name' at row 1

SQL Mode演示

还是基于上面的t2表，演示不同的SQL Mode的效果。

案例一：取消sql mode

set session sql_mode='';

# 查看是否设置成功
mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

插入一条超过字段长度限制的数据。

mysql> insert into t2 values('你好呀');      
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t2;
+--------+
| name   |
+--------+
| h2     |
| 你好   |
| 你好   |
+--------+
3 rows in set (0.00 sec)

可以看到，超过字段长度的数据插入成功，没有报错，但是超过长度的数据被截断了。

案例二：STRICT_TRANS_TABLES（严格事务表模式）

mysql> set session sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t2 values('自如初');
ERROR 1406 (22001): Data too long for column 'name' at row 1

可以看到，设置为STRICT_TRANS_TABLES模式时，超过字段长度的数据插入时将会报错。

关于修改sql_mode的方法，即设置当前会话或全局模式，具体可以参考 MySQL基础系列笔记之变量 这篇文章；本篇文章还是简单记录一下：

修改sql_mode的方式，set [global|session] sql_mode='modes'

session表示只在本次连接中生效；global表示在本次连接中并不生效，而对于新的连接生效。关于其他模式，多多练习。

案例三：ANSI模式

验证日期合法性。2021-02-30这个日期是错误的，2月没有30号。

create table `t3`(d datetime);
# 修改模式
mysql> set session sql_mode='ANSI';
Query OK, 0 rows affected (0.00 sec)

# 插入数据
mysql> insert into t3 values('2021-02-30');
Query OK, 1 row affected, 1 warning (0.00 sec)

# 查看
mysql> select * from t3;
+---------------------+
| d                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

可以神奇的看到，非法日期被插入成功了，但是，查询出来的日期值却变成了'0000-00-00 00:00:00'。

案例四：TRADITIONAL模式

mysql> set session sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t3 values('2021-02-30');
ERROR 1292 (22007): Incorrect datetime value: '2021-02-30' for column 'd' at row 1


mysql> insert into t3 values('2021-02-28');
Query OK, 1 row affected (0.00 sec)

该模式下，非法日期将无法插入。

常用的SQL Mode

2021-01-11