精华内容
下载资源
问答
  • 如果考虑产品版本控制,数据量过大,数据统计和数据分析这些问题以及考虑到学习的性价比和技术的价值最大化但是由于vba的历史遗留问题和一些企业的限制安装软件,那还是老老实实的躺好,用vba解决问题。我们先看一下...

    6073ff3d1443271b49ad5c8750d40a74.png

    如果处理相对简单以及数据量不大,建议使用vba。

    如果考虑产品版本控制,数据量过大,数据统计和数据分析这些问题

    以及考虑到学习的性价比和技术的价值最大化

    482016a9220384e7bf2f006cad834521.png

    但是由于vba的历史遗留问题和一些企业的限制安装软件,那还是老老实实的躺好,用vba解决问题。


    我们先看一下某wc两者的大纲

    vba:

    1cfba524f41a1bc880b099ef096db8a7.png

    Python:

    9f0d14adbe782e0ecf169f8a8e2e6d26.png

    有很多东西是相似的,语言基础全家桶【变量、数据类型、运算符、选择和循环语句、函数、模块】,虽然看上去差别比较大的东西,但是实质大家都还是编程,那一套基础的东西都差不多。所以学习难度应该也差不多,这里只考虑用python搞定excel,其他不管。

    python如果只考虑处理excel,也就学一下基础语法,再学习几个模块即可。知识体系也不是很庞大。而且这个技术,后边还可以干一些其他事!

    e243972405218c7a39ea4efbcfa2dc5f.png

    处理excel的库有很多,但是一个pandas其实就够了。

    70c8889ed1b4f94dc218a458e7c1c97a.png

    pandas是一个强大的分析结构化数据的工具集;它的使用基础是numpy(提供高性能的矩阵运算);用于数据挖掘和数据分析,同时也提供数据清洗功能。

    用来操作数据的话和excel差不多,比如一些分列,合并,排序功能等等。而且里边最基础的数据帧其实就是excel的表格。

    但是

    vba处理完excel,你还能做什么?好像也只能处理excel了。

    python处理完excel,你还能做什么?再学习个requests与xpath就可以批量获取数据或者下载小说图片,再学习个pyecharts与seaborn可以出一些复杂的图表,有兴趣还可以搞搞机器学习……

    Python能做方向,可以参考我的另一篇文章。

    https://zhuanlan.zhihu.com/p/136982854

    展开全文
  • 显然,这是不合法的日期值,但由于设计问题或历史遗留问题,有时候数据库中有类似日期值为零的数据,默认情况下插入零值日期会报错,可以通过修改参数sql_mode模式来避免该问题。下面展示下默认情况下插入零值的情况...

    前言:

    前面文章我们介绍过日期和时间字段的查询方法,最近遇到日期值为零的问题。原来了解过和 sql_mode 参数设置有关,但还不是特别清楚,本篇文章将探究下MySQL怎么处理日期值为零的问题。

    1.问题描述

    这里我们说的日期为零值是指年、月、日为零,即'0000-00-00'。显然,这是不合法的日期值,但由于设计问题或历史遗留问题,有时候数据库中有类似日期值为零的数据,默认情况下插入零值日期会报错,可以通过修改参数sql_mode模式来避免该问题。下面展示下默认情况下插入零值的情况:

    # 首先创建测试表
    CREATE TABLE `t_zerodate` (
      `increment_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
      `year_col` year DEFAULT NULL COMMENT '年',
      `date_col` date DEFAULT NULL COMMENT '日期',
      `dt_col` datetime DEFAULT NULL COMMENT 'datetime时间',
      `ts_col` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'timestamp时间',
      PRIMARY KEY (`increment_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='zerodate';
    
    # 查看sql_mode模式
    mysql> select @@sql_mode;
    +----------------------------------------------------------------------------------------------------+
    | @@sql_mode                                                                                         |
    +----------------------------------------------------------------------------------------------------+
    | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
    +----------------------------------------------------------------------------------------------------+
    
    # 分别插入年、月、日为零值测试
    # 年份为0000 插入成功
    mysql> insert into t_zerodate (year_col) values (0000);
    Query OK, 1 row affected (0.02 sec)
    
    # 月、日都不为零时 可插入成功
    mysql> insert into t_zerodate (date_col) values ('0000-00-00');
    ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'date_col' at row 1
    mysql> insert into t_zerodate (date_col) values ('0000-01-00'); 
    ERROR 1292 (22007): Incorrect date value: '0000-01-00' for column 'date_col' at row 1
    mysql> insert into t_zerodate (date_col) values ('0000-00-01');
    ERROR 1292 (22007): Incorrect date value: '0000-00-01' for column 'date_col' at row 1
    mysql> insert into t_zerodate (date_col) values ('0000-01-01'); 
    Query OK, 1 row affected (0.02 sec)

    2.sql_mode变更测试

    关于sql_mode,原来写过一篇文章,sql_mode支持多个变量的不同组合,不同的sql_mode影响服务端支持的SQL语法以及数据校验规则。其中 NO_ZERO_IN_DATENO_ZERO_DATE这两个变量影响MySQL对日期零值的处理。上面测试中可以发现,严格模式下,当sql_mode中包含NO_ZERO_IN_DATE,NO_ZERO_DATE两个变量时,月和日都不为零时可以插入成功。

    乍一看,NO_ZERO_IN_DATE和NO_ZERO_DATE两个变量很相似,但作用有什么不同呢?下面我们给出这两个变量的作用并做下具体测试。

    NO_ZERO_DATE模式影响服务端是否允许将 '0000-00-00' 作为有效日期。其效果还取决于sql_mode是否启用了严格模式。

    • 如果未启用此模式,'0000-00-00'则允许插入并且不会产生警告。
    • 如果只启用此模式,'0000-00-00'则允许插入但是会产生警告。
    • 如果启用了此模式和严格模式,'0000-00-00'则会被认定为非法,并且插入也会产生错误。除非同时带有IGNORE,对于 INSERT IGNORE和UPDATE IGNORE,'0000-00-00'则允许插入但是会产生警告。

    NO_ZERO_IN_DATE模式影响服务端是否允许插入年份部分非零但月或日部分为0的日期。(例如'2010-00-01'或 '2010-01-00',但不影响日期'0000-00-00'),其效果同样还取决于sql_mode是否启用了严格模式。

    • 如果未启用此模式,则允许部分为零的日期插入,并且不会产生任何警告。
    • 如果只启用此模式,则将该零值日期插入为'0000-00-00'并产生警告。
    • 如果启用了此模式和严格模式,则除非IGNORE同时指定,否则不允许插入为零的日期。对于INSERT IGNORE和 UPDATE IGNORE,将该零值日期插入为'0000-00-00'并产生警告。

    同时,官方文档中指出:NO_ZERO_DATE和NO_ZERO_IN_DATE虽然不是严格模式的一部分,但应与严格模式结合使用,如果在未启用严格模式的情况下启用了NO_ZERO_DATE或NO_ZERO_IN_DATE则会产生警告,反之亦然,(sql_mode中包含STRICT_TRANS_TABLES,一般可认为启用了严格模式)。

    下面我们来测试下,严格模式下分别启用和不启用这两个变量的效果:

    1.严格模式下 同时启用NO_ZERO_DATE、NO_ZERO_IN_DATE
    
    # 上文已经测试过,正常情况下 月、日都不为零时 可插入成功
    # 下面测试insert ignore into
    mysql> set session sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> truncate table t_zerodate;
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> insert ignore into t_zerodate (date_col) values ('0000-00-00');
    Query OK, 1 row affected, 1 warning (0.01 sec)
    
    mysql> insert ignore into t_zerodate (date_col) values ('2010-00-00');    
    Query OK, 1 row affected, 1 warning (0.01 sec)
    
    mysql> insert ignore into t_zerodate (date_col) values ('2010-01-00'); 
    Query OK, 1 row affected, 1 warning (0.01 sec)
    
    mysql> insert ignore into t_zerodate (date_col) values ('2010-00-01');
    Query OK, 1 row affected, 1 warning (0.01 sec)
    
    mysql> select date_col from t_zerodate;
    +------------+
    | date_col   |
    +------------+
    | 0000-00-00 |
    | 0000-00-00 |
    | 0000-00-00 |
    | 0000-00-00 |
    +------------+
    
    # 结论:
    正常情况下 月、日都不为零时 可插入成功;
    对于INSERT IGNORE则允许插入,但会产生告警,并会将零值日期保存为'0000-00-00'。
    
    2.严格模式下 不启用NO_ZERO_DATE
    
    mysql> set session sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    mysql> truncate table t_zerodate;
    Query OK, 0 rows affected (0.08 sec)
    
    mysql> insert into t_zerodate (date_col) values ('0000-00-00');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> insert into t_zerodate (date_col) values ('2010-00-01');
    ERROR 1292 (22007): Incorrect date value: '2010-00-01' for column 'date_col' at row 1
    mysql> insert into t_zerodate (date_col) values ('2010-01-00'); 
    ERROR 1292 (22007): Incorrect date value: '2010-01-00' for column 'date_col' at row 1
    mysql> insert into t_zerodate (date_col) values ('0000-00-01');
    ERROR 1292 (22007): Incorrect date value: '0000-00-01' for column 'date_col' at row 1
    mysql> insert into t_zerodate (date_col) values ('0000-01-00');
    ERROR 1292 (22007): Incorrect date value: '0000-01-00' for column 'date_col' at row 1
    
    # 结论:
    '0000-00-00'可以正常插入,其余月、日为零的日期插入会报错;
    对于INSERT IGNORE则允许插入,但会产生告警,并会将零值日期保存为'0000-00-00'。
    
    3.严格模式下 不启用NO_ZERO_IN_DATE
    
    mysql> set session sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> truncate table t_zerodate;
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> insert into t_zerodate (date_col) values ('0000-00-00');
    ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'date_col' at row 1
    mysql> insert into t_zerodate (date_col) values ('2010-00-00');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> insert into t_zerodate (date_col) values ('2010-00-01');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into t_zerodate (date_col) values ('2010-01-00');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert ignore into t_zerodate (date_col) values ('0000-00-00');
    Query OK, 1 row affected, 1 warning (0.01 sec)
    
    mysql> select date_col from t_zerodate;
    +------------+
    | date_col   |
    +------------+
    | 2010-00-00 |
    | 2010-00-01 |
    | 2010-01-00 |
    | 0000-00-00 |
    +------------+
    
    # 结论:
    '0000-00-00'插入报错,其余月、日为零的日期可以正常插入;
    对于INSERT IGNORE则允许插入'0000-00-00',但会产生告警。
    
    4.严格模式下 NO_ZERO_DATE和NO_ZERO_IN_DATE都不启用
    
    mysql> set session sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    mysql> truncate table t_zerodate;
    Query OK, 0 rows affected (0.09 sec)
    
    mysql> insert into t_zerodate (date_col) values ('0000-00-00');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> insert into t_zerodate (date_col) values ('2010-00-00');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> insert into t_zerodate (date_col) values ('2010-00-01');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> insert into t_zerodate (date_col) values ('2010-01-00');
    Query OK, 1 row affected (0.01 sec)
    
    # 结论:
    '0000-00-00'和其余月、日为零的日期都可以正常插入。

    3.结论及建议

    简单总结下,NO_ZERO_DATE模式影响'0000-00-00'日期的插入,NO_ZERO_IN_DATE模式影响除'0000-00-00'外的月、日为零的日期的插入。另外无论何种模式,YEAR类型都允许0000插入,这两个变量影响的是DATE、DATETIME、TIMESTAMP三种字段类型中对日期部分为零的处理。

    至于我们是否要启用这两种模式,这取决于业务需求。如果你的业务有插入零值日期的需求,则可以选择sql_mode中不要包含NO_ZERO_DATE和NO_ZERO_IN_DATE,例如,某字段要求设置为DATE类型且不为空,默认值设为'0000-00-00'。一般情况下,NO_ZERO_DATE和NO_ZERO_IN_DATE建议同时有或者同时没有,有插入零日期值的需求则可以去除二者,没有此类需要则可以保留二者。这里提醒下,官方文档中讲到,这两个变量在未来版本中不再作为独立变量使用,故官方不推荐使用。

    总结:

    写了这么多,不知道你认真看了多少,其实本篇文章讲的东西还是比较简单的。如果你遇到过此类问题,再看下本篇文章可能理解会更深刻些,没遇过此类问题的小伙伴,希望这篇文章可以让你知道MySQL对于零值日期有不同的处理。

    展开全文
  • 前言:前面文章我们介绍过日期和时间字段的查询方法,最近遇到日期值为零的问题。...显然,这是不合法的日期值,但由于设计问题或历史遗留问题,有时候数据库中有类似日期值为零的数据,默认情况下插入...
    前言: 

    前面文章我们介绍过日期和时间字段的查询方法,最近遇到日期值为零的问题。原来了解过和 sql_mode 参数设置有关,但还不是特别清楚,本篇文章将探究下MySQL怎么处理日期值为零的问题。

      1.问题描述

    这里我们说的日期为零值是指年、月、日为零,即'0000-00-00'。显然,这是不合法的日期值,但由于设计问题或历史遗留问题,有时候数据库中有类似日期值为零的数据,默认情况下插入零值日期会报错,可以通过修改参数sql_mode模式来避免该问题。下面展示下默认情况下插入零值的情况:

    # 首先创建测试表CREATE TABLE `t_zerodate` (  `increment_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',  `year_col` year DEFAULT NULL COMMENT '年',  `date_col` date DEFAULT NULL COMMENT '日期',  `dt_col` datetime DEFAULT NULL COMMENT 'datetime时间',  `ts_col` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'timestamp时间',  PRIMARY KEY (`increment_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='zerodate';# 查看sql_mode模式mysql> select @@sql_mode;+----------------------------------------------------------------------------------------------------+| @@sql_mode                                                                                         |+----------------------------------------------------------------------------------------------------+| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |+----------------------------------------------------------------------------------------------------+# 分别插入年、月、日为零值测试# 年份为0000 插入成功mysql> insert into t_zerodate (year_col) values (0000);Query OK, 1 row affected (0.02 sec)# 月、日都不为零时 可插入成功mysql> insert into t_zerodate (date_col) values ('0000-00-00');ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'date_col' at row 1mysql> insert into t_zerodate (date_col) values ('0000-01-00'); ERROR 1292 (22007): Incorrect date value: '0000-01-00' for column 'date_col' at row 1mysql> insert into t_zerodate (date_col) values ('0000-00-01');ERROR 1292 (22007): Incorrect date value: '0000-00-01' for column 'date_col' at row 1mysql> insert into t_zerodate (date_col) values ('0000-01-01'); Query OK, 1 row affected (0.02 sec)

      2.sql_mode变更测试

    关于sql_mode,原来写过一篇文章,sql_mode支持多个变量的不同组合,不同的sql_mode影响服务端支持的SQL语法以及数据校验规则。其中 NO_ZERO_IN_DATENO_ZERO_DATE 这两个变量影响MySQL对日期零值的处理。上面测试中可以发现,严格模式下,当sql_mode中包含NO_ZERO_IN_DATE,NO_ZERO_DATE两个变量时,月和日都不为零时可以插入成功。

    乍一看,NO_ZERO_IN_DATE和NO_ZERO_DATE两个变量很相似,但作用有什么不同呢?下面我们给出这两个变量的作用并做下具体测试。

    NO_ZERO_DATE 模式影响服务端是否允许将 '0000-00-00' 作为有效日期。其效果还取决于sql_mode是否启用了严格模式。

    • 如果未启用此模式,'0000-00-00'则允许插入并且不会产生警告。

    • 如果只启用此模式,'0000-00-00'则允许插入但是会产生警告。

    • 如果启用了此模式和严格模式,'0000-00-00'则会被认定为非法,并且插入也会产生错误。除非同时带有IGNORE,对于 INSERT IGNORE和UPDATE IGNORE,'0000-00-00'则允许插入但是会产生警告。

    NO_ZERO_IN_DATE 模式影响服务端是否允许插入年份部分非零但月或日部分为0的日期。(例如'2010-00-01'或 '2010-01-00',但不影响日期'0000-00-00'),其效果同样还取决于sql_mode是否启用了严格模式。

    • 如果未启用此模式,则允许部分为零的日期插入,并且不会产生任何警告。

    • 如果只启用此模式,则将该零值日期插入为'0000-00-00'并产生警告。

    • 如果启用了此模式和严格模式,则除非IGNORE同时指定,否则不允许插入为零的日期。对于INSERT IGNORE和 UPDATE IGNORE,将该零值日期插入为'0000-00-00'并产生警告。

    同时,官方文档中指出:NO_ZERO_DATE和NO_ZERO_IN_DATE虽然不是严格模式的一部分,但应与严格模式结合使用,如果在未启用严格模式的情况下启用了NO_ZERO_DATE或NO_ZERO_IN_DATE则会产生警告 ,反之亦然,(sql_mode中包含STRICT_TRANS_TABLES,一般可认为启用了严格模式)。

    下面我们来测试下,严格模式下分别启用和不启用这两个变量的效果:

    1.严格模式下 同时启用NO_ZERO_DATE、NO_ZERO_IN_DATE# 上文已经测试过,正常情况下 月、日都不为零时 可插入成功# 下面测试insert ignore intomysql> set session sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";Query OK, 0 rows affected (0.00 sec)mysql> truncate table t_zerodate;Query OK, 0 rows affected (0.07 sec)mysql> insert ignore into t_zerodate (date_col) values ('0000-00-00');Query OK, 1 row affected, 1 warning (0.01 sec)mysql> insert ignore into t_zerodate (date_col) values ('2010-00-00');    Query OK, 1 row affected, 1 warning (0.01 sec)mysql> insert ignore into t_zerodate (date_col) values ('2010-01-00'); Query OK, 1 row affected, 1 warning (0.01 sec)mysql> insert ignore into t_zerodate (date_col) values ('2010-00-01');Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select date_col from t_zerodate;+------------+| date_col   |+------------+| 0000-00-00 || 0000-00-00 || 0000-00-00 || 0000-00-00 |+------------+# 结论:正常情况下 月、日都不为零时 可插入成功;对于INSERT IGNORE则允许插入,但会产生告警,并会将零值日期保存为'0000-00-00'2.严格模式下 不启用NO_ZERO_DATEmysql> set session sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> truncate table t_zerodate;Query OK, 0 rows affected (0.08 sec)mysql> insert into t_zerodate (date_col) values ('0000-00-00');Query OK, 1 row affected (0.01 sec)mysql> insert into t_zerodate (date_col) values ('2010-00-01');ERROR 1292 (22007): Incorrect date value: '2010-00-01' for column 'date_col' at row 1mysql> insert into t_zerodate (date_col) values ('2010-01-00'); ERROR 1292 (22007): Incorrect date value: '2010-01-00' for column 'date_col' at row 1mysql> insert into t_zerodate (date_col) values ('0000-00-01');ERROR 1292 (22007): Incorrect date value: '0000-00-01' for column 'date_col' at row 1mysql> insert into t_zerodate (date_col) values ('0000-01-00');ERROR 1292 (22007): Incorrect date value: '0000-01-00' for column 'date_col' at row 1# 结论:'0000-00-00'可以正常插入,其余月、日为零的日期插入会报错;对于INSERT IGNORE则允许插入,但会产生告警,并会将零值日期保存为'0000-00-00'3.严格模式下 不启用NO_ZERO_IN_DATEmysql> set session sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> truncate table t_zerodate;Query OK, 0 rows affected (0.07 sec)mysql> insert into t_zerodate (date_col) values ('0000-00-00');ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'date_col' at row 1mysql> insert into t_zerodate (date_col) values ('2010-00-00');Query OK, 1 row affected (0.01 sec)mysql> insert into t_zerodate (date_col) values ('2010-00-01');Query OK, 1 row affected (0.00 sec)mysql> insert into t_zerodate (date_col) values ('2010-01-00');Query OK, 1 row affected (0.00 sec)mysql> insert ignore into t_zerodate (date_col) values ('0000-00-00');Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select date_col from t_zerodate;+------------+| date_col   |+------------+| 2010-00-00 || 2010-00-01 || 2010-01-00 || 0000-00-00 |+------------+# 结论:'0000-00-00'插入报错,其余月、日为零的日期可以正常插入;对于INSERT IGNORE则允许插入'0000-00-00',但会产生告警。4.严格模式下 NO_ZERO_DATE和NO_ZERO_IN_DATE都不启用mysql> set session sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> truncate table t_zerodate;Query OK, 0 rows affected (0.09 sec)mysql> insert into t_zerodate (date_col) values ('0000-00-00');Query OK, 1 row affected (0.01 sec)mysql> insert into t_zerodate (date_col) values ('2010-00-00');Query OK, 1 row affected (0.01 sec)mysql> insert into t_zerodate (date_col) values ('2010-00-01');Query OK, 1 row affected (0.01 sec)mysql> insert into t_zerodate (date_col) values ('2010-01-00');Query OK, 1 row affected (0.01 sec)# 结论:'0000-00-00'和其余月、日为零的日期都可以正常插入。

      3.结论及建议

    简单总结下,NO_ZERO_DATE模式影响'0000-00-00'日期的插入,NO_ZERO_IN_DATE模式影响除'0000-00-00'外的月、日为零的日期的插入。另外无论何种模式,YEAR类型都允许0000插入,这两个变量影响的是DATE、DATETIME、TIMESTAMP三种字段类型中对日期部分为零的处理。

    至于我们是否要启用这两种模式,这取决于业务需求。如果你的业务有插入零值日期的需求,则可以选择sql_mode中不要包含NO_ZERO_DATE和NO_ZERO_IN_DATE,例如,某字段要求设置为DATE类型且不为空,默认值设为'0000-00-00'。一般情况下,NO_ZERO_DATE和NO_ZERO_IN_DATE建议同时有或者同时没有,有插入零日期值的需求则可以去除二者,没有此类需要则可以保留二者。这里提醒下,官方文档中讲到,这两个变量在未来版本中不再作为独立变量使用,故官方不推荐使用。

    总结: 

    写了这么多,不知道你认真看了多少,其实本篇文章讲的东西还是比较简单的。如果你遇到过此类问题,再看下本篇文章可能理解会更深刻些,没遇过此类问题的小伙伴,希望这篇文章可以让你知道MySQL对于零值日期有不同的处理。

    PS:最近整理了一些MySQL相关资料,有本公众号文章原文、有相关PDF文档、还有业界大咖分享的资料!需要的小伙伴可以在公众号对话窗口回复 666 获取。近期微信公众号更改推送机制,推文不再按照时间线显示,如果你没有第一时间收到推送的话不妨加个“星标”试试看。

    推荐阅读

    (点击标题可跳转阅读)

    关于日期及时间字段的查询

    MySQL分区表最佳实践

    SQLmode最佳实践

    — END —

    如果你喜欢我的文章

    请在文末右下角点一下在看

    2d10468965a2970301dcc9f9a8916fad.png

    展开全文
  • 前言:前面文章我们介绍过日期和时间字段的查询方法,最近遇到日期值为零的...显然,这是不合法的日期值,但由于设计问题或历史遗留问题,有时候数据库中有类似日期值为零的数据,默认情况下插入零值日期会报错,可...

    前言:

    前面文章我们介绍过日期和时间字段的查询方法,最近遇到日期值为零的问题。原来了解过和 sql_mode 参数设置有关,但还不是特别清楚,本篇文章将探究下MySQL怎么处理日期值为零的问题。

    1.问题描述

    这里我们说的日期为零值是指年、月、日为零,即'0000-00-00'。显然,这是不合法的日期值,但由于设计问题或历史遗留问题,有时候数据库中有类似日期值为零的数据,默认情况下插入零值日期会报错,可以通过修改参数sql_mode模式来避免该问题。下面展示下默认情况下插入零值的情况:

    # 首先创建测试表

    CREATE TABLE `t_zerodate` (

    `increment_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',

    `year_col` year DEFAULT NULL COMMENT '年',

    `date_col` date DEFAULT NULL COMMENT '日期',

    `dt_col` datetime DEFAULT NULL COMMENT 'datetime时间',

    `ts_col` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'timestamp时间',

    PRIMARY KEY (`increment_id`)

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='zerodate';

    # 查看sql_mode模式

    mysql> select @@sql_mode;

    +----------------------------------------------------------------------------------------------------+

    | @@sql_mode |

    +----------------------------------------------------------------------------------------------------+

    | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |

    +----------------------------------------------------------------------------------------------------+

    # 分别插入年、月、日为零值测试

    # 年份为0000 插入成功

    mysql> insert into t_zerodate (year_col) values (0000);

    Query OK, 1 row affected (0.02 sec)

    # 月、日都不为零时 可插入成功

    mysql> insert into t_zerodate (date_col) values ('0000-00-00');

    ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'date_col' at row 1

    mysql> insert into t_zerodate (date_col) values ('0000-01-00');

    ERROR 1292 (22007): Incorrect date value: '0000-01-00' for column 'date_col' at row 1

    mysql> insert into t_zerodate (date_col) values ('0000-00-01');

    ERROR 1292 (22007): Incorrect date value: '0000-00-01' for column 'date_col' at row 1

    mysql> insert into t_zerodate (date_col) values ('0000-01-01');

    Query OK, 1 row affected (0.02 sec)

    2.sql_mode变更测试

    关于sql_mode,原来写过一篇文章,sql_mode支持多个变量的不同组合,不同的sql_mode影响服务端支持的SQL语法以及数据校验规则。其中 NO_ZERO_IN_DATE、NO_ZERO_DATE这两个变量影响MySQL对日期零值的处理。上面测试中可以发现,严格模式下,当sql_mode中包含NO_ZERO_IN_DATE,NO_ZERO_DATE两个变量时,月和日都不为零时可以插入成功。

    乍一看,NO_ZERO_IN_DATE和NO_ZERO_DATE两个变量很相似,但作用有什么不同呢?下面我们给出这两个变量的作用并做下具体测试。

    NO_ZERO_DATE模式影响服务端是否允许将 '0000-00-00' 作为有效日期。其效果还取决于sql_mode是否启用了严格模式。

    如果未启用此模式,'0000-00-00'则允许插入并且不会产生警告。

    如果只启用此模式,'0000-00-00'则允许插入但是会产生警告。

    如果启用了此模式和严格模式,'0000-00-00'则会被认定为非法,并且插入也会产生错误。除非同时带有IGNORE,对于 INSERT IGNORE和UPDATE IGNORE,'0000-00-00'则允许插入但是会产生警告。

    NO_ZERO_IN_DATE模式影响服务端是否允许插入年份部分非零但月或日部分为0的日期。(例如'2010-00-01'或 '2010-01-00',但不影响日期'0000-00-00'),其效果同样还取决于sql_mode是否启用了严格模式。

    如果未启用此模式,则允许部分为零的日期插入,并且不会产生任何警告。

    如果只启用此模式,则将该零值日期插入为'0000-00-00'并产生警告。

    如果启用了此模式和严格模式,则除非IGNORE同时指定,否则不允许插入为零的日期。对于INSERT IGNORE和 UPDATE IGNORE,将该零值日期插入为'0000-00-00'并产生警告。

    同时,官方文档中指出:NO_ZERO_DATE和NO_ZERO_IN_DATE虽然不是严格模式的一部分,但应与严格模式结合使用,如果在未启用严格模式的情况下启用了NO_ZERO_DATE或NO_ZERO_IN_DATE则会产生警告,反之亦然,(sql_mode中包含STRICT_TRANS_TABLES,一般可认为启用了严格模式)。

    下面我们来测试下,严格模式下分别启用和不启用这两个变量的效果:

    1.严格模式下 同时启用NO_ZERO_DATE、NO_ZERO_IN_DATE

    # 上文已经测试过,正常情况下 月、日都不为零时 可插入成功

    # 下面测试insert ignore into

    mysql> set session sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";

    Query OK, 0 rows affected (0.00 sec)

    mysql> truncate table t_zerodate;

    Query OK, 0 rows affected (0.07 sec)

    mysql> insert ignore into t_zerodate (date_col) values ('0000-00-00');

    Query OK, 1 row affected, 1 warning (0.01 sec)

    mysql> insert ignore into t_zerodate (date_col) values ('2010-00-00');

    Query OK, 1 row affected, 1 warning (0.01 sec)

    mysql> insert ignore into t_zerodate (date_col) values ('2010-01-00');

    Query OK, 1 row affected, 1 warning (0.01 sec)

    mysql> insert ignore into t_zerodate (date_col) values ('2010-00-01');

    Query OK, 1 row affected, 1 warning (0.01 sec)

    mysql> select date_col from t_zerodate;

    +------------+

    | date_col |

    +------------+

    | 0000-00-00 |

    | 0000-00-00 |

    | 0000-00-00 |

    | 0000-00-00 |

    +------------+

    # 结论:

    正常情况下 月、日都不为零时 可插入成功;

    对于INSERT IGNORE则允许插入,但会产生告警,并会将零值日期保存为'0000-00-00'。

    2.严格模式下 不启用NO_ZERO_DATE

    mysql> set session sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";

    Query OK, 0 rows affected, 1 warning (0.00 sec)

    mysql> truncate table t_zerodate;

    Query OK, 0 rows affected (0.08 sec)

    mysql> insert into t_zerodate (date_col) values ('0000-00-00');

    Query OK, 1 row affected (0.01 sec)

    mysql> insert into t_zerodate (date_col) values ('2010-00-01');

    ERROR 1292 (22007): Incorrect date value: '2010-00-01' for column 'date_col' at row 1

    mysql> insert into t_zerodate (date_col) values ('2010-01-00');

    ERROR 1292 (22007): Incorrect date value: '2010-01-00' for column 'date_col' at row 1

    mysql> insert into t_zerodate (date_col) values ('0000-00-01');

    ERROR 1292 (22007): Incorrect date value: '0000-00-01' for column 'date_col' at row 1

    mysql> insert into t_zerodate (date_col) values ('0000-01-00');

    ERROR 1292 (22007): Incorrect date value: '0000-01-00' for column 'date_col' at row 1

    # 结论:

    '0000-00-00'可以正常插入,其余月、日为零的日期插入会报错;

    对于INSERT IGNORE则允许插入,但会产生告警,并会将零值日期保存为'0000-00-00'。

    3.严格模式下 不启用NO_ZERO_IN_DATE

    mysql> set session sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";

    Query OK, 0 rows affected, 1 warning (0.00 sec)

    mysql> truncate table t_zerodate;

    Query OK, 0 rows affected (0.07 sec)

    mysql> insert into t_zerodate (date_col) values ('0000-00-00');

    ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'date_col' at row 1

    mysql> insert into t_zerodate (date_col) values ('2010-00-00');

    Query OK, 1 row affected (0.01 sec)

    mysql> insert into t_zerodate (date_col) values ('2010-00-01');

    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_zerodate (date_col) values ('2010-01-00');

    Query OK, 1 row affected (0.00 sec)

    mysql> insert ignore into t_zerodate (date_col) values ('0000-00-00');

    Query OK, 1 row affected, 1 warning (0.01 sec)

    mysql> select date_col from t_zerodate;

    +------------+

    | date_col |

    +------------+

    | 2010-00-00 |

    | 2010-00-01 |

    | 2010-01-00 |

    | 0000-00-00 |

    +------------+

    # 结论:

    '0000-00-00'插入报错,其余月、日为零的日期可以正常插入;

    对于INSERT IGNORE则允许插入'0000-00-00',但会产生告警。

    4.严格模式下 NO_ZERO_DATE和NO_ZERO_IN_DATE都不启用

    mysql> set session sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";

    Query OK, 0 rows affected, 1 warning (0.00 sec)

    mysql> truncate table t_zerodate;

    Query OK, 0 rows affected (0.09 sec)

    mysql> insert into t_zerodate (date_col) values ('0000-00-00');

    Query OK, 1 row affected (0.01 sec)

    mysql> insert into t_zerodate (date_col) values ('2010-00-00');

    Query OK, 1 row affected (0.01 sec)

    mysql> insert into t_zerodate (date_col) values ('2010-00-01');

    Query OK, 1 row affected (0.01 sec)

    mysql> insert into t_zerodate (date_col) values ('2010-01-00');

    Query OK, 1 row affected (0.01 sec)

    # 结论:

    '0000-00-00'和其余月、日为零的日期都可以正常插入。

    3.结论及建议

    简单总结下,NO_ZERO_DATE模式影响'0000-00-00'日期的插入,NO_ZERO_IN_DATE模式影响除'0000-00-00'外的月、日为零的日期的插入。另外无论何种模式,YEAR类型都允许0000插入,这两个变量影响的是DATE、DATETIME、TIMESTAMP三种字段类型中对日期部分为零的处理。

    至于我们是否要启用这两种模式,这取决于业务需求。如果你的业务有插入零值日期的需求,则可以选择sql_mode中不要包含NO_ZERO_DATE和NO_ZERO_IN_DATE,例如,某字段要求设置为DATE类型且不为空,默认值设为'0000-00-00'。一般情况下,NO_ZERO_DATE和NO_ZERO_IN_DATE建议同时有或者同时没有,有插入零日期值的需求则可以去除二者,没有此类需要则可以保留二者。这里提醒下,官方文档中讲到,这两个变量在未来版本中不再作为独立变量使用,故官方不推荐使用。

    总结:

    写了这么多,不知道你认真看了多少,其实本篇文章讲的东西还是比较简单的。如果你遇到过此类问题,再看下本篇文章可能理解会更深刻些,没遇过此类问题的小伙伴,希望这篇文章可以让你知道MySQL对于零值日期有不同的处理。

    2f65deb287714bd3fd3c7157dfd951ef.png

    展开全文
  • 前言:前面文章我们介绍过日期和时间字段的查询方法,最近遇到日期值为零的...显然,这是不合法的日期值,但由于设计问题或历史遗留问题,有时候数据库中有类似日期值为零的数据,默认情况下插入零值日期会报错,可...
  • 前言:前面文章我们介绍过日期和时间字段的查询方法,最近遇到日期值为零的...显然,这是不合法的日期值,但由于设计问题或历史遗留问题,有时候数据库中有类似日期值为零的数据,默认情况下插入零值日期会报错,可...
  • 前言:前面文章我们介绍过日期和时间字段的查询方法,最近遇到日期值为零的...显然,这是不合法的日期值,但由于设计问题或历史遗留问题,有时候数据库中有类似日期值为零的数据,默认情况下插入零值日期会报错,可...
  • 前言:前面文章我们介绍过日期和时间字段的查询方法,最近遇到日期值为零的...显然,这是不合法的日期值,但由于设计问题或历史遗留问题,有时候数据库中有类似日期值为零的数据,默认情况下插入零值日期会报错,可...
  • 有个历史遗留的app通过开一个线程,不断调用show方法进行实现,这些年也没出过问题,直到系统版本更新到了Android9.0。 实现方式大概如下: mToast = new Toast(context); mToast.setDuration(Toast.LENGTH_LONG); ...
  • 有个历史遗留的app通过开一个线程,不断调用show方法进行实现,这些年也没出过问题,直到系统版本更新到了Android9.0。实现方式大概如下: mToast = new Toast(context); mToast.setDuration(Toast.LENGTH_LONG); ...
  • 有个历史遗留的app通过开一个线程,不断调用show方法进行实现,这些年也没出过问题,直到系统版本更新到了Android9.0。实现方式大概如下:mToast = new Toast(context); mToast.setDuration(Toast.LENGTH_LONG); m.....
  • 有个历史遗留的app通过开一个线程,不断调用show方法进行实现,这些年也没出过问题,直到系统版本更新到了Android9.0。实现方式大概如下:mToast = new Toast(context); mToast.setDuration(Toast.LENGTH_LONG); m.....
  • 法律顾问述职报告优质范文合集 作为法律顾问,... 第二、三两个月 着重处理历史遗留问题 经深入了解公司情况,提出改进公司内部管理、控制的建议;对历史遗留问题加以梳理,重点解决突出的法律问题;保证公司的运营...
  • 前言:前面文章我们介绍过日期和时间字段的查询方法,最近遇到日期值为零的...显然,这是不合法的日期值,但由于设计问题或历史遗留问题,有时候数据库中有类似日期值为零的数据,默认情况下插入零值日期会报错,可...
  • 【运筹帷幄】一般来说,需要架构重构的系统,基本上都是因为各种历史原因和历史问题没有及时处理遗留下来逐渐积累,然后到了一个临界点,各种问题开始互相作用,集中爆发!到了真正要开始重构的时候,我们可能会...
  • 一般来说,需要架构重构的系统,基本上都是因为各种历史原因和历史问题没有及时处理遗留下来逐渐积累,然后到了一个临界点,各种问题开始互相作用,集中爆发!到了真正要开始重构的时候,我们可能会发现千头万绪,...
  • 与Quartz的半日情结

    2010-11-17 21:21:05
    最近一直在处理报表订阅的一些历史遗留问题,编码人离职了,理所当然就得是我这个专门负责给人"擦PP"的人去解决了.虽然解决了大部分问题,但今天好悬就把偶搞傻掉了. 先说下Quartz这个玩意吧,说得挺悬乎,...
  • 我的项目里由于历史原因(除了这还能有什么原因),采用的是gb2312编码,因此在Object中添加了gbk和utf8两个方法以进行编码转换。...当然完美的方案是只在model上处理编码问题,但是考虑到遗留代码,这是个不错的方案。
  • 1 逻辑类问题(A类)-指设计、编码中出现的计算正确性和一致性、程序逻辑控制等方面出现的问题,在系统中起关键作用,将导致软件死机、功能正常实现等严重问题; 接口类问题(B类)-指设计、编码中出现的函数和...
  • 12.5.2 处理遗留的LONG类型 517 12.6 DATE、TIMESTAMP和INTERVAL类型 523 12.6.1 格式 523 12.6.2 DATE类型 525 12.6.3 TIMESTAMP类型 533 12.6.4 INTERVAL类型 541 12.7 LOB 类型 544 12.7.1 内部LOB 545 ...
  • 12.5.2 处理遗留的LONG类型 464 12.6 DATE、TIMESTAMP和INTERVAL类型 469 12.6.1 格式 469 12.6.2 DATE类型 470 12.6.3 TIMESTAMP类型 475 12.6.4 INTERCAL类型 482 12.7 LOB类型 484 12.7.1 内部LOB -1 ...
  • 前言:前面文章我们介绍过日期和时间字段的查询方法,最近遇到日期值为零的...显然,这是不合法的日期值,但由于设计问题或历史遗留问题,有时候数据库中有类似日期值为零的数据,默认情况下插入零值日期会报错,可...

空空如也

空空如也

1 2
收藏数 25
精华内容 10
关键字:

怎么处理历史遗留问题