MySQL 拾遗
首页 死磕 Java 优质系列 Java 技术总览 Java 面试宝典 关于我

傻傻分不清的 DATE、DATETIME 和 TIMESTAMP ( 中 )

转
 2023-03-21  阅读(3)
©版权
原文作者：简单教程 原文地址：https://www.twle.cn/c/yufei/mysqlfav/mysqlfav-basic-distinct.html

不使用 date 、datetime 和 timestamp 三个类型的另外一个非重要原因，是一直记不住如何设置 datetime 和 timestamp 的默认值。

一般情况下，我在创建表结构的时候都会添加 created_at 和 updated_at 作为记录创建时间和记录最近更新时间，这两个时间都很好理解，如果使用 datetime 或 timestamp 则可以设置一些约束在 insert 数据和 update 数据时自动填充其值。

但是，我一贯的用法，就是把它们定义为一个整数类型 ( int )，然后使用应用程序级别的时间戳填充，其实，这是很简单愚蠢的。

MySQL 可以帮我们做这些事情，前提就是使用 datetime 和 timestamp 两个类型。

timestamp 和 datetime 列可以自动初始化并更新为当前日期和时间 ( 即当前时间戳 ) 。

对于 MySQL 表中的任何 timestamp 和 datetime 列，我们可以将设置当前时间戳为默认值，自动更新值或两者皆有：

  1. 如果将某个列设置为插入时自动初始化为当前时间戳，那么就可以不用为这些列指定任何值。

  2. 当行中任何其他列的值发生变更时，设置了自动更新的列将自动更新为当前时间戳。

    当然了，这里有一个要点，如果 update 时任何其它列的数据并没有发生变更，那么自动更新列保持不变。

    如果要更新自动更新的列，即使其他列未更改，则需要显式设置值为应具有的值，例如使用 CURRENT_TIMESTAMP()

    如果要防止在其他列更改时更新自动更新列，则需要显式的设置自动更新列的值为当前值

除了以上这些规则，如果禁用 （ disabled ) 了 explicit_defaults_for_timestamp 系统变量，则可以通过为其指定 NULL 值来初始化或更新任何 timestamp（但不是 datetime ）列到当前日期和时间，除非已使用 NULL 属性定义允许 NULL 值

正如上一章节中所提到的那样，要指定自动属性，可以在创建表时在列定义中使用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 两个约束，约束的顺序无关紧要。

如果这两个约束同时存在于某个列定义中，那么任何一个都可能先执行。

CURRENT_TIMESTAMP 可以使用任何同义词代替，任何 CURRENT_TIMESTAMP 的任何同义词与 CURRENT_TIMESTAMP 具有相同的含义，例如 CURRENT_TIMESTAMP()、NOW()、LOCALTIME、LOCALTIME()、LOCALTIMESTAMP 和 LOCALTIMESTAMP() 。

当在 datetime 和 timestamp 列上使用了 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 约束时，其实还是可以添加 DEFAULT 约束的，default 约束用于指定常量 (非自动 ) 默认值;例如 DEFAULT 0 或 DEFAULT '2018-09-14 00:00:00'

下面的示例中可能会使用到 default 0 ，这是一个默认值，但可以产生警告或错误，具体取决于是否启用了严格的 SQL 模式或 NO_ZERO_DATE SQL 模式。

timestamp 和 datetime 列可以同时指定默认值和自动更新值为当前时间戳，也可以值指定其中一个，或者两个都不指定。不同的列可以具有不同的自动属性组合。下面的规则则描述了一些可能的使用场景

  1. 同时指定 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP，那么该列会自动设置当前时间为默认值，并自动更新为当前时间戳
        CREATE TABLE t1 (
          ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        );

  1. 使用了 default 约束，当没有使用 ON UPDATE CURRENT_TIMESTAMP 约束时，该列会自动使用给定的默认值，但不会自动更新为当前时间戳

    而默认的值取决于 default 子句是指定 CURRENT_TIMESTAMP 还是常量值。

    如果使用 CURRENT_TIMESTAMP，默认值是当前时间戳

        CREATE TABLE t1 (
          ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          dt DATETIME DEFAULT CURRENT_TIMESTAMP
        );

如果使用了常量值，默认值是则为给定值，在这种情况下，该列根本没有自动属性

        CREATE TABLE t1 (
          ts TIMESTAMP DEFAULT 0,
          dt DATETIME DEFAULT 0
        );
  1. 如果使用了 ON UPDATE CURRENT_TIMESTAMP 约束和常量 default 约束，该列将自动更新为当前时间戳并具有给定的常量默认值
        CREATE TABLE t1 (
          ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
          dt DATETIME DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
        );

  1. 如果使用了 ON UPDATE CURRENT_TIMESTAMP 约束当没有使用 default 约束，该列将自动更新为当前时间戳，且不会使用当前时间戳作为其默认值

    这种情况下的默认值取决于所使用的类型。

    timestamp 类型的默认值为 0，除非使用了 NULL 约束，那么默认值就是 NULL

        CREATE TABLE t1 (
          ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,     -- default 0
          ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
        );

