SQL_MODE中的STRICT_TRANS_TABLES和STRICT_ALL_TABLES

不念不忘少年蓝@ 2022-04-24 06:26 167阅读 0赞

STRICT\_TRANS\_TABLES和STRICT\_ALL\_TABLES，只要把SQL\_MODE设置为其中的一个，就被称为严格模式。  
这两个选项对于Innodb表是相同的作用，对于单个insert操作，无论插入单行或是多行，只要插入数据与字段类型不兼容，则insert操作失败并回滚；  
对于Myisam表是不同的作用：  
1.STRICT\_TRANS\_TABLES，对于单个insert操作，插入单行数据与字段类型不兼容，则insert操作失败并回滚；插入多行数据，如果插入数据的第一行内容与字段类型不兼容，则insert操作失败并回滚，如果插入数据的第一行内容与字段类型兼容，但后续的数据行存在不兼容的情况，则兼容的数据正常插入，不兼容的数据会转换成符合字段类型的格式再插入，不会中断和回滚；  
2.STRICT\_ALL\_TABLES，与STRICT\_TRANS\_TABLES不同的是，如果插入数据的第一行内容与字段类型兼容，但后续的数据行存在不兼容的情况，则兼容的数据正常插入，不兼容的数据则会报错并终止insert操作。

以下配合实验说明：  
有如下两个表，test是Innodb引擎，test1是Myisam引擎:

mysql> show create table test\G
    *************************** 1. row ***************************
           Table: test
    Create Table: CREATE TABLE `test` (
      `id` int(11) NOT NULL,
      `col1` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)
    
    mysql> show create table test1\G
    *************************** 1. row ***************************
           Table: test1
    Create Table: CREATE TABLE `test1` (
      `id` int(11) NOT NULL,
      `col1` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)

当在sql\_mode不设置为严格模式时，Innodb和Myisam在插入’a’到int类型的col1字段时，都没有报错，并且将’a’转换成了数字0：

