前言:
前面文章我们介绍过日期和时间字段的查询方法,最近遇到日期值为零的问题。原来了解过和 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