mysql管理之道 pdf_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)