资讯专栏INFORMATION COLUMN

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

IT那活儿 / 2200人阅读
mysql特定情况下日期类型自动转换为0导致业务异常一例


背景



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






分析过程



查看sql_mode参数

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

  • 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信息并分析:

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


分析binlog

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

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




    实验验证



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


创建一张测试表


测试insert ignore语句

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


测试insert语句

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


插入的日期字段为NULL

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




查官方文档



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




总结


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

文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。

转载请注明本文地址:https://www.ucloud.cn/yun/130207.html

相关文章

  • DataX在有赞大数据平台的实践

    摘要:与大数据体系交互上报运行统计数据自带了运行结果的统计数据,我们希望把这些统计数据上报到元数据系统,作为的过程元数据存储下来。基于我们的开发策略,不要把有赞元数据系统的嵌入源码,而是在之外获取,截取出打印的统计信息再上报。一、需求 有赞大数据技术应用的早期,我们使用 Sqoop 作为数据同步工具,满足了 MySQL 与 Hive 之间数据同步的日常开发需求。 随着公司业务发展,数据同步的场景越...

    JerryWangSAP 评论0 收藏0
  • 美团外卖Android Crash治理之路

    摘要:通过团队的全力全策,美团外卖的平均率从千分之三降到了万分之二,最优值万一左右率统计方式次数。美团外卖自年创建以来,业务就以指数级的速度发展。目前美团外卖日完成订单量已突破万,成为美团点评最重要的业务之一。 面试中常常问到的是Android的性能优化以及Crash处理。 今天我们来学习一下啊美团App的Crash处理。更多参考《Android性能优化:手把手带你全面实现内存优化》 原为地...

    elva 评论0 收藏0

发表评论

0条评论

IT那活儿

|高级讲师

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<