采坑之使用MySQL，SQL_MODE有哪些坑

SQL_MODE是MySQL中的一个系统变量（variable），可由多个MODE组成，每个MODE控制一种行为，如是否允许除数为0，日期中是否允许'0000-00-00'值。

为什么需要关注SQL_MODE呢？

首先，看三个简单的Demo（MySQL 5.6）。

1.

mysql> create table t1(c1 datetime);Query OK, 0 rows affected (0.16 sec)mysql> insert into t1 values('2019-02-29');Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select * from t1;+---------------------+| c1                  |+---------------------+| 0000-00-00 00:00:00 |+---------------------+row in set (0.00 sec)

实际存储值与插入值不符。

2.

mysql> create table t2(c1 varchar(10));Query OK, 0 rows affected (0.06 sec)mysql> insert into t2 values('a'),('b'),('c');Query OK, 3 rows affected (0.01 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> select * from t2;+------+| c1   |+------+| a    || b    || c    |+------+rows in set (0.00 sec)mysql> alter table t2 modify column c1 int;Query OK, 3<em>本文来源gao.dai.ma.com搞@代*码(网$</em> rows affected, 3 warnings (0.05 sec)Records: 3  Duplicates: 0  Warnings: 3mysql> show warnings;+---------+------+-------------------------------------------------------+| Level   | Code | Message                                               |+---------+------+-------------------------------------------------------+| Warning | 1366 | Incorrect integer value: 'a' for column 'c1' at row 1 || Warning | 1366 | Incorrect integer value: 'b' for column 'c1' at row 2 || Warning | 1366 | Incorrect integer value: 'c' for column 'c1' at row 3 |+---------+------+-------------------------------------------------------+rows in set (0.00 sec)mysql> select * from t2;+------+| c1   |+------+|    0 ||    0 ||    0 |+------+rows in set (0.00 sec)

DDL导致原列内容丢失。

3.

mysql> create table t3(id int not null,c1 varchar(10));Query OK, 0 rows affected (0.05 sec)mysql> insert into t3 values(null,'a');ERROR 1048 (23000): Column 'id' cannot be nullmysql> insert into t3(c1) values('a');Query OK, 1 row affected, 1 warning (0.00 sec)mysql> show warnings;+---------+------+-----------------------------------------+| Level   | Code | Message                                 |+---------+------+-----------------------------------------+| Warning | 1364 | Field 'id' doesn't have a default value |+---------+------+-----------------------------------------+row in set (0.00 sec)mysql> select * from t3;+----+------+| id | c1   |+----+------+|  0 | a    |+----+------+row in set (0.00 sec)

显式指定列和不显式指定的处理逻辑竟然不一样。

为什么会这样呢？这个即与SQL_MODE有关。

在MySQL 5.6中， SQL_MODE的默认值为"NO_ENGINE_SUBSTITUTION"，非严格模式。

在这种模式下，在进行数据变更操作时，如果涉及的列中存在无效值（如日期不存在，数据类型不对，数据溢出），只会提示"Warning"，并不会报错。

如果要规避上述问题，需开启SQL_MODE的严格模式。

SQL_MODE的严格模式

所谓的严格模式，即SQL_MODE中开启了STRICT_ALL_TABLES或STRICT_TRANS_TAB LES。

还是上面的Demo，看看严格模式下，MySQL的处理逻辑。

mysql> set session sql_mode='STRICT_TRANS_TABLES';Query OK, 0 rows affected (0.00 sec)mysql> insert into t1 values('2019-02-29');ERROR 1292 (22007): Incorrect datetime value: '2019-02-29' for column 'c1' at row 1mysql> alter table t2 modify column c1 int;ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'c1' at row 1mysql> insert into t3(c1) values('a');ERROR 1364 (HY000): Field 'id' doesn't have a default value

喜欢 (0)赏

[搞代码]

分享 (0)