文章目录

    • 1. 数据类型简介
    • 2. 方便的处理
      • 2.1 默认值与自动更新
      • 2.2 毫秒及更高精度

1. 数据类型简介

MySQL中关于日期和时间有DATE、TIME、DATETIME、TIMESTAMP这几种数据类型:

  • 1️⃣DATE:储存日期,格式为YYYY-MM-DD,范围1000-01-019999-12-31
  • 2️⃣TIME:储存时间,格式为hhh:mm:ss,范围-838:59:59838:59:59
  • 3️⃣DATETIME:储存日期和时间,格式为YYYY-MM-DD hh:mm:ss,范围1000-01-01 00:00:009999-12-31 23:59:59
  • 4️⃣TIMESTAMP:储存时间戳,格式与DATETIME相同,范围1970-01-01 00:00:012038-01-19 03:14:07

赋值时通过上述格式的字符串赋值即可,还有一些如同NOW()之类的函数也可赋值,但本质可以理解为返回了格式化字符串后赋值的。

其中的TIME可以有缩写,但❌十分不建议❌这样做:

  • 1112将被转化为00:11:12
  • 11:12将被转化为11:12:00

类似地,DATE也可以简写,参考官方文档2-Digit Years in Dates

对于后两者(DATETIMETIMESTAMP)很类似,又有所不同:

1️⃣☀️首先能够轻松注意到TIMESTAMP的储存范围比DATETIME小很多,那简单一想:

正经人谁还用TIMESTAMP啊❓

从官方介绍中了解到,TIMESTAMP在存储时会存成UTC时间,在取出时转换为服务器的所在时区时间。因此能够提供时区自动变换的功能,不过看起来不是跨国大鳄也不太用得上这功能……

UTC:协调世界时,又称世界统一时间

2️⃣在未指定默认值时,TIMESTAMPDATETIME的默认值根据是否允许NULL值有区别:

// DATETIME默认允许NULL值,指定不允许NULL时默认值为0CREATE TABLE t1 (  dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP,         -- default NULL  dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0);// TIMESTAMP默认不允许NULL值,指定允许NULL时默认值为NULLCREATE TABLE t1 (  ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,     -- default 0  ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL);

建议:这种知识点不建议去记,在声明字段时显式指出☝️即可。

有兴趣可以参考官方文档The DATE, DATETIME, and TIMESTAMP Types

2. 方便的处理

2.1 默认值与自动更新

TIMESTAMPDATETIME类型字段,都可以将当前时间戳指定为默认值和/或自动更新值,只需要在创建表格时添加DEFAULT描述和ON UPDATE描述:

// ts和dt将在创建和修改时,被赋值为当前时间CREATE TABLE t1 (  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

官方文档提到CURRENT_TIMESTAMP有很多同义词,他们效果相同:

  • NOW()
  • CURRENT_TIMESTAMP()
  • LOCALTIME/LOCALTIME()
  • LOCALTIMESTAMP/LOCALTIMESTAMP()

2.2 毫秒及更高精度

可以在声明字段时添加小数点精度,只需要在TIMESTAMP后添加(n)即可,其中0 ≤ n ≤ 6

❗️❗️❗️但要注意:声明时各处的小数精度要一致,如:

CREATE TABLE t1 (  ts TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),--正确  dt DATETIME(4) DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(6) -- 错误);

有兴趣可以参考官方文档Fractional Seconds in Time Values


如有错误 ❌ ,欢迎指正 ☝️~

如有收获 ,点赞/评论/收藏⭐️/关注~