MySQL时间类型和模式详情

目录
  • 1、MySQL时间类型
  • 2、查看时区
  • 3、非法时间值
  • 4、严格模式
  • 5、case汇总

当我在MySQL数据库中尝试插入一条带有时间戳的数据时报错:

mysql> insert into alarm_service values (6, '1970-01-01 08:00:00');
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 08:00:00' for column 'time' at row 1

# 查看表结构
mysql> show create table alarm_service;
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                                                                                         |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| alarm_service | CREATE TABLE `alarm_service` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

我们可以发现错误信息提示是时间值错误,但是我们这明显是一个合法的时间点啊。

经过查询资料,发现原因是在MySQL中,timestamp类型的合法区间是1970-01-01 00:00:01 - 2038-01-19 03:14:07 UTC,而在存储是,会先将你插入的数据转换为UTC时间,然后存储起来,读取的时候,再转换为你的本地时间。由于我的时区为东八区,因此转换后就变为了1970-01-01 00:00:00 UTC,成为了非法时间。

解决方案为:

  1. 调整时间为合法范围
  2. 调整MySQL严格模式,允许非法时间

下面我们详细说明相关的内容。

1、MySQL时间类型

MySQL时间类型分为三种:

  • DATE:用于只包含日期不包含时间的时候,MySQL会将格式转换为YYYY-MM-DD,合法范围为1000-01-01 - 9999-12-31
  • DATETIME:用于包含日期+时间的时候,格式为YYYY-MM-DD HH:MM:SS,合法范围为1000-01-01 00:00:00 - 9999-12-31 23:59:59
  • TIMESTAMP:用于包含日期+时间的时候,格式为YYYY-MM-DD HH:MM:SS,合法范围为1997-01-01 00:00:01 - 2038-01-19 03:14:07 UTC

同时,DATETIMETIMESTAMP还都支持一个6位微秒的数据支持,格式为YYYY-MM-DD HH:MM:SS[.fraction] ,合法范围为.000000 - .999999

DATETIMETIMESTAMP还都提供自动初始化并更新为当前日期和时间的数据。

对于TIMESTAMP类型,MySQL会在存储时将数据值转换为UTC标准时间来存储,读取时再转为当前时间。如果你的时区没有发生改变,则该值就是你存储的值,如果你改变了时区,读取到的值就会发生变化。这个特性不会对DATETIME生效。

2、查看时区

mysql> show variables like '%zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+

可以看到当前设置的时区是SYSTEM,即跟操作系统保持一致,同时系统的时区是CST(China Standard Time 北京标准时间),查看系统时间也可以看到是东8区(+0800):

$ date -R
Tue, 23 Apr 2019 11:22:47 +0800

因此我们输入1970-01-01 08:00:00MySQL会纠正为1970-01-01 00:00:00,而成为一个非法值。

3、非法时间值

对于非法的时间值,针对不同的时间类型,MySQL会将其转为合适的值:0000-00-00 0000-00-00 00:00:00

比如月份为1-12月,当你尝试插入2019-13-01 00:00:00时,就会被纠正为0000-00-00 00:00:00,因为不存在13月,为非法值。

4、严格模式

当我们插入非法时间值时,虽然会被纠正,但是在严格模式下,不会插入数据,反而会报错:

ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 08:00:00' for column 'time' at row 1

我们可以通过设置模式,来调整MySQL的行为,首先查看MySQL的模式:

mysql> show variables like '%sql_mode%';
+----------------------------+--------------------------------------------+
| Variable_name              | Value                                      |
+----------------------------+--------------------------------------------+                               |
| sql_mode                   | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+----------------------------+--------------------------------------------+

在这个模式下,非法时间会直接报错,我们可以调整模式为ALLOW_INVALID_DATES

mysql> set session sql_mode = 'ALLOW_INVALID_DATES';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%sql_mode%';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| sql_mode      | ALLOW_INVALID_DATES |
+---------------+---------------------+
1 row in set (0.00 sec)

