mysql特定情况下日期类型自动转换为0导致业务异常一例


背景


日期类型是数据库中比较常用的一种数据类型,业务系统在操作该数据类型字段时,通常情况下都会赋值正常的日期格式数据或NULL值,这样才符合正常的业务逻辑。而MySQL在处理日期类型字段时相对比较灵活,允许某种条件下日期字段存在0值现象,但这在正常的业务逻辑上是非法的。今天所分享的案例就是这种情况,生产MySQL5.7数据库中的一张表,业务系统对该表的日期字段赋值",但MySQL数据库中存储的却是0值。


mysql特定情况下日期类型自动转换为0导致业务异常一例





分析过程


查看sql_mode参数

看到上述数据第一反应是数据库参数sql_mode被修改了,查看生产库该参数具体值

mysql特定情况下日期类型自动转换为0导致业务异常一例

  • STRICT_TRANS_TABLES:严格模式,表示一个值不能被插入一个事务表时,该sql被终止。

  • NO_ZERO_IN_DATE: 在严格模式下,不允许月、日出现0值,如 '2010-00-01' or '2010-01-00'。

  • NO_ZERO_DATE: 在严格模式下,不允许 '0000-00-00' 插入日期字段。


生产库sql_mode参数并没被修改,STRICT_TRANS_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE同时启用,是不允许0值出现在日期字段中的。


查看应用连接参数

既然生产sql_mode参数未被修改,那是否存在业务系统在连接数据库时在会话级别修改了该参数的值?为了验证这一猜想,打开MySQL数据库的generallog,收集5分钟的全量sql信息并分析:

mysql特定情况下日期类型自动转换为0导致业务异常一例

ID为25766425的会话,从最初的连接建立到发出第一个select语句间并没有对sql_mode参数的任何修改,可见业务系统应该没在会话级修改sql_mode参数。


分析binlog

接下来分析下binlog,查看哪条语句引起的该现象:

mysql特定情况下日期类型自动转换为0导致业务异常一例

分析后发现,是一条插入相应日期字段为’’值的insertignore语句引起的。




实验验证


为验证业务系统在没修改会话级别sql_mode值的前提下,仅发出上述insertignore语句便引起了该现象,做了以下实验:


创建一张测试表

mysql特定情况下日期类型自动转换为0导致业务异常一例


测试insert ignore语句

mysql特定情况下日期类型自动转换为0导致业务异常一例

测试确实产生了日期字段为0的现象,证明业务系统没修改会话级别的sql_mode值;但却出现了“日期字段bt被截取”的告警信息,说明MySQL内部做了操作。


测试insert语句

mysql特定情况下日期类型自动转换为0导致业务异常一例

insert into语句却出错,难道这两个语句在Mysql内部的处理机制不一样吗?如果插入的日期字段为NULL呢?


插入的日期字段为NULL

mysql特定情况下日期类型自动转换为0导致业务异常一例

mysql特定情况下日期类型自动转换为0导致业务异常一例

利用insertinto、insertignore into向日期字段插入NULL值时,都没报错且都是正常的日期字段合理取值。




查官方文档


mysql特定情况下日期类型自动转换为0导致业务异常一例

官方文档说明,在严格模式下,启用NO_ZERO_DATE或NO_ZERO_IN_DATE,insertignore、updateignore语句是允许插入0值到日期字段并产生一个告警。




总结

insertignore并没有被NO_ZERO_DATE或NO_ZERO_IN_DATE模式影响,MySQL把插入日期字段的值"转换为0值,二者结合产生了上述现象。因此建议在做MySQL数据库操作时,尽量避免insertignore语句的使用,在向字段插入空值时都使用NULL而非"。


上一篇:477. 汉明距离总和


下一篇:十、系统内置模块 6.系统接口模块 - os.path