datetime 类型的默认值为 NULL ，除非使用 NOT NULL 约束，那么默认值就是 0

        CREATE TABLE t1 (
          dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP,         -- default NULL
          dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0
        );

需要注意的是，datetime 和 timstamp 默认是么有任何自动属性的，如果需要自动属性需要显式的指明，但有一个例外情况：

「 如果禁用了系统变量 explicit_defaults_for_timestamp ，且没有任何一个 timestamp 类型的列明确的设置了自动属性，那么第一个 timestamp 类型的列会自动设置为 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 」

如果要禁止第一个 timestamp 列自动设置 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 属性，有以下几种方法

  • 启用 explicit_defaults_for_timestamp 系统变量。

    在这种情况下，可以为 timestamp 列设置自动初始化和自动更新的 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP，但需要明确指明，否则任何 timetamp 都不会获得这些自动属性

  • 或者，保持禁用 explicit_defaults_for_timestamp ，但通过以下方法来改变：

    • 使用 default 约束为 timestamp 指定一个默认的常量值。

    • 使用 null 约束。

      但因为使用了 NULL 约束，导致列允许 NULL 值，也就意味着无法通过将列设置为 NULL 来分配当前时间戳。

      使用 NULL 约束会将列设置为 NULL，而不是当前时间戳，如果要分配当前时间戳，则需要将列设置为 CURRENT_TIMESTAMP 或同义词，例如NOW（）

比如下面这个表结构定义

    CREATE TABLE t1 (
      ts1 TIMESTAMP DEFAULT 0,
      ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    ON UPDATE CURRENT_TIMESTAMP);
    CREATE TABLE t2 (
      ts1 TIMESTAMP NULL,
      ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    ON UPDATE CURRENT_TIMESTAMP);
    CREATE TABLE t3 (
      ts1 TIMESTAMP NULL DEFAULT 0,
      ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    ON UPDATE CURRENT_TIMESTAMP);

这些表 t1、t2、t3 具有以下特征

  • 每个表结构定义中，第一个 TIMESTAMP 列没有设置自动初始化或自动更新。

  • 这些表的不同之处在于 ts1 列如何处理 NULL 值

    • 对于 t1，ts1 为 NOT NULL，并为其赋值为 NULL，这会将其值设置为当前时间戳
    • 对于 t2 和 t3 ，ts1 允许 NULL，并为其赋值 NULL

  • t2 和 t3 在 ts1 的默认值上有所不同。

    • 对于 t2，ts1 被定义为允许 NULL，因此在没有显式 DEFAULT 子句的情况下，缺省值也为 NULL
    • 对于 t3，ts1 允许 NULL 但显式默认值为 0

对于 datetime 和 timestamp 列，无论在任何位置定义了小数位数，则必须在整个列定义中使用相同的值，例如

    CREATE TABLE t1 (
      ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
    );

但下面这种是不允许的

    CREATE TABLE t1 (
      ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(3)
    );

Java 面试宝典是大明哥全力打造的 Java 精品面试题，它是一份靠谱、强大、详细、经典的 Java 后端面试宝典。它不仅仅只是一道道面试题，而是一套完整的 Java 知识体系，一套你 Java 知识点的扫盲贴。

它的内容包括：

  • 大厂真题：Java 面试宝典里面的题目都是最近几年的高频的大厂面试真题。
  • 原创内容：Java 面试宝典内容全部都是大明哥原创，内容全面且通俗易懂，回答部分可以直接作为面试回答内容。
  • 持续更新：一次购买，永久有效。大明哥会持续更新 3+ 年，累计更新 1000+，宝典会不断迭代更新，保证最新、最全面。
  • 覆盖全面：本宝典累计更新 1000+，从 Java 入门到 Java 架构的高频面试题，实现 360° 全覆盖。
  • 不止面试：内容包含面试题解析、内容详解、知识扩展，它不仅仅只是一份面试题，更是一套完整的 Java 知识体系。
  • 宝典详情：https://www.yuque.com/chenssy/sike-java/xvlo920axlp7sf4k
  • 宝典总览：https://www.yuque.com/chenssy/sike-java/yogsehzntzgp4ly1
  • 宝典进展：https://www.yuque.com/chenssy/sike-java/en9ned7loo47z5aw

目前 Java 面试宝典累计更新 400+ 道，总字数 42w+。大明哥还在持续更新中，下图是大明哥在 2024-12 月份的更新情况：

想了解详情的小伙伴，扫描下面二维码加大明哥微信【daming091】咨询

同时，大明哥也整理一套目前市面最常见的热点面试题。微信搜[大明哥聊 Java]或扫描下方二维码关注大明哥的原创公众号[大明哥聊 Java] ，回复【面试题】 即可免费领取。

阅读全文
文章标签： MySQL