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