MySQL运维实战(4.4) SQL_MODE之STRICT_TRANS_TABLES和STRICT_ALL_TABLES
如果设置STRICT模式，则如果数据写入时，如果数据不符合字段定义（字符串超出长度、数值类型数据超出范围、违反not null约束等），SQL会报错。
如果不设置STRICT模式，会对异常数据进行截断处理，SQL会显示Warning，但不报错。
对于组合型SQL，对于非事物型存储引擎，如MyISAM存储引擎，一个SQL中部分数据合法，部分数据不合法，则可能会在表中写入部分数据。
STRICT_ALL_TABLES
对于非事务型存储引擎如MyISAM，可能会出现批量Insert中部分数据写入成功、部分数据写入失败的情况。事务型存储引擎如InnoDB不存在该问题。
mysql> create table t_innodb(a int) engine=innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> create table t_myisam(a int) engine=myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> set sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t_innodb values(1),('abc'),(3);
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'a' at row 2
mysql> select * from t_innodb;
Empty set (0.00 sec)
mysql> insert into t_myisam values(1),('abc'),(3);
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'a' at row 2
mysql> select * from t_myisam;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)STRICT_TRANS_TABLES
如果设置strict_trans_tables SQL_MODE，则只影响事物型存储引擎。不影响往非事务型引擎中写入非法数据。
mysql> set sql_mode='STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t_innodb values(10),('abc'),(30);
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'a' at row 2
mysql> insert into t_myisam values(10),('abc'),(30);
Query OK, 3 rows affected, 1 warning (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 1
mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'abc' for column 'a' at row 2 |
+---------+------+--------------------------------------------------------+
1 row in set (0.01 sec)
mysql> select * from t_myisam;
+------+
| a |
+------+
| 1 |
| 10 |
| 0 |
| 30 |
+------+
4 rows in set (0.00 sec)不设置STRICT模式
不设置STRICT模式，则可以写入非法数据。SQL会有warning，非法的数据，会根据字段类型做相应的处理。
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t_innodb values(100), ('abc');
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 1
mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'abc' for column 'a' at row 2 |
+---------+------+--------------------------------------------------------+
1 row in set (0.01 sec)
mysql> insert into t_myisam values(100), ('abc');
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 1
mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'abc' for column 'a' at row 2 |
+---------+------+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t_innodb;
+------+
| a |
+------+
| 100 |
| 0 |
+------+
2 rows in set (0.00 sec)
mysql> select * from t_myisam;
+------+
| a |
+------+
| 1 |
| 10 |
| 0 |
| 30 |
| 100 |
| 0 |
+------+
6 rows in set (0.00 sec)字符串类型
字符串类型的处理方式是截断超出长度的字符
mysql> set sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table t_str(a varchar(3));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t_str values('abcd');
ERROR 1406 (22001): Data too long for column 'a' at row 1
mysql> select * from t_str;
Empty set (0.00 sec)
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t_str values('abcd');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'a' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t_str;
+------+
| a |
+------+
| abc |
+------+
1 row in set (0.00 sec)数值类型
对于unsigned类型，插入负数会转换成0。如果数字超过允许的最大值，则截断为最大值
mysql> set sql_mode='STRICT_ALL_TABLES'; mysql> create table t_unsigned(a tinyint unsigned); Query OK, 0 rows affected (0.01 sec) mysql> insert into t_unsigned values(-1); ERROR 1264 (22003): Out of range value for column 'a' at row 1 mysql> insert into t_unsigned values(256); ERROR 1264 (22003): Out of range value for column 'a' at row 1 mysql> select * from t_unsigned; Empty set (0.00 sec) mysql> set sql_mode=''; mysql> insert into t_unsigned values(-1); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> show warnings; +---------+------+--------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------+ | Warning | 1264 | Out of range value for column 'a' at row 1 | +---------+------+--------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from t_unsigned; +------+ | a | +------+ | 0 | +------+ 1 row in set (0.00 sec) mysql> insert into t_unsigned values(256); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+--------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------+ | Warning | 1264 | Out of range value for column 'a' at row 1 | +---------+------+--------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from t_unsigned; +------+ | a | +------+ | 0 | | 255 | +------+ 2 rows in set (0.00 sec)
总结
建议设置STRICT_TRANS_TABLES。生产环境修改SQL_MODE需要先进行兼容性测试。
