| 专题描述 | 详细描述SQL_MODE为严格模式下可能出现的数据错误风险。 | ||||
| 问题提出 | 1 | 日期默认’0000-00-00 00:00:00’的，查询时会直接报错。 Caused by: java.sql.SQLException: Value ‘0000-00-00 00:00:00’ can not be represented as java.sql.Timestamp URL需添加如下参数：zeroDateTimeBehavior=convertToNull。 |
|||
| 2 | OMS数据库因UAT环境和性能测试环境的参数配置不同，导致性能测试环境下应用SQL执行报错，相关参数为STRICT_TRANS_TABLES与explicit_defaults_for_timestamp。 | ||||
| 数据风险 | 1 | 整型类型数据值截断风险 |
重点关注 TINYINT类型 ，其他整数类型上线初期溢出概率低。 以“`payment_display_mode` tinyint(4) DEFAULT NULL COMMENT ‘付款到期日显示方式: 11-最晚汇款日 12-到期日’,” 为例。 示例：在非严格模式下，应用程序为 字段 payment_display_mode 设置一个值 小于-128 或大于127 ， 则超过部分会被截断（超过-128，则存储为-128，例如 -200 会默认变成 -128；超过127，则存储为127），例如 300 会默认变成127。 |
||
| 2 | 浮点类型截断 |
以“`longitude` DECIMAL(19,5) NULL DEFAULT ‘0.00000’ COMMENT ‘经度坐标’” 为例。 示例：在非严格模式下，应用程序为 字段 longitude 设置一个值 超过 5位小数或者整数超过14位， 则超过部分会被截断。 |
|||
| 3 | 字符串类型风险 |
以“`MODIFIER_USER` VARCHAR(30) NUL L DEFAULT NULL COMMENT ‘更新者用户账号’,” 为例。 示例：在非严格模式下， 应用程序为 字段 MODIFIER_USER 设置一个值 超过 30个字符，则超过部分会被截断。 |
|||
| 4 | 时间类型风险 | 由于JAVA代码认为0000-00-00 00:00:00时间是非法的时间，JDBC中提供参数控制对它的处理， 故可通过添加zeroDateTimeBehavior=convertToNull使其转化为null。
连接字符串示例： |
|||
| 5 | NOT NULL类型风险 |
非严格模式下，不给字段定义约束 NOT NULL字段赋值 或 给其一个NULL值， 在无定义约束无默认值的情况下：数据值类型的 字段值默认为0，字符串类型的字段值默认为空字符串”。从SQL语句和存储过程处理分析，多数情况下都兼容处理了，但依然请研发团队谨慎评估。 |
|||
| 问题原因 | 数据库SQL_MODE设置为严格模式。 | ||||
| 解决方案 | 1 | 建议DBA团队设置SQL_MODE为严格模式，运行系统进行测试，将根据报错定位存在的数据隐患，修改程序。 | |||
| 2 | 设置服务器SQL_MODE为严格模式。 | ||||
| 知识点 | 1 |
数据库SQL_MODE设置为严格模式将出现问题分为以下几类 ：1、日期默认’0000-00-00 00:00:00’的；查询时会直接报错， 2、字段类型设置为NOT NULL时，插入NULL值将报错； 3、当插入超出数值字段类型范围的值时，直接报错； |
|||
| 2 | STRICT_TRANS_TABLES释义:
官方文档链接： http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-strict 官方文档解释：For transactional tables, an error occurs for invalid or missing values in a data-change statement when either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled. The statement is aborted and rolled back. |
||||
| 3 |
数据库SQL_MODE设置为非严格模式时，系统上述情况倾向于警告 。插入超过数值类型范围字段值时系统取最大值插入， 插入字符类型字段值超过长度范围时会截断。 |
||||
