MySQL日期/时间类型及自动填充机制

前言

在前期文章中，我们介绍了MySQL的Numeric数据类型。本文将重点探讨MySQL中的日期/时间类型及其自动填充机制，为数据库设计和操作提供重要参考。

一、日期/时间类型
  MySQL提供了五种主要的日期/时间数据类型：Date、Datetime、Timestamp、time、year。每种类型有其特定的应用场景和取值范围。  1. Date
  日期类型不含时间部分，主要用于存储年、月、日的信息。
  1.1 取值范围
  1000-01-01 ~ 9999-12-31
  1.2 显示格式
  YYYY-MM-DD
  2. Datetime
  时间类型包含日期和时间部分，精确到秒或毫秒。
  2.1 取值范围
  1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.499999
  2.2 显示格式
  YYYY-MM-DD hh:mm:ss[.小数位]
  
     
      提示：小数位长度6，取值0-6。
     
  3. Timestamp
  时间戳类型用于存储时间点的精确秒数，通常用于分布式系统。
  3.1 取值范围
  ‘1970-01-01 00:00:01.000000’ UTC ~ ‘2038-01-19 03:14:07.499999’ UTC.
  
     
      提示：Timestamp值存储为自‘1970-01-01 00:00:00’UTC以来的秒数。Timestamp不能表示值“1970-01-01 00:00:00”，因为这相当于从1970-01-01 00:00:00算起的0秒。如果表示0值，可输入“0000-00-00 00:00:00”。
     
  3.2 显示格式
  YYYY-MM-DD hh:mm:ss[.小数位]
  
     
      提示：小数位长度6，取值0-6。
     
  4. time
  时间类型用于存储时间部分（小时、分钟、秒），不包含日期信息。
  4.1 取值范围
  -838:59:59.000000 ~ 838:59:59.000000
  4.2 显示格式
  hh:mm:ss[.小数位]
  
     
      提示：小数位长度6，取值0-6。
     
  5. year
  年份类型用于存储四位数的年份信息。
  5.1 取值范围
  1901 ~ 2155或0000
  5.2 显示格式
  YYYY
  二、自动填充机制
  MySQL支持对Timestamp和Datetime类型的字段进行自动填充，默认为当前数据库服务器时间。以下是实现自动填充的方法及注意事项：  1. 原理介绍
  对于包含Timestamp或Datetime类型的字段，你可以通过设置默认值为当前时间戳来实现自动填充。当行中其他列发生变化时，自动更新的字段会更新为当前时间戳。
  2. 实现方式
  在列定义中使用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP子句即可实现自动填充。
  2.1 案例1
  默认为当前时间戳，且在更新时也会自动更新为最新时间戳。
  CREATE TABLE t1 (    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,    dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  );
  2.2 案例2
  默认为当前时间戳，但不会在更新时自动填充。
  CREATE TABLE t1 (    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,    dt DATETIME DEFAULT CURRENT_TIMESTAMP  );
  2.3 案例3
  默认为一个常量，不会自动更新为最新时间戳。
  CREATE TABLE t1 (    ts TIMESTAMP DEFAULT 0,    dt DATETIME DEFAULT 0  );
  2.4 案例4
  默认为一个常量，但在更新时会自动填充为最新时间戳。
  CREATE TABLE t1 (    ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,    dt DATETIME DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP  );
  2.5 案例5
  默认值设置为NULL，但在更新时会自动填充为当前时间戳。
  CREATE TABLE t1 (    ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,     -- default 0    ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL  );
  
     
      同样，除非使用NOT NULL定义，否则Datetime的默认值为0：
     
  CREATE TABLE t1 (    dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP,         -- default NULL    dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0  );
  结语
  本文详细介绍了MySQL中的日期/时间类型及其自动填充机制，为数据库设计和操作提供了重要的参考依据。理解这些类型及其自动填充特性，有助于我们在实际项目中做出更合适的数据库设计选择。  
     
  精彩回放