缺陷的背后（三）---mysql之sql_mode为空的陷阱

妖狐艹你老母 2023-10-11 10:04 11阅读 0赞

导语
    
      mysql服务器可以在不同的sql_mode模式下运行，并且可以根据sql_mode系统变量的值，为不同的客户机应用不同的模式。sql_mode会影响mysql支持的sql语法，并且会执行数据验证检查，那不同的mysql_mode是如何影响支持的sql语法和数据类型检验的呢？本文将给大家系统总结分析说明这个问题，以及在sql_mode为空的时候，测试需要注意的测试点。

目录
    
     一、经典的“测试缺陷”
    
     二、Server SQL Modes 介绍
            2.1 sql_mode概念   
        2.2 七种常见的sql_mode 
            ONLY_FULL_GROUP_BY 
            ANSI_QUOTES
            PIPES_AS_CONCAT
            NO_AUTO_CREATE_USER 
            NO_ZERO_DATE
            NO_ZERO_IN_DATE
            STRICT_TRANS_TABLES   
        2.3 三个重要且常用的sql_mode模式  
            ANSI 
            STRICT_TRANS_TABLES 
            TRADITIONA
    
     三、测试策略
        3.1 小结：宽松，严格模式下常见sql实例影响和结果对比
        3.2 宽松模式下测试应该注意哪些地方？

# 一：经典的“测试缺陷” #

某日版本一如常规的发布上线，灰度过程中开发传来了一个“噩耗”，程序在操作数据库时有bug。

开发：第一笔数据插入都正常，第二笔数据插入就报重入了，报主键冲突，而实际这两笔数据的主键应该是不一样的，后面发现第一笔数据插入时的主键值是错误导致的。

测试：怎么会，在日志里打印的每条sql语句我都是有检查的，测试环境插入多少遍都从没报过这样的错误，而且每笔数据插入完成后，DB表的主键，业务关键字段也都是有检查的，当时我测试的记录还在，主键肯定是插入对的。

开发：在日志里查看sql语句的主键值是对的，但是插入db的主键是被截断的错误的。截断入库还不报error错误，是因为线上mysql的sql\_mode为空是宽松模式，导致数据插入时被截断，也不报error错误，业务层会以为是插入成功业务继续，从而产生了这个问题。

被截断的字段是表的主键，表的主键在DB设计长度是64，在应用程序中，主键= 批次号+订单号，批次号是外部系统返回字段，订单号由内部产生。测试同学在模块测试过程中，默认设置批次号为20190717\_02，订单号长度固定为32位，主键一直都只有43位，远小于64位，该问题一直被隐藏；而后跟外部联调时，实际传入的批次号是34位，联调时已经出现主键被截断的错误，但是由于设置的sql\_mode为空，数据截断插入不报error错误，流程正常运行结束，而且联调时又一笔通过导致问题在测试阶段被雪藏。

# 二：Server SQL Modes 介绍 #

## 2.1 sql\_mode概念 ##

sql_mode定义了MySQL应该支持的sql语法，对数据值和类型的校验程度。不同的sql_mode模式下导致mysql服务器在运行时结果不一样，有两种方式可以改变该值：
    方法一：静态修改，修改配置文件（mysql的安装目录下my.ini）后重启mysql。
    方法二：动态修改，sql_mode支持全局修改以及会话层修改。
    全局查看和全局修改：SELECT @@GLOBAL.sql_mode; SET @@global.sql_mode= 'modes';全局修改就是影响整个数据库。需要超级特权（root）才能修改,global修改后，需要重新连接进来才会生效。
    会话查看和会话修改：SELECT @@SESSION.sql_mode;SET SESSION sql_mode = 'modes';会话就是影响当前连接的会话,如果会话终止,设置的参数值失效。

## **2.2 sql\_mode常用值说明** ##

SQL Mode 定义了两个方面：MySQL应支持的SQL语法，以及应该在数据上执行何种确认检查。

### **2.2.1 常见的SQL语法支持类** ###

为了更好的举例说明不同的sql\_mode对不同sql的影响，使用表t\_payfund\_log\_20181121，其结构定义如下：