mysql> select @@session.sql_mode;
    +--------------------+
    | @@session.sql_mode |
    +--------------------+
    |                    |
    +--------------------+
    1 row in set (0.00 sec)
    
    mysql> select @@global.sql_mode;
    +-------------------+
    | @@global.sql_mode |
    +-------------------+
    |                   |
    +-------------------+
    1 row in set (0.00 sec)
    
    mysql> insert into test values (1,'s');
    Query OK, 1 row affected, 1 warning (0.01 sec)
    
    mysql> show warnings;
    +---------+------+---------------------------------------------------------+
    | Level   | Code | Message                                                 |
    +---------+------+---------------------------------------------------------+
    | Warning | 1366 | Incorrect integer value: 's' for column 'col1' at row 1 |
    +---------+------+---------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select * from test;
    +----+------+
    | id | col1 |
    +----+------+
    |  1 |    0 |
    +----+------+
    1 row in set (0.00 sec)
    
    mysql> insert into test values (2,'a'),(3,'a');
    Query OK, 2 rows affected, 2 warnings (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 2
    
    mysql> show warnings;
    +---------+------+---------------------------------------------------------+
    | Level   | Code | Message                                                 |
    +---------+------+---------------------------------------------------------+
    | Warning | 1366 | Incorrect integer value: 'a' for column 'col1' at row 1 |
    | Warning | 1366 | Incorrect integer value: 'a' for column 'col1' at row 2 |
    +---------+------+---------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    mysql> select * from test;
    +----+------+
    | id | col1 |
    +----+------+
    |  1 |    0 |
    |  2 |    0 |
    |  3 |    0 |
    +----+------+
    3 rows in set (0.00 sec)
    
    mysql> insert into test1 values (1,'a');
    Query OK, 1 row affected, 1 warning (0.02 sec)
    
    mysql> show warnings;
    +---------+------+---------------------------------------------------------+
    | Level   | Code | Message                                                 |
    +---------+------+---------------------------------------------------------+
    | Warning | 1366 | Incorrect integer value: 'a' for column 'col1' at row 1 |
    +---------+------+---------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select * from test1;
    +----+------+
    | id | col1 |
    +----+------+
    |  1 |    0 |
    +----+------+
    1 row in set (0.00 sec)
    
    mysql> insert into test1 values (2,'a'),(3,'a'); 
    Query OK, 2 rows affected, 2 warnings (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 2
    
    mysql> show warnings;
    +---------+------+---------------------------------------------------------+
    | Level   | Code | Message                                                 |
    +---------+------+---------------------------------------------------------+
    | Warning | 1366 | Incorrect integer value: 'a' for column 'col1' at row 1 |
    | Warning | 1366 | Incorrect integer value: 'a' for column 'col1' at row 2 |
    +---------+------+---------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    mysql> select * from test1;
    +----+------+
    | id | col1 |
    +----+------+
    |  1 |    0 |
    |  2 |    0 |
    |  3 |    0 |
    +----+------+
    3 rows in set (0.00 sec)

当SQL\_MODE设置为STRICT\_TRANS\_TABLES时，

mysql> set @@session.sql_mode='strict_trans_tables';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> select @@session.sql_mode;
    +---------------------+
    | @@session.sql_mode  |
    +---------------------+
    | STRICT_TRANS_TABLES |
    +---------------------+
    1 row in set (0.00 sec)
    
    --'a'无法插入
    mysql> insert into test values (4,'a');
    ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'col1' at row 1
    
    --即是第一行数据没问题，但受第二行影响，整个insert操作失败
    mysql> insert into test values (4,1),(5,'a');   
    ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'col1' at row 2
    mysql> select * from test;
    +----+------+
    | id | col1 |
    +----+------+
    |  1 |    0 |
    |  2 |    0 |
    |  3 |    0 |
    +----+------+
    3 rows in set (0.00 sec)
    
    --Myisam表，无法插入'a'
    mysql> insert into test1 values (4,'a');
    ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'col1' at row 1
    
    --Myisam表，第一行数据没问题，可以插入，'a'转换为0
    mysql> insert into test1 values (4,1),(5,'a');
    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: 'a' for column 'col1' at row 2 |
    +---------+------+---------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select * from test1;
    +----+------+
    | id | col1 |
    +----+------+
    |  1 |    0 |
    |  2 |    0 |
    |  3 |    0 |
    |  4 |    1 |
    |  5 |    0 |
    +----+------+
    5 rows in set (0.00 sec)
    
    --Myisam表，第一行'a'有问题，导致后续的数据行都插入失败
    mysql> insert into test1 values (6,'a'),(7,1);
    ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'col1' at row 1
    
    mysql> select * from test1;
    +----+------+
    | id | col1 |
    +----+------+
    |  1 |    0 |
    |  2 |    0 |
    |  3 |    0 |
    |  4 |    1 |
    |  5 |    0 |
    +----+------+
    5 rows in set (0.00 sec)
    
    --Myisam表，第三行数据正常插入
    mysql> insert into test1 values (6,1),(7,'a'),(8,1);
    Query OK, 3 rows affected, 1 warning (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 1
    
    mysql> select * from test1;
    +----+------+
    | id | col1 |
    +----+------+
    |  1 |    0 |
    |  2 |    0 |
    |  3 |    0 |
    |  4 |    1 |
    |  5 |    0 |
    |  6 |    1 |
    |  7 |    0 |
    |  8 |    1 |
    +----+------+
    8 rows in set (0.00 sec)

当SQL\_MODE设置为STRICT\_ALL\_TABLES时的Myisam表：

mysql> set @@session.sql_mode='strict_all_tables';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> select * from test1;
    +----+------+
    | id | col1 |
    +----+------+
    |  1 |    0 |
    |  2 |    0 |
    |  3 |    0 |
    |  4 |    1 |
    |  5 |    0 |
    |  6 |    1 |
    |  7 |    0 |
    |  8 |    1 |
    +----+------+
    8 rows in set (0.00 sec)
    
    --只有(9,1)插入成功
    mysql> insert into test1 values (9,1),(10,'a'),(11,1);
    ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'col1' at row 2
    mysql> select * from test1;
    +----+------+
    | id | col1 |
    +----+------+
    |  1 |    0 |
    |  2 |    0 |
    |  3 |    0 |
    |  4 |    1 |
    |  5 |    0 |
    |  6 |    1 |
    |  7 |    0 |
    |  8 |    1 |
    |  9 |    1 |
    +----+------+
    9 rows in set (0.00 sec)

可见，STRICT\_ALL\_TABLES会导致部分数据插入正常的情况。