MySQL运维实战(4.4) SQL_MODE之STRICT_TRANS_TABLES和STRICT_ALL_TABLES

本文介绍: 对于组合型SQL，对于非事物型存储引擎，如MyISAM存储引擎，一个SQL中部分数据合法，部分数据不合法，则可能会在表中写入部分数据，但截断或修改为合法值，而不会导致整个操作失败。例如，在数据写入时，如果数据不符合字段定义（字符串超出长度、数值类型数据超出范围、违反not null约束等），SQL会报错。与STRICT_ALL_TABLES不同，设置STRICT_TRANS_TABLES模式仅在事务表上执行更严格的检查，包括对插入和更新的数据类型和值的验证，而不影响往非事务型引擎中写入非法数据。

作者：俊达

1 STRICT MODE

在MySQL中，STRICT模式主要用于控制数据库的行为，有助于保持数据的一致性和完整性，特别是在涉及到数据写入、更新和其他操作时的约束。
如果设置STRICT模式，MySQL会更加严格地执行数据写入和更新等操作，当数据不符合字段定义或者违反了约束条件，MySQL将抛出错误，相关操作也会被拒绝。例如，在数据写入时，如果数据不符合字段定义（字符串超出长度、数值类型数据超出范围、违反not null约束等），SQL会报错。
如果不设置STRICT模式，MySQL在遇到异常数据时可能会进行截断处理，SQL会显示Warning，但不报错。对于组合型SQL，对于非事物型存储引擎，如MyISAM存储引擎，一个SQL中部分数据合法，部分数据不合法，则可能会在表中写入部分数据，但截断或修改为合法值，而不会导致整个操作失败。虽然没有立即的错误，但这可能会导致数据丢失或不一致。

2 设置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)

3 设置STRICT_TRANS_TABLES

与STRICT_ALL_TABLES不同，设置STRICT_TRANS_TABLES模式仅在事务表上执行更严格的检查，包括对插入和更新的数据类型和值的验证，而不影响往非事务型引擎中写入非法数据。

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)