mysql no zero date_MySQL关于日期为零值的处理

2024-02-03 05:20
文章标签 mysql 处理 date 日期 database zero 零值

本文主要是介绍mysql no zero 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 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

这篇关于mysql no zero date_MySQL关于日期为零值的处理的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/673094。

相关文章

MySQL 中的 CAST 函数详解及常见用法

MySQL 中的 CAST 函数详解及常见用法

《MySQL中的CAST函数详解及常见用法》CAST函数是MySQL中用于数据类型转换的重要函数,它允许你将一个值从一种数据类型转换为另一种数据类型,本文给大家介绍MySQL中的CAST... 目录mysql 中的 CAST 函数详解一、基本语法二、支持的数据类型三、常见用法示例1. 字符串转数字2. 数字
阅读更多...
Mysql实现范围分区表(新增、删除、重组、查看)

Mysql实现范围分区表(新增、删除、重组、查看)

《Mysql实现范围分区表(新增、删除、重组、查看)》MySQL分区表的四种类型（范围、哈希、列表、键值）,主要介绍了范围分区的创建、查询、添加、删除及重组织操作,具有一定的参考价值,感兴趣的可以了解... 目录一、mysql分区表分类二、范围分区（Range Partitioning1、新建分区表：2、分
阅读更多...
MySQL 定时新增分区的实现示例

MySQL 定时新增分区的实现示例

《MySQL定时新增分区的实现示例》本文主要介绍了通过存储过程和定时任务实现MySQL分区的自动创建,解决大数据量下手动维护的繁琐问题,具有一定的参考价值,感兴趣的可以了解一下... mysql创建好分区之后，有时候会需要自动创建分区。比如，一些表数据量非常大，有些数据是热点数据，按照日期分区MululbU
阅读更多...
SQL Server配置管理器无法打开的四种解决方法

SQL Server配置管理器无法打开的四种解决方法

《SQLServer配置管理器无法打开的四种解决方法》本文总结了SQLServer配置管理器无法打开的四种解决方法,文中通过图文示例介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的... 目录方法一：桌面图标进入方法二：运行窗口进入检查版本号对照表php方法三：查找文件路径方法四：检查 S
阅读更多...
Spring Boot @RestControllerAdvice全局异常处理最佳实践

Spring Boot @RestControllerAdvice全局异常处理最佳实践

《SpringBoot@RestControllerAdvice全局异常处理最佳实践》本文详解SpringBoot中通过@RestControllerAdvice实现全局异常处理,强调代码复用、统... 目录前言一、为什么要使用全局异常处理？二、核心注解解析1. @RestControllerAdvice2
阅读更多...
MySQL 删除数据详解(最新整理)

MySQL 删除数据详解(最新整理)

《MySQL删除数据详解(最新整理)》：本文主要介绍MySQL删除数据的相关知识,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录一、前言二、mysql 中的三种删除方式1.DELETE语句✅ 基本语法： 示例：2.TRUNCATE语句✅ 基本语
阅读更多...
MySQL中查找重复值的实现

MySQL中查找重复值的实现

《MySQL中查找重复值的实现》查找重复值是一项常见需求,比如在数据清理、数据分析、数据质量检查等场景下,我们常常需要找出表中某列或多列的重复值,具有一定的参考价值,感兴趣的可以了解一下... 目录技术背景实现步骤方法一：使用GROUP BY和HAVING子句方法二：仅返回重复值方法三：返回完整记录方法四：
阅读更多...
从入门到精通MySQL联合查询

从入门到精通MySQL联合查询

《从入门到精通MySQL联合查询》：本文主要介绍从入门到精通MySQL联合查询,本文通过实例代码给大家介绍的非常详细,需要的朋友可以参考下... 目录摘要1. 多表联合查询时mysql内部原理2. 内连接3. 外连接4. 自连接5. 子查询6. 合并查询7. 插入查询结果摘要前面我们学习了数据库设计时要满
阅读更多...
MySQL查询JSON数组字段包含特定字符串的方法

MySQL查询JSON数组字段包含特定字符串的方法

《MySQL查询JSON数组字段包含特定字符串的方法》在MySQL数据库中,当某个字段存储的是JSON数组,需要查询数组中包含特定字符串的记录时传统的LIKE语句无法直接使用,下面小编就为大家介绍两种... 目录问题背景解决方案对比1. 精确匹配方案（推荐）2. 模糊匹配方案参数化查询示例使用场景建议性能优
阅读更多...
mysql表操作与查询功能详解

mysql表操作与查询功能详解

《mysql表操作与查询功能详解》本文系统讲解MySQL表操作与查询,涵盖创建、修改、复制表语法,基本查询结构及WHERE、GROUPBY等子句,本文结合实例代码给大家介绍的非常详细,感兴趣的朋友跟随... 目录01.表的操作1.1表操作概览1.2创建表1.3修改表1.4复制表02.基本查询操作2.1 SE
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2