在这个模式下,不会再完备检查日期的合法性,只会检查月份的范围在1-12,日期在1-31。这在处理用户输入的时候很合适,但是这个模式只对于DATEDATETIME很合适,对于TIMESTAMP,依然需要一个合法的值,否则就会纠正为0000-00-00 00:00:00。

在非法值时,如果这个模式启用,就会报错;如果禁用,就会纠正为0000-00-00 00:00:00并产生一个警告:

mysql> insert into alarm_service values (7, '1970-01-01 08:00:00');
Query OK, 1 row affected, 1 warning (0.00 sec)

总结:

对于这种问题,有两种解决方法:

  1. 调整时间为合法范围
  2. 调整MySQL严格模式,允许非法时间

5、case汇总

ERROR 1067 (42000): Invalid default value for 'createTime'

查看原因发现设置为:

# 查看创建表单的语句
CREATE TABLE `dimensionsConf` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `createTime` datetime DEFAULT CURRENT_TIMESTAMP,
) ENGINE=InnoDB AUTO_INCREMENT=178 DEFAULT CHARSET=utf8;

# 查看数据库版本
$mysql --version
mysql  Ver 14.14 Distrib 5.1.30, for unknown-linux-gnu (x86_64) using  EditLine wrapper

到此这篇关于MySQL时间类型和模式详情的文章就介绍到这了,更多相关MySQL时间类型和模式内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • 解析MySql与Java的时间类型

    MySql的时间类型有          Java中与之对应的时间类型date                                           java.sql.DateDatetime                                    java.sql.TimestampTimestamp                                  java.sql.TimestampTime                            

  • 关于mysql 的时间类型选择

    间类型:尽量使用TIMESTAMP类型,因为其存储空间只需要 DATETIME 类型的一半.对于只需要精确到某一天的数据类型,建议使用DATE类型,因为他的存储空间只需要3个字节,比TIMESTAMP还少.不建议通过INT类型类存储一个unix timestamp 的值,因为这太不直观,会给维护带来不必要的麻烦,同时还不会带来任何好处.

  • MySQL日期数据类型、时间类型使用总结

    MySQL 日期类型:日期格式.所占存储空间.日期范围 比较. 日期类型        存储空间       日期格式                 日期范围 ------------ ---------   --------------------- ----------------------------------------- datetime       8 bytes   YYYY-MM-DD HH:MM:SS   1000-01-01 00:00:00 ~ 9999-12-31

  • MySQL存储时间类型选择的问题讲解

    MySQL中存储时间通常会用datetime类型,但现在很多系统也用int存储unix时间戳,它们有什么区别?本人总结如下: int (1)4个字节存储,INT的长度是4个字节,存储空间上比datatime少,int索引存储空间也相对较小,排序和查询效率相对较高一点点 (2)可读性极差,无法直观的看到数据 TIMESTAMP (1)4个字节储存 (2)值以UTC格式保存 (3)时区转化 ,存储时对当前的时区进行转换,检索时再转换回当前的时区. (4)TIMESTAMP值不能早于1970或晚于20

  • MySQL 时间类型的选择

    MySQL 提供了 DATETIME 和 TIMESTAMP 两种非常相似的类型处理日期和时间,大部分情况下两种都是 OK 的,但是有些情况二者会互有优劣. DATETIME DATETIME 的时间跨度更大,可以从1001年到9999年,精度是秒.并且存储的格式是将日期和时间打包使用 YYYYMMDDhhmmss格式的整数存储,这个时间与时区无关,需要占用8个字节的存储空间.默认,MySQL 显示 的DATETIME是有序的,明确的格式,例如2021-06-02 18:35:23.这是 ANS

  • 如何选择合适的MySQL日期时间类型来存储你的时间

    构建数据库写程序避免不了使用日期和时间,对于数据库来说,有多种日期时间字段可供选择,如 timestamp 和 datetime 以及使用 int 来存储 unix timestamp. 不仅新手,包括一些有经验的程序员还是比较迷茫,究竟我该用哪种类型来存储日期时间呢? 那我们就一步一步来分析他们的特点,这样我们根据自己的需求选择合适的字段类型来存储 (优点和缺点是比较出来的  , 跟父母从小喜欢拿邻居小孩子跟自己比一样的) datetime 和 timestamp datetime 更像日历上

  • MySQL时间类型和模式详情

    目录 1.MySQL时间类型 2.查看时区 3.非法时间值 4.严格模式 5.case汇总 当我在MySQL数据库中尝试插入一条带有时间戳的数据时报错: mysql> insert into alarm_service values (6, '1970-01-01 08:00:00'); ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 08:00:00' for column 'time' at row 1 # 查看表结构 my

  • MySQL 时间类型用 datetime, timestamp 还是 integer 更好

    目录 问题 MySQL 支持的数据类型 Integer Datetime TIMESTAMP DATETIME Integer BETWEEN 查询 性能差异 问题 今天我们来探讨一个有意思的问题,先说场景: 这是一个做在线文档产品的业务,需要给用户展示文档的编辑记录,现在我们叫它[智能文档]. 智能文档会不定期给文档数据打一个快照,保存起来.用户可以在历史记录中查阅快照. 快照之间会展示具体的变更记录,比如“用户A 复制了一段文字”,“用户B删除了一个图片”. 快照本身是动态生成和回收的,即距

  • MySQL时间设置注意事项的深入总结

    时间真的存在吗?有观点认为,时间只是人类构想出来的一种概念,是用来衡量事物变化的标准.对于数据库来说时间伴随着数据并进.进入MySQL时间漩涡中看看. 1.时间类型的字段 MySQL时间类型字段: 下面的容易忽略的内容: TIMESTAMP保存数据方式: MySQL将TIMESTAMP值从当前时区转换为UTC进行存储,并从UTC返回到当前时区进行检索. (这不适用于其他类型,比如DATETIME.)默认情况下,每个连接的当前时区是服务器的时间.时区可以在每个连接的基础上设置.只要时区设置保持不变

  • MySQL中的日期时间类型与格式化方式

    目录 [1]MySQL中的日期时间类型 ① 详细解释 ② SQL语句实例 ③ timestamp字段 ④ 测试实例 [2]日期时间类型格式化 ① DATE_FORMAT( )函数 ② date_format( ) 转换格式 ③ str_to_date()函数 [1]MySQL中的日期时间类型 MySQL中常用的几种时间类型有:date.datetime.time.year.timestamp 数据类型 占用字节 最小值 最大值 零值表示 date 4 1000-01-01 9999-12-31

  • MySQL datetime类型与时间、日期格式字符串大小比较的方法

    目录 一.前提 1.MySQL版本信息: 2.表字段: 二.使用>.<比较 1.  日期格式比较: 2. 时间格式 三.between and 1. 日期格式 四.总结 一.前提 1.MySQL版本信息: MySQL版本:8.0.27 注意:其他版本(主要5.x版本未验证) 2.表字段: 定义列 CREATE TABLE IF NOT EXISTS `user` ( id INT NOT NULL PRIMARY KEY auto_increment COMMENT 'id', user_na

  • Mysql InnoDB 的内存结构详情

    目录 1 前言 2 InnoDB 存储引擎结构 2.1 InnoDB表存储引擎文件 2.2 InnoDB 预读机制 2.3 InnoDB 特性 2.3.1 插入缓存 2.3.2 二次写 (double write) 2.3.3 自适应hash索引 2.3.4 异步IO 2.3.5 刷新邻接页 3 sql 执行的逻辑 3.1 sql 执行 3.2 FreeList.LRU List 和 Flush List 1 前言 我们都熟悉mysql数据库服务架构,也清楚 sql 的执行顺序,mysql的数据

随机推荐