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

导语

  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;

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

类型一：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

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)

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以及结果对比

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: 检查每一笔数据插入时，字段值的正确性。

posted @ 2019-09-21 17:15 loleina 阅读(3121) 评论(0) 编辑收藏举报

刷新页面返回顶部

loleina

当你决定了方向，勇气可以带你走得更远。

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

一：经典的“测试缺陷”

二：Server SQL Modes 介绍

2.1 sql_mode概念

2.2 sql_mode常用值说明

2.2.1 常见的SQL语法支持类

2.2.2 数据检查类

2.3、三个重要且常用的sql_mode模式

2.3.1ANSI

2.3.2 `STRICT_TRANS_TABLES`

2.3.3 `TRADITIONAL`

三：测试策略

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

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

公告

loleina

当你决定了方向，勇气可以带你走得更远。

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

一：经典的“测试缺陷”

二：Server SQL Modes 介绍

2.1 sql_mode概念

2.2 sql_mode常用值说明

2.2.1 常见的SQL语法支持类

2.2.2 数据检查类

2.3、三个重要且常用的sql_mode模式

2.3.1ANSI

2.3.2 STRICT_TRANS_TABLES

2.3.3 TRADITIONAL

三：测试策略

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

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

公告

2.3.2 `STRICT_TRANS_TABLES`

2.3.3 `TRADITIONAL`