MySQL

📘 官方文档。

FSP (fractional seconds precision)，表示允许保存到几位小数的秒，默认是 0。在建表时指定，如下面的 TIME(3) 即指定了 3 位小数：

CREATE TABLE t1 (t TIME(3), dt DATETIME(6), ts TIMESTAMP(0));

MySQL 如何展示日期类型？

Literals

标准的字面量是：

DATE 'str'
TIME 'str'
TIMESTAMP 'str'

中间的空格可要可不要。

但是实际写 SQL 时很少需要用到：

mysql> CREATE TABLE ts (
    ->     id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     col TIMESTAMP NOT NULL
    -> ) AUTO_INCREMENT = 1;

mysql> INSERT INTO ts (col) VALUES ('2020-01-01 10:10:10'),
    ->     ('2020-01-02 10:10:10'), ('2020-01-03 10:10:10');

DATE, DATETIME, TIMESTAMP

TIMESTAMP 类型无法表示 '1970-01-01 00:00:00'，因为这个值对应 epoch 中的 0，被 MySQL 用来表示 '0000-00-00 00:00:00'（zero timestamp）。

当指定了 FSP 后，数值范围也会有小的变化，比如一个 DATETIME 列指定了 3 位小数，那么数据范围变成：'1000-01-01 00:00:00.000' to '9999-12-31 23:59:59.999'。

TIMESTAMP 类型有个特殊逻辑：

• 当 explicit_defaults_for_timestamp 变量关闭时，当有 INSERT / UPDATE 操作时，表的第一个 TIMESTAMP 列如果没有被设值、或者设了 NULL 但是列定义中不允许 NULL，那么 MySQL 会自动将该列置为当前的时间
• 当 explicit_defaults_for_timestamp 变量打开时，没有上述自动设置的行为。你仍然可以在列定义中使用 DEFAULT CURRENT_TIMESTAMP / ON UPDATE CURRENT_TIMESTAMP 来使其自动更新

当 MySQL 设置中没有关闭 ZERO_DATE 时，日期的值可以是 '0000-00-00' / '0000-00-00 00:00:00'。使用场景：

• 用来表示类似空字符串的语义
• 当日期不足够详情时，比如用户设置其生日、只设了年份和月份时，可以用 'YYYY-MM-00' 来表达

Timezone

MySQL 本质上不存储一个时间值的时区。

MySQL 在存储 TIMESTAMP 时，会按 time_zone 把日期转成 UTC 存储；读取时再按服务器时区转成 YYYY-MM-DD 来表示。DATETIME 则没有此逻辑；它不带时区信息，不管服务器时区怎样变化，读到的值都是一样的。

MySQL 8.0.19 开始支持在 literal 里面写 time zone offset，比如 '2020-01-01 10:10:10+05:30'。但实际上存储的时候并没有把时区信息存进去。读出来会根据 time_zone 系统变量的值来展示。

应用开发者不应该关心 time_zone 的设置。它增加了理解成本。既然时区信息不是 DATETIME 的一部分，那么也不要有一个所谓的 DB 默认时区存在；你读到的 DATETIME 的时区，理解成跟你存入时的时区一致就好。比如即使 time_zone 为 Asia/Shanghai，但我存入 DATETIME 时认为它的时区从逻辑上是 UTC，那么取出来时当 UTC 就可以了。此外，time_zone 影响的 TIMESTAMP 的取值范围，一般对应用构不成限制；有一些函数会参考 time_zone 取值（如 CURTIME()），也尽量不要使用它们。各种编程语言的 MySQL 库，在把读出的 DATETIME 转换成该语言的日期对象时（比如 Go 的 time.Time，Python 的 datetime.datetime），也不会根据 time_zone 的取值来设定其时区。

应用开发者如果的确想利用 time_zone，那么也应该是在其代码的每个 session 手动指定（比如 SET time_zone=Asia/Shanghai）；不应该依赖 MySQL server 的 time_zone 设置（比如目前 SG 机房的 MySQL 设置的是东八区，但换个地方的机房不一定是这个设定了）。但这种方式要求任何使用该 DB 的程序都要指定好 time_zone，容易出错。

最佳建议：采用 Django 的建议，用 DATETIME 存日期，存数据库前先转成 UTC（Django 在常规配置下会自动转）。取出时再转成本地时区展示。Django 的 DateTimeField，在 MySQL 时使用 datetime(6)、在 PG 时使用 timestamp [ (p) ] with time zone。Django 在 USE_TZ 为 True 时就采用的这种逻辑。

自动初始化及更新日期字段

MySQL 支持自动设置时间字段（支持 DATETIME 及 TIMESTAMP 类型），比如：

CREATE TABLE t1 (
  createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
  updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Django 的 ORM 也提供了类似的能力，但是它要兼容多数据库，应该不是通过这种语法来支持的。因此，最佳实践应该还是在应用代码中手动指定时间字段。