CREATE TABLE `t_payfund_log_20181121` (
      `Fbank_seq` varchar(32) NOT NULL,
      `Flast_bank_seq` varchar(32) DEFAULT NULL,
      `Fstatus` smallint(6) NOT NULL DEFAULT '0',
      `Fuid` bigint(20) DEFAULT NULL,
      `Flast_interface_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`Fbank_seq`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

表内数据暂且包含以下三条记录：

**![849009-20190915153409356-1625685493.png][]**

**`类型一：ONLY_FULL_GROUP_BY`**

**`ONLY_FULL_GROUP_BY`** 对于GROUP BY聚合操作，如果在SELECT中的列、HAVING或者ORDER BY子句的列，没有在GROUP BY中出现，那么这个SQL是不合法的。是可以理解的，因为不在 group by 的列查出来展示会有矛盾。在5.7中默认启用，所以DB在从5.6升级到5.7的过程需要注意。

示例SQL语句：SELECT Fstatus, Flast\_interface\_id FROM t\_payfund\_log\_20181121 GROUP BY Fstatus;

A: sql\_mode 为空：查询结果后正常返回

MySQL [loleina]> SELECT @@GLOBAL.sql_mode;
    +-------------------+
    | @@GLOBAL.sql_mode |
    +-------------------+
    |                   |
    +-------------------+
    1 row in set (0.00 sec)
    
    MySQL [loleina]> SELECT  Fstatus, Flast_interface_id FROM t_payfund_log_20181121 GROUP BY Fstatus;
    +---------+--------------------+
    | Fstatus | Flast_interface_id |
    +---------+--------------------+
    |       2 |             998501 |
    |       3 |              99802 |
    +---------+--------------------+
    2 rows in set (0.00 sec)

B: sql_mode=‘ONLY_FULL_GROUP_BY '：查询直接报错   （设置后重新连接登录）

MySQL [loleina]> SET GLOBAL sql_mode ='ONLY_FULL_GROUP_BY';
    Query OK, 0 rows affected (0.00 sec)
    
    MySQL [loleina]> SELECT @@GLOBAL.sql_mode;
    +--------------------+
    | @@GLOBAL.sql_mode  |
    +--------------------+
    | ONLY_FULL_GROUP_BY |
    +--------------------+
    1 row in set (0.00 sec)
    
    MySQL [loleina]> SELECT  Fstatus, Flast_interface_id FROM t_payfund_log_20181121 GROUP BY Fstatus;
    ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'loleina.t_payfund_log_20181121.Flast_interface_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

当遇到这种情况，可以使用ANY\_VALUE()，对于不符合ONLY\_FULL\_GROUP\_BY的字段使用ANY\_VALUE()函数，让MySQL跳过ONLY\_FULL\_GROUP\_BY检测

MySQL [loleina]> SELECT  Fstatus, ANY_VALUE(Flast_interface_id) FROM t_payfund_log_20181121 GROUP BY Fstatus;
    +---------+-------------------------------+
    | Fstatus | ANY_VALUE(Flast_interface_id) |
    +---------+-------------------------------+
    |       2 |                        998501 |
    |       3 |                         99802 |
    +---------+-------------------------------+
    2 rows in set (0.00 sec)

**`类型二：ANSI_QUOTES`**

启用 ANSI\_QUOTES 后，不能用双引号来引用字符串，因为它被解释为识别符，作用与 \` 一样。

示例SQL语句： update t\_payfund\_log\_20181121 set Flast\_bank\_seq="20" where Fbank\_seq='23020190915090211222002';

A: sql\_mode 为空：查询结果后正常返回.

MySQL [loleina]> SELECT @@GLOBAL.sql_mode; 
    +-------------------+
    | @@GLOBAL.sql_mode |
    +-------------------+
    |                   |
    +-------------------+
    1 row in set (0.00 sec)
    
    MySQL [loleina]> update t_payfund_log_20181121 set Flast_bank_seq="20" where Fbank_seq='23020190915090211222001';    
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

B: sql\_mode=‘ANSI\_QUOTES '：报 Unknown column '' in 'field list语法错误.

MySQL [loleina]> SELECT @@GLOBAL.sql_mode; 
    +-------------------+
    | @@GLOBAL.sql_mode |
    +-------------------+
    | ANSI_QUOTES       |
    +-------------------+
    1 row in set (0.00 sec)
    
    MySQL [loleina]> update t_payfund_log_20181121 set Flast_bank_seq="20" where Fbank_seq='23020190915090211222002'; 
    ERROR 1054 (42S22): Unknown column '20' in 'field list'

**`类型三：PIPES_AS_CONCAT`**

在Oracle，||连接字符串的，而在mysql，是用CONCAT来实现的，mysql为了兼容这一个特性，设置了这个模式,mysql会将 将 `||` 视为字符串的连接操作符而非 或 运算符，

示例SQL语句：update t\_payfund\_log\_20181121 set Fmemo=' '||Fmemo||'&pay failed' where Fbank\_seq='23020190915090211222002';

等价于： update t\_payfund\_log\_20181121 set Fmemo=CONCAT\_WS(' ',Fmemo, '&pay failed') where Fbank\_seq='23020190915090211222002';

A: sql\_mode 为空：使用||报数据截断warnings

MySQL [loleina]> SELECT @@GLOBAL.sql_mode; 
    +-------------------+
    | @@GLOBAL.sql_mode |
    +-------------------+
    |                   |
    +-------------------+
    1 row in set (0.00 sec)
    
    MySQL [loleina]>  update t_payfund_log_20181121 set Fmemo=' '||Fmemo||'&pay failed' where Fbank_seq='23020190915090211222002'; 
    Query OK, 1 row affected, 2 warnings (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 2
    
    MySQL [loleina]> show warnings;
    +---------+------+-------------------------------------------------+
    | Level   | Code | Message                                         |
    +---------+------+-------------------------------------------------+
    | Warning | 1292 | Truncated incorrect DOUBLE value: ' test2 '     |
    | Warning | 1292 | Truncated incorrect DOUBLE value: '&pay failed' |
    +---------+------+-------------------------------------------------+
    2 rows in set (0.01 sec)
    
    MySQL [loleina]>  SELECT * from loleina.t_payfund_log_20181121;
    +-------------------------+----------------+---------+--------+--------------------+--------------------+
    | Fbank_seq               | Flast_bank_seq | Fstatus | Fuid   | Flast_interface_id | Fmemo              |
    +-------------------------+----------------+---------+--------+--------------------+--------------------+
    | 23020190915090211222001 | 2              |       2 | 540977 |             998501 |  test1 &pay failed |
    | 23020190915090211222002 | 2              |       3 |  34566 |              99802 | 0                  |
    | 23020190915090211222003 | 3              |       2 |  44322 |             997601 |  test3             |
    +-------------------------+----------------+---------+--------+--------------------+--------------------+
    3 rows in set (0.00 sec)

B: sql\_mode=‘PIPES\_AS\_CONCAT '：数据正常插入。

MySQL [loleina]> SELECT @@GLOBAL.sql_mode; 
    +-------------------+
    | @@GLOBAL.sql_mode |
    +-------------------+
    | PIPES_AS_CONCAT   |
    +-------------------+
    1 row in set (0.00 sec)
    
    MySQL [loleina]>  SELECT * from loleina.t_payfund_log_20181121;
    +-------------------------+----------------+---------+--------+--------------------+---------+
    | Fbank_seq               | Flast_bank_seq | Fstatus | Fuid   | Flast_interface_id | Fmemo   |
    +-------------------------+----------------+---------+--------+--------------------+---------+
    | 23020190915090211222001 | 2              |       2 | 540977 |             998501 | test1   |
    | 23020190915090211222002 | 2              |       3 |  34566 |              99802 |  test2  |
    | 23020190915090211222003 | 3              |       2 |  44322 |             997601 |  test3  |
    +-------------------------+----------------+---------+--------+--------------------+---------+
    3 rows in set (0.00 sec)
    
    MySQL [loleina]>     update t_payfund_log_20181121 set Fmemo=' '||Fmemo||'&pay failed' where Fbank_seq='23020190915090211222001'; 
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    MySQL [loleina]>  SELECT * from loleina.t_payfund_log_20181121;
    +-------------------------+----------------+---------+--------+--------------------+--------------------+
    | Fbank_seq               | Flast_bank_seq | Fstatus | Fuid   | Flast_interface_id | Fmemo              |
    +-------------------------+----------------+---------+--------+--------------------+--------------------+
    | 23020190915090211222001 | 2              |       2 | 540977 |             998501 |  test1 &pay failed |
    | 23020190915090211222002 | 2              |       3 |  34566 |              99802 |  test2             |
    | 23020190915090211222003 | 3              |       2 |  44322 |             997601 |  test3             |
    +-------------------------+----------------+---------+--------+--------------------+--------------------+
    3 rows in set (0.00 sec)

**`类型四：NO_AUTO_CREATE_USER`**

授权命令GRANT 语句的语法：GRANT privileges (columns) ON what TO user IDENTIFIED BY "password" WITH GRANT OPTION，其中IDENTIFIED BY是可选子句，用来于指定mysql用户的口令。既然是可选，那就是可以没有。如果没有指定IDENTIFIED BY，会怎么样？

答案是可能导致一个安全漏洞：如果user是指现有用户，那么没有任何影响；但如果user是指新用户，那么该用户将不被赋予口令，这样的语句会创建一个口令为空，且已有数据库操作权限的mysql用户！那怎么办呢？mysql自身已经给出了解决方案，将sql\_mode设置为NO\_AUTO\_CREATE\_USER，mysql会阻止任何创建空密码的用户。

**`NO_AUTO_CREATE_USER`** 字面意思不自动创建用户。在给MySQL用户授权时，我们习惯使用上述类似命令 `GRANT ... ON ... TO dbuser` 一起创建用户。设置该模式后就与oracle操作类似，授权之前须先建立用户，否则会报error错误。5.7.7开始也默认了。

示例SQL语句：GRANT ALL PRIVILEGES ON \*.\* TO 'root'@'10.43.24.149';

A: sql\_mode 为空：存在安全漏洞

MySQL [loleina]> SELECT @@GLOBAL.sql_mode; 
    +-------------------+
    | @@GLOBAL.sql_mode |
    +-------------------+
    |                   |
    +-------------------+
    1 row in set (0.00 sec)
    
    MySQL [loleina]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.43.24.149';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    MySQL [loleina]>  GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.43.24.19';
    Query OK, 0 rows affected, 1 warning (0.00 sec)

B: sql\_mode=‘NO\_AUTO\_CREATE\_USER '：设置该模式：直接报错

MySQL [loleina]> SELECT @@GLOBAL.sql_mode; 
    +---------------------+
    | @@GLOBAL.sql_mode   |
    +---------------------+
    | NO_AUTO_CREATE_USER |
    +---------------------+
    1 row in set (0.00 sec)
    MySQL [loleina]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.43.24.149';
    ERROR 1133 (42000): Can't find any matching row in the user table
    MySQL [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.43.24.22';
    ERROR 1133 (42000): Can't find any matching row in the user table

那如果使用IDENTIFIED BY子句呢？虽然不会直接报error错误，但是waring错误已经明确给出”不赞成使用grant创建新用户，会在以后的版本中删除。使用CREATEUSER语句创建新用户。”

MySQL [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.43.24.22' IDENTIFIED BY 'root1234' WITH GRANT OPTION;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    MySQL [(none)]> show warnings;                                      
    +---------+------+------------------------------------------------------------------------------------------------------------------------------------+
    | Level   | Code | Message                                                                                                                            |
    +---------+------+------------------------------------------------------------------------------------------------------------------------------------+
    | Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
    +---------+------+------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

### **2.2.2 数据检查类** ###

**`类型五：NO_ZERO_DATE`**

**`NO_ZERO_DATE`** 认为日期 '0000-00-00' 是否非法，与是否设置后面的严格模式有关。 在严格模式，只有‘0000-00-00’报错，输入‘1000-00-00’不报错; 在非严格模式，可以接受该‘0000-00-00’但会生成警告。

示例sql语句：

INSERT INTO \`loleina\`.\`t\_payfund\_log\_20181121\` (\`Fbank\_seq\`, \`Fuid\`, \`Flast\_interface\_id\`, \`Fpay\_time\`, \`Fend\_time\`) VALUES ('23020190915090211222009', '540977', '998501', 0000-00-00, 0000-00-00);

A: sql\_mode 为空：'0000-00-00' 能合法插入

MySQL [loleina]> SELECT @@GLOBAL.sql_mode; 
    +-------------------+
    | @@GLOBAL.sql_mode |
    +-------------------+
    |                   |
    +-------------------+
    1 row in set (0.00 sec)
    
    
    MySQL [loleina]> INSERT INTO `loleina`.`t_payfund_log_20181121` (`Fbank_seq`, `Fuid`, `Flast_interface_id`,  `Fpay_time`, `Fend_time`) VALUES ('23020190915090211222006',  '540977', '998501', 0000-00-00, 0000-00-00);
    Query OK, 1 row affected (0.00 sec)
    
    MySQL [loleina]> select * from t_payfund_log_20181121 \g
    +-------------------------+----------------+---------+--------+--------------------+--------------------+----------------------------+---------------------+
    | Fbank_seq               | Flast_bank_seq | Fstatus | Fuid   | Flast_interface_id | Fmemo              | Fpay_time                  | Fend_time           |
    +-------------------------+----------------+---------+--------+--------------------+--------------------+----------------------------+---------------------+
    | 23020190915090211222001 | 2              |       2 | 540977 |             998501 |  test1 &pay failed | NULL                       | NULL                |
    | 23020190915090211222002 | 2              |       3 |  34566 |              99802 | 0                  | NULL                       | NULL                |
    | 23020190915090211222003 | 3              |       2 |  44322 |             997601 |  test3             | NULL                       | NULL                |
    | 23020190915090211222006 | NULL           |       0 | 540977 |             998501 | NULL               | 0000-00-00 00:00:00.000000 | 0000-00-00 00:00:00 |
    +-------------------------+----------------+---------+--------+--------------------+--------------------+----------------------------+---------------------+
    4 rows in set (0.00 sec)

B: 非严格模式设置sql\_mode =‘**`NO_ZERO_DATE`**’，输入日期=‘0000-00-00'’ 能插入但会产生告警信息：

MySQL [loleina]> SELECT @@GLOBAL.sql_mode; 
    +-------------------+
    | @@GLOBAL.sql_mode |
    +-------------------+
    | NO_ZERO_DATE      |
    +-------------------+
    1 row in set (0.00 sec)
    
    
    MySQL [loleina]> INSERT INTO `loleina`.`t_payfund_log_20181121` (`Fbank_seq`, `Fuid`, `Flast_interface_id`,  `Fpay_time`, `Fend_time`) VALUES ('23020190915090211222008',  '540977', '998501', 0000-00-00, 0000-00-00);
    Query OK, 1 row affected, 2 warnings (0.00 sec)
    
    MySQL [loleina]> show warnings;
    +---------+------+----------------------------------------------------+
    | Level   | Code | Message                                            |
    +---------+------+----------------------------------------------------+
    | Warning | 1264 | Out of range value for column 'Fpay_time' at row 1 |
    | Warning | 1264 | Out of range value for column 'Fend_time' at row 1 |
    +---------+------+----------------------------------------------------+
    2 rows in set (0.00 sec)
    
    MySQL [loleina]> select * from t_payfund_log_20181121 \g
    +-------------------------+----------------+---------+--------+--------------------+--------------------+----------------------------+---------------------+
    | Fbank_seq               | Flast_bank_seq | Fstatus | Fuid   | Flast_interface_id | Fmemo              | Fpay_time                  | Fend_time           |
    +-------------------------+----------------+---------+--------+--------------------+--------------------+----------------------------+---------------------+
    | 23020190915090211222001 | 2              |       2 | 540977 |             998501 |  test1 &pay failed | NULL                       | NULL                |
    | 23020190915090211222002 | 2              |       3 |  34566 |              99802 | 0                  | NULL                       | NULL                |
    | 23020190915090211222003 | 3              |       2 |  44322 |             997601 |  test3             | NULL                       | NULL                |
    | 23020190915090211222006 | NULL           |       0 | 540977 |             998501 | NULL               | 0000-00-00 00:00:00.000000 | 0000-00-00 00:00:00 |
    | 23020190915090211222008 | NULL           |       0 | 540977 |             998501 | NULL               | 0000-00-00 00:00:00.000000 | 0000-00-00 00:00:00 |
    +-------------------------+----------------+---------+--------+--------------------+--------------------+----------------------------+---------------------+
    5 rows in set (0.00 sec)

C: 严格模式下设置sql\_mode =‘**`NO_ZERO_DATE`**’，输入日期=‘0000-00-00'’ 插入直接报错，输入‘000-00-00’则不报错

MySQL [loleina]>  SELECT @@GLOBAL.sql_mode; 
    +----------------------------------+
    | @@GLOBAL.sql_mode                |
    +----------------------------------+
    | STRICT_TRANS_TABLES,NO_ZERO_DATE |
    +----------------------------------+
    1 row in set (0.00 sec)
    
    MySQL [loleina]> INSERT INTO `loleina`.`t_payfund_log_20181121` (`Fbank_seq`, `Fuid`, `Flast_interface_id`,  `Fpay_time`, `Fend_time`) VALUES ('23020190915090211222009',  '540977', '998501', 0000-00-00, 0000-00-00);
    ERROR 1292 (22007): Incorrect datetime value: '0' for column 'Fpay_time' at row 1
    
    INSERT INTO `loleina`.`t_payfund_log_20181121` (`Fbank_seq`, `Fuid`, `Flast_interface_id`,  `Fpay_time`, `Fend_time`) VALUES ('23020190915090211222009',  '540977', '998501', 1000-00-00, '1999-01-01 11:11:11');
    Query OK, 1 row affected (0.00 sec)

类型六：NO\_ZERO\_IN\_DATE

NO\_ZERO\_IN\_DATE 认为日期 '0000-00-00' 是否非法，与是否设置后面的严格模式有关。 在严格模式，只有‘0000-00-00’能正常插入，输入‘1000-00-00’报错; 在非严格模式，可以接受‘0000-00-00’，但会生成警告。NO\_ZERO\_IN\_DATE ：MySQL中插入的时间字段值，支持‘0000-00-00’插入， 但是只要日期的月和日中含有0值就跟严格模式设置有关。

示例sql语句：

INSERT INTO `loleina`.`t_payfund_log_20181121` (`Fbank_seq`, `Fuid`, `Flast_interface_id`,  `Fpay_time`, `Fend_time`) VALUES ('23020190915090211222010',  '540977', '998501', 0000-00-00, 0000-00-00);

A: 非严格模式设置sql\_mode =‘NO\_ZERO\_IN\_DATE ’，支持日期=‘0000-00-00'，输入日期=‘1000-00-00'则告警

MySQL [loleina]> SELECT @@GLOBAL.sql_mode; 
    +-------------------+
    | @@GLOBAL.sql_mode |
    +-------------------+
    | NO_ZERO_IN_DATE   |
    +-------------------+
    1 row in set (0.00 sec)
    
    
    MySQL [loleina]> INSERT INTO `loleina`.`t_payfund_log_20181121` (`Fbank_seq`, `Fuid`, `Flast_interface_id`,  `Fpay_time`, `Fend_time`) VALUES ('23020190915090211222010',  '540977', '998501', 0000-00-00, 0000-00-00);
    Query OK, 1 row affected (0.00 sec)
    
    MySQL [loleina]> INSERT INTO `loleina`.`t_payfund_log_20181121` (`Fbank_seq`, `Fuid`, `Flast_interface_id`,  `Fpay_time`, `Fend_time`) VALUES ('23020190915090211222011',  '540977', '998501', 1000-00-00, 0000-00-00);
    Query OK, 1 row affected, 1 warning (0.00 sec)
    
    MySQL [loleina]> show warnings;
    +---------+------+------------------------------------------------+
    | Level   | Code | Message                                        |
    +---------+------+------------------------------------------------+
    | Warning | 1265 | Data truncated for column 'Fpay_time' at row 1 |
    +---------+------+------------------------------------------------+
    1 row in set (0.00 sec)
    
    
    MySQL [loleina]> SELECT * FROM t_payfund_log_20181121 WHERE Fbank_seq = '23020190915090211222012' \g
    +-------------------------+----------------+---------+--------+--------------------+-------+----------------------------+---------------------+
    | Fbank_seq               | Flast_bank_seq | Fstatus | Fuid   | Flast_interface_id | Fmemo | Fpay_time                  | Fend_time           |
    +-------------------------+----------------+---------+--------+--------------------+-------+----------------------------+---------------------+
    | 23020190915090211222012 | NULL           |       0 | 540977 |             998501 | NULL  | 0000-00-00 00:00:00.000000 | 0000-00-00 00:00:00 |
    +-------------------------+----------------+---------+--------+--------------------+-------+----------------------------+---------------------+
    1 row in set (0.00 sec)

B: 严格模式下设置sql\_mode =‘NO\_ZERO\_IN\_DATE ’，输入日期=‘0000-00-00'’告警，输入‘1000-00-00’则直接报错

MySQL [loleina]> SELECT @@GLOBAL.sql_mode; 
    +-------------------------------------+
    | @@GLOBAL.sql_mode                   |
    +-------------------------------------+
    | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE |
    +-------------------------------------+
    1 row in set (0.00 sec)
    
    MySQL [loleina]> INSERT INTO `loleina`.`t_payfund_log_20181121` (`Fbank_seq`, `Fuid`, `Flast_interface_id`,  `Fpay_time`, `Fend_time`) VALUES ('23020190915090211222013',  '540977', '998501', 0000-00-00, 0000-00-00);
    Query OK, 1 row affected (0.00 sec)
    
    MySQL [loleina]> SELECT * FROM t_payfund_log_20181121 WHERE Fbank_seq = '23020190915090211222013' \g
    +-------------------------+----------------+---------+--------+--------------------+-------+----------------------------+---------------------+
    | Fbank_seq               | Flast_bank_seq | Fstatus | Fuid   | Flast_interface_id | Fmemo | Fpay_time                  | Fend_time           |
    +-------------------------+----------------+---------+--------+--------------------+-------+----------------------------+---------------------+
    | 23020190915090211222013 | NULL           |       0 | 540977 |             998501 | NULL  | 0000-00-00 00:00:00.000000 | 0000-00-00 00:00:00 |
    +-------------------------+----------------+---------+--------+--------------------+-------+----------------------------+---------------------+
    1 row in set (0.00 sec)
    
    MySQL [loleina]> INSERT INTO `loleina`.`t_payfund_log_20181121` (`Fbank_seq`, `Fuid`, `Flast_interface_id`,  `Fpay_time`, `Fend_time`) VALUES ('23020190915090211222013',  '540977', '998501', 1000-00-00, 0000-00-00);
    ERROR 1292 (22007): Incorrect datetime value: '1000' for column 'Fpay_time' at row 1

**`类型七：STRICT_TRANS_TABLES`**

`严格模式，STRICT_TRANS_TABLES` 不是几种策略的组合，单独指 `INSERT`、`UPDATE`出现少值或无效值该如何处理，考虑以下5种情况：

1、整型字段数据超长，DB设置Fstatus长度为6，实际插入数据为12345678

2、字符串字段数据超长，DB设置Fmemo长度为25，实际插入数据为30.FmemoFmemoFmemoFmemoFmemoFmemo

3、设置Fstatu的默认值为0，插入时带这个字段，数据为''

4、设置Fstatu的默认值为空，插入时不带这个字段

5、Fststus字段为int类型，但是插入string类型的数据：abc

A: sql\_mode 为空：报error错误：

1、整型字段长度超长溢出，mysql自动校正为类型最长值插入DB，风险很高

MySQL [loleina]> SELECT @@GLOBAL.sql_mode; 
    +-------------------+
    | @@GLOBAL.sql_mode |
    +-------------------+
    |                   |
    +-------------------+
    1 row in set (0.01 sec)
    
    MySQL [loleina]> INSERT INTO `loleina`.`t_payfund_log_20181121` (`Fbank_seq`, `Flast_bank_seq`, `Fstatus`, `Fuid`, `Flast_interface_id`, `Fmemo`, `Fpay_time`) VALUES ('23020190917090211222001', '3', '12345678', '44322', '997601', 'Fmemo', NULL);
    Query OK, 1 row affected, 1 warning (0.00 sec)
    
    MySQL [loleina]> show warnings;
    +---------+------+--------------------------------------------------+
    | Level   | Code | Message                                          |
    +---------+------+--------------------------------------------------+
    | Warning | 1264 | Out of range value for column 'Fstatus' at row 1 |
    +---------+------+--------------------------------------------------+
    1 row in set (0.00 sec)
    
    MySQL [loleina]> select *  from t_payfund_log_20181121 where Fbank_seq='23020190917090211222001' \G
    *************************** 1. row ***************************
             Fbank_seq: 23020190917090211222001
        Flast_bank_seq: 3
               Fstatus: 32767
                  Fuid: 44322
    Flast_interface_id: 997601
                 Fmemo: Fmemo
             Fpay_time: NULL
    1 row in set (0.00 sec)

2、字符串字段数据超长被截断保存到DB

MySQL [loleina]> INSERT INTO `loleina`.`t_payfund_log_20181121` (`Fbank_seq`, `Flast_bank_seq`, `Fstatus`, `Fuid`, `Flast_interface_id`, `Fmemo`, `Fpay_time`) VALUES ('23020190917090211222002', '3', '2', '44322', '997601', 'FmemoFmemoFmemoFmemoFmemoFmemo', NULL);
    Query OK, 1 row affected, 1 warning (0.00 sec)
    
    MySQL [loleina]> show warnings;
    +---------+------+--------------------------------------------+
    | Level   | Code | Message                                    |
    +---------+------+--------------------------------------------+
    | Warning | 1265 | Data truncated for column 'Fmemo' at row 1 |
    +---------+------+--------------------------------------------+
    1 row in set (0.00 sec)
    
    MySQL [loleina]> select *  from t_payfund_log_20181121 where Fbank_seq='23020190917090211222002' \G
    *************************** 1. row ***************************
             Fbank_seq: 23020190917090211222002
        Flast_bank_seq: 3
               Fstatus: 2
                  Fuid: 44322
    Flast_interface_id: 997601
                 Fmemo: FmemoFmemoFmemoFmemo
             Fpay_time: NULL
    1 row in set (0.00 sec)

3、设置Fstatu的默认值为0，插入时带这个字段，但数据为''，实际插入为0

4、设置Fstatu的默认值为空，插入时不带这个字段，实际插入0

MySQL [loleina]> INSERT INTO `loleina`.`t_payfund_log_20181121` (`Fbank_seq`, `Flast_bank_seq`, `Fuid`, `Flast_interface_id`, `Fmemo`) VALUES ('23020190917090211222004', '3', '44322', '997601', 'Fmemo');
    Query OK, 1 row affected, 1 warning (0.01 sec)
    
    MySQL [loleina]> show warnings;
    +---------+------+----------------------------------------------+
    | Level   | Code | Message                                      |
    +---------+------+----------------------------------------------+
    | Warning | 1364 | Field 'Fstatus' doesn't have a default value |
    +---------+------+----------------------------------------------+
    1 row in set (0.00 sec)
    
    MySQL [loleina]> select *  from t_payfund_log_20181121 where Fbank_seq='23020190917090211222004' \G
    *************************** 1. row ***************************
             Fbank_seq: 23020190917090211222004
        Flast_bank_seq: 3
               Fstatus: 0
                  Fuid: 44322
    Flast_interface_id: 997601
                 Fmemo: Fmemo
             Fpay_time: NULL
    1 row in set (0.00 sec)

5、Fststus字段为int类型，但是插入string类型的数据：abc，实际插入为0

MySQL [loleina]> INSERT INTO `loleina`.`t_payfund_log_20181121` (`Fbank_seq`, `Flast_bank_seq`, `Fstatus`, `Fuid`, `Flast_interface_id`, `Fmemo`, `Fpay_time`) VALUES ('23020190916090211222005', '3', 'ABC', '44322', '997601', 'Fmemo', NULL);
    Query OK, 1 row affected, 1 warning (0.00 sec)
    
    MySQL [loleina]> show warnings;
    +---------+------+--------------------------------------------------------------+
    | Level   | Code | Message                                                      |
    +---------+------+--------------------------------------------------------------+
    | Warning | 1366 | Incorrect integer value: 'ABC' for column 'Fstatus' at row 1 |
    +---------+------+--------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    MySQL [loleina]> select *  from t_payfund_log_20181121 where Fbank_seq='23020190917090211222005' \G
    Empty set (0.00 sec)
    
    MySQL [loleina]> select *  from t_payfund_log_20181121 where Fbank_seq='23020190916090211222005' \G
    *************************** 1. row ***************************
             Fbank_seq: 23020190916090211222005
        Flast_bank_seq: 3
               Fstatus: 0
                  Fuid: 44322
    Flast_interface_id: 997601
                 Fmemo: Fmemo
             Fpay_time: NULL
    1 row in set (0.00 sec)

B : sql\_mode =‘STRICT\_TRANS\_TABLES ’设置为严格模式，1-5插入数据全失败，报error错误：

MySQL [loleina]> SELECT @@GLOBAL.sql_mode; 
    +---------------------+
    | @@GLOBAL.sql_mode   |
    +---------------------+
    | STRICT_TRANS_TABLES |
    +---------------------+
    1 row in set (0.00 sec)
    
    
    整型超长
    MySQL [loleina]> INSERT INTO `loleina`.`t_payfund_log_20181121` (`Fbank_seq`, `Flast_bank_seq`, `Fstatus`, `Fuid`, `Flast_interface_id`, `Fmemo`, `Fpay_time`) VALUES ('23020190916090211222001', '3', '12345678', '44322', '997601', 'Fmemo', NULL);
    ERROR 1264 (22003): Out of range value for column 'Fstatus' at row 1
    
    字符串超长
    MySQL [loleina]> INSERT INTO `loleina`.`t_payfund_log_20181121` (`Fbank_seq`, `Flast_bank_seq`, `Fstatus`, `Fuid`, `Flast_interface_id`, `Fmemo`, `Fpay_time`) VALUES ('23020190916090211222001', '3', '2', '44322', '997601', 'FmemoFmemoFmemoFmemoFmemoFmemo', NULL);
    ERROR 1406 (22001): Data too long for column 'Fmemo' at row 1
    
    设置Fstatu的默认值为0，插入时带这个字段，但是设置为''
    MySQL [loleina]> INSERT INTO `loleina`.`t_payfund_log_20181121` (`Fbank_seq`, `Flast_bank_seq`, `Fstatus`, `Fuid`, `Flast_interface_id`, `Fmemo`, `Fpay_time`) VALUES ('23020190916090211222001', '3', '', '44322', '997601', Fmemo, NULL);
    ERROR 1366 (HY000): Incorrect integer value: '' for column 'Fstatus' at row 1
    
    设置Fstatu的默认值为''
    MySQL [loleina]> INSERT INTO `loleina`.`t_payfund_log_20181121` (`Fbank_seq`, `Flast_bank_seq`, `Fuid`, `Flast_interface_id`, `Fmemo`) VALUES ('23020190916090211222004', '3', '44322', '997601', 'Fmemo');
    ERROR 1364 (HY000): Field 'Fstatus' doesn't have a default value
    
    Fststus为int，但是插入string类型
    MySQL [loleina]> INSERT INTO `loleina`.`t_payfund_log_20181121` (`Fbank_seq`, `Flast_bank_seq`, `Fstatus`, `Fuid`, `Flast_interface_id`, `Fmemo`, `Fpay_time`) VALUES ('23020190916090211222005', '3', 'ABC', '44322', '997601', 'Fmemo', NULL);
    ERROR 1366 (HY000): Incorrect integer value: 'ABC' for column 'Fstatus'

## 2.3、三个重要且常用的sql\_mode模式 ##

## 2.3.1ANSI ##

此模式下，更接近标准sql。包含 REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ONLY_FULL_GROUP_BY.
    
    宽松模式，对插入数据进行校验，如果不符合定义类型或长度，会对数据类型调整或截断保存，报warning警告。

### 2.3.2 `STRICT_TRANS_TABLES` ###

该选项针对事务性存储引擎生效，对于非事务性存储引擎无效，该选项表示开启strict sql模式。在strict sql模式下，在INSERT或者UPDATE语句中，插入或者更新了某个不符合规定的字段值，则会直接报错中断操作。包含： `ERROR_FOR_DIVISION_BY_ZERO`, `NO_ZERO_DATE`, and `NO_ZERO_IN_DATE` modes. 从mysql 5.7.5开始，默认的sql模式包括此模式。

严格模式，进行数据的严格校验，错误数据不能插入，报error错误。

### 2.3.3 `TRADITIONAL` ###

严格模式，当向mysql数据库插入数据时，进行数据的严格校验，保证错误数据不能插入，报error错误。用于事务时，会进行事物的回滚。

# 三：测试策略 #

##  3.1 小结：sql\_mode=''和其他模式下影响的sql以及结果对比 ##

![849009-20190921152345908-551225688.png][]

## 3.2 宽松模式下测试应该注意哪些地方？ ##

1、怎么查看出现的warings？

使用show warnings查看最新warings日志，但在多客户端使用的情况下，这个日志很快就会被刷新而捕捉不到。  在非严格模式下，一些mysql语句会报error错，这类error错误不会记日志，使用show warnings可以看见。show warnings是一个诊断语句，它显示有关在当前会话中最新的执行语句所导致的条件（错误、警告和注释）的信息。
    
    2、可以把现网的sql_mode更改为严格模式来防止一些问题的产生吗？

实际一般现网运营的DB设置的模式都是严格模式，连mysql的5.7以上的版本默认都是严格模式，但是可能有些系统运行很久了，在mysql进行升级的时候，可以把sql_mode从原来的宽松模式更改为严格模式吗？
        现网是不可以的，mysql官网明确指出：“在创建好表和插入数据到分区表中之后，更改服务器SQL模式可能会导致此类表的行为发生重大变化，并可能导致数据丢失或损坏。强烈建议在使用用户定义的分区创建表后，不
    要更改sql模式。当复制分区表时，主和从机上不同的SQL模式也会导致问题。为了获得最佳结果，您应该始终在主服务器和从服务器上使用相同的服务器sql模式。”
    
    3、宽松模式下使用mysql数据库测试需要注意什么呢？
    
        A：注意表的字段的长度和数据类型设计是否合理。因为在长度设计过短或者数据类型设计错误时，宽松模式下插入或者更新是不会直接报error错误的。
        B: 检查每一笔数据插入时，字段值的正确性。

转载于:https://www.cnblogs.com/loleina/p/11518535.html

[849009-20190915153409356-1625685493.png]: https://img2018.cnblogs.com/blog/849009/201909/849009-20190915153409356-1625685493.png
[849009-20190921152345908-551225688.png]: https://img2018.cnblogs.com/blog/849009/201909/849009-20190921152345908-551225688.png