2021-06-05 10:43  阅读(7894)
文章分类:MySQL 拾遗 文章标签:MySQLMySQL 拾遗
©  原文作者:简单教程 原文地址:https://www.twle.cn

在我十年的 IT 生涯中,好像几乎没有使用过 MySQL 的 datedatetimetimestamp 几种数据类型。

究其原因,有两个:一是傻傻的分不清 DATEDATETIMETIMESTAMP 三种数据类型,记不住它们的格式;二是,它们或多或少和时区相关,为了存储时区无关数据,我一般都使用 int(11) 存储时间戳格式

今天就称这个机会,我们一起来熟悉下这三种数据类型

DATE、DATETIME 和 TIMESTAMP 的格式

首先要说明的是,这三种数据类型是息息相关的,它们有很多相似之处,也有各自的特征,因为这些特征,才导致了彼此的不同。

其次,MySQL 可以识别多种格式的 datedatetimetimestamp ,具体支持的格式有

  1. 对于 date 类型,支持 YYYY-MM-DDYY-MM-DD 以及它们各自的变形

说到变形,就是指中划线 ( - ) 可以替换为任意字符,除了数字 0-9 ,比如

格式 范例
YYYY-MM-DD 2018-09-13
YY-MM-DD 18-09-13
YYYY/MM/DD 2018/09/13
YY/MM/DD 18/09/13
YYYY^MM^DD 2018^09^13
YY^MM^DD 18^09^13
YYYY@MM@DD 2018@09@13
YY@MM@DD 18@09@13

同时还支持 YYYYMMDDYYMMDD 的字符串形式,例如 '20180913''180913'

还支持 YYYYMMMDDYYMMDD 的数字格式,例如 20180913180913

  1. 对于 datetimetimestamp 类型,支持 YYYY-MM-DD HH:MM:SSYY-MM-DD HH:MM:SS,一起它们的变形

这个变形其实和 DATE 一样,而且就是指中划线 ( - ) 可以替换为任意字符,除了数字 0-9

更进一步,这两个类型可以针对日期部分和时间部分使用不同的分隔符

格式 范例
YYYY-MM-DDHH:MM:SS 2018-09-1321:15:10
YY-MM-DDHH:MM:SS 18-09-1321:15:10
YYYYY^MM^DDHH+MM+SS 2018^09^1321+15+10
YY^MM^DDHH+MM+SS 18^09^1321+15+10
YYYY^MM^DD 2018^09^13
YY^MM^DD 18^09^13
YYYY@MM@DDHH^MM^SS 2018@09@1321^15^10
YY@MM@DDHH^MM^SS 18@09@1321^15^10

这种格式下也有几个点要注意

  1. 日期和时间部分与小数秒部分之间的唯一可用的分隔符号是小数点 ( . ) 例如 2018-09-13 21:15:10.11
  2. 日期与时间部分的分隔符不一定就要使用空格 ( ' ' ),还可以使用字符 T ,例如 2018-09-13 21:15:102018-09-13T21:15:10 是等价的

同时还可以使用没有任何分隔符的形式,比如 YYYYMMDDHHMMSSYYMMDDHHMMSS,例如 '20180913211510'2018-09-13 21:15:10 是等价的

071122129015 则是非法的,并不是因为年份缺少了,而是因为分钟太大 ( 90 ),然后会被视为 0000-00-00 00:00:00

因为 MySQL 同时支持超大整数类型,所以,数字形式的 YYYYMMDDHHMMSSYYMMDDHHMMSS 也是被支持的,例如 20180913211510180913211510 被认为与 2018-09-13 21:15:10 是等价的

DATE、DATETIME 和 TIMESTAMP 的特征

从上面的格式中可以看出,date 类型就是只有 年月日 没有 时分秒,MySQL 以 YYYY-MM-DD 格式检索并显示 date 类型的值。支持的范围是 1000-01-019999-12-31

datetime 类型通常包含完整的日期时间,也就是能够完整的表达某一个时刻。MySQL 以 YYYY-MM-DD HH:MM:SS 格式检索并显示 datetime 类型的值,支持的范围是 1000-01-01 00:00:009999-12-31 23:59:59

datetime 类型一样,timestamp 类型通常也包含完整的日期时间,也能够完整的表达某一个时刻。MySQL 也以 YYYY-MM-DD HH:MM:SS 格式检索并显示 timestamp 类型的值,但是,timestamp 支持的范围是不同的,只能是1970-01-01 00:00:00 UTC2038-12-31 23:59:59 UTC

也就是说,支持的格式和显示上,datetimetimestamp 并没有什么不同,但在支持的范围上是有不同的

DATETIME 和 TIMESTAMP 的小数部分

