MySQL管理之道-筆記-MySQL5.7 sql_mode的改變

MySQL 5.7 sql_mode的改變
1、默認啟用STRICT_TRANS_TABLES嚴格模式，該模式為嚴格模式，對數據會作嚴格的校驗，錯誤數據不能插入報錯，并且事物回滾。
2、MySQL5.6默認SQL_MODE模式為空。

表age字段是int，插入字符類時會報錯，但sql_mode為空，所以數據可以插入。

圖1

root@localhost:mysql3306.sock [sbtest]>desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(2) | YES | | NULL | |
| age | smallint(6) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+

圖2 （sql_mode設置為空）

root@localhost:mysql3306.sock [sbtest]>set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.02 sec)

root@localhost:mysql3306.sock [sbtest]>insert into t1 values(1,'aa','aaa');
Query OK, 1 row affected, 1 warning (0.04 sec)

root@localhost:mysql3306.sock [sbtest]>show warnings;
+---------+------+----------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'aaa' for column 'age' at row 1 |
+---------+------+----------------------------------------------------------+
row in set (0.00 sec)

圖3 （插入成功）

root@localhost:mysql3306.sock [sbtest]>select * from t1;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | aa | 0 |
+----+------+------+
row in set (0.00 sec)

圖4（改成STRICT_TRANS_TABLES，插入失敗，事務回滾）

root@localhost:mysql3306.sock [sbtest]>set sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost:mysql3306.sock [sbtest]>insert into t1 values(2,'bb','bbb');
ERROR 1366 (HY000): Incorrect integer value: 'bbb' for column 'age' at row 1
root@localhost:mysql3306.sock [sbtest]>select * from t1 where id=2;
Empty set (0.04 sec)