datetime 和 timestamp 两种时间类型还可以精确到 微秒 ,具体的形式就是支持在 后面使用 6 位精度的小数来支持,例如 2018-09-13 21:15:10.111111

而且,插入 datetime 或 timestamp 列的值中的任何小数部分都将被存储而不是被丢弃。

当包含小数部分时,datetime 和 timstamp 两个日期时间类型的格式为 YYYY-MM-DD HH:MM:SS [.fraction],datetime 值的范围为 1000-01-01 00:00:00.0000009999 -12-31 23:59:59.999999 而 timestamp 类型的范围则为 1970-01-01 00:00:01.0000002038-01-19 03:14:07.999999

注意: 小数部分与其它部分的唯一的分隔符只能是小数点 ( . ),且没有任何其它可替代的字符。

DATETIME 和 TIMESTAMP 支持自动赋值

MySQL 还为 datetime 和 timestamp 两种日期时间类型提供了自动赋值功能。也就是在 MySQL INSERT 时可以自动初始化为当前日期时间,在 MySQL Update 时自动更新为当前时间。

而具体的做法,就是创建表结构时为这两种日期时间类型添加以下约束:

    CREATE TABLE t1 (
     ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     dt DATETIME  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    );
    
  1. DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 指示在 insert 操作时自动插入当前日期时间
  2. DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 指示在 update 操作时自动更新为当前日期时间

DATETIME 和 TIMESTAMP 的时区问题

datetime 和 timestamp 两种类型的另一个区别,就是它们对待 时区 有所不同

  1. timestamp 会将值的时区从当前时区转换为 UTC 然后存储,并在需要显示和检索时从 UTC 转换回当前时区
  2. 但对于 datetime 类型,什么都不会发生,值是什么时区,存储的就是什么时区

默认情况下,timestamp 和 datetime 都会将当前连接的 MySQL 服务器的时区当作当前时区,当然了,这个时区是可配置的,而且可以针对每个连接单独配置。

从某些方面说,在数据转移或者在不同地方显示时,只要设置了一样的时区,那么数据就是一致的,否额

  1. datetime 的值虽然存储和显示的时候都是同一个值,但可能不是我们想要的,因为时区错了
  2. timestamp 虽然可以保证时间是正常的,但存储的值和显示的值可能会不一样,可能会导致我们错觉发生

PS 就是因为这个原因,导致了我不敢轻易使用 DATETIME 数据类型,连带 timestamp 也不敢使用

DATE、DATETIME 和 TIMESTAMP 的注意事项

  1. 对于这三种日期时间类型,无效的值将会转换为相应的 值,也就是,date 类型会转换为 0000-00-00datetime 类型会转换为 0000-00-00 00:00:00 ,而 timestamp 则会转换为 1970-01-01 00:00:00
  2. 虽然 MySQL 支持为指定为字符串的值使用 宽松 格式,其中任何标点字符都可以用作日期部分或时间部分之间的分隔符,但是,在某些情况下,这种语法可能是欺骗性的。

例如,10:11:12 之类的值可能看起来像时间值,因为它们的分隔符是 : ,但它也可以表示为一个日期 2010-11-12

还有,年月日时分秒中的任何一部分只要非法,那么整个值就会 归零,例如 date 类型的值如果为 10:45:15 那么就会被转换为 0000-00-00,因为 45 不是有效月份值

  1. 上面也提到了,就是日期和时间部分与小数秒部分之间识别的唯一分隔符是小数点
  2. MySQL 服务器会真实的判断一个日期是否有效,而不仅仅是判断月份和日期是否分别在 1 到 12 和 1 到 31 的范围内。

在严格模式下 ( sql_mode=TRADITIONAL ) 无效的月份和日期 ( 例如 '2004-04-31' )直接会导致一个错误发生,

即使在禁用严格模式后,'2004-04-31' 等无效日期也会被转换为 '0000-00-00' 且生成一个警告

  1. 对于 timestamp 类型,MySQL 并不支持月份和日期中的 0 值,也就是没有什么 00 月和 00 日,这些都不是有效的值,会直接导致整体的值被转换为 1970-01-01 00:00:00
  2. 对于 datetime 和 timestamp 类型,两位数的年是不确定的日期,因为不知道是属于哪个世纪。

对于两位数的年份,MySQL 使用下面这些规则解释

  1. 00-69 范围内的年份值将转换为 2000-2069
  2. 70-99 范围内的年份值转换为 1970-1999
点赞(2)
版权归原创作者所有,任何形式转载请联系作者; Java 技术驿站 >> 傻傻分不清的 DATE、DATETIME 和 TIMESTAMP ( 上 )
上一篇
被 MySQL sql_mode 深深伤害( 下 )
下一篇
傻傻分不清的 DATE、DATETIME 和 TIMESTAMP ( 中 )