MySQL中的日期和时间类型的精度和default值的问题

mysql中，日期和时间值可以用五种数据类型表示, DATE、TIME、YEAR、DATETIME、TIMESTAMP。每个时态类型都有一个有效值范围。

时态类型的字面量

字面量：在计算机科学中，字面量（literal）是用于表达源代码中一个固定值的表示法（notation）。在当前场景下，说白了就是日期和时间类型的表示方式。

DATE

支持的时间范围：'1000-01-01' to '9999-12-31'

显示值：YYYY-MM-DD

Time[(fsp)]

支持的时间范围：'-838:59:59.000000' to '838:59:59.000000'

显示值：hh:mm:ss[.fraction]

YEAR[(4)]

支持的时间范围：1901 ~ 2155

显示值：YYYY

DATETIME[(fsp)]

支持的时间范围： '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.499999'

显示值：YYYY-MM-DD hh:mm:ss[.fraction]

TIMESTAMP[(fsp)]

支持的时间范围： '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.499999'

显示值：YYYY-MM-DD hh:mm:ss[.fraction]

存储值：TIMESTAMP值被存储为自epoch ('1970-01-01 00:00:00' UTC)以来的秒数。TIMESTAMP不能表示'1970-01-01 00:00:00'，因为它相当于从epoch开始的0秒，而0值被保留用于表示'0000-00-00 00:00:00'，即TIMESTAMP的“零”值。

MySQL允许为TIME, DATETIME和TIMESTAMP值设置小数秒，精度可达微秒(6位)。要定义包含小数秒部分的列，请使用语法type_name(fsp)，其中type_name是TIME、DATETIME或TIMESTAMP, fsp是小数秒精度。fsp的值在0到6之间，值为0表示没有小数部分。如果省略fsp,默认精度是0

接收值

虽然各个数据类型各自的时间范围如上，显示值也各不同，如YYYY-MM-DD、YYYY-MM-DD hh:mm:ss等，但是在mysql接受这些值，日期和时间值可以用几种格式表示，例如引号字符串或数字，具体取决于值的确切类型和其他因素。例如，在MySQL需要日期的上下文中，它将'2015-07-21'，'20150721'和'20150721'中的任何一个解释为日期，它接受更“宽松”的语法。

如DATE类型，任务标点符号都可以用作日期部分之间的分隔符。如，“2012-12-31”、“2012/12/31”、“2012^12^31”和“2012@12@31”是等价的。

作为'YYYYMMDD'或'YYMMDD'格式的无分隔符的字符串，前提是该字符串作为日期有意义。例如，'20070523'和'070523'被解释为'2007-05-23'，但'071332'是非法的(它有无意义的月和日部分)，并变成'0000-00-00'。

如Datetime和TimeStamp类型，任何标点符号都可以用作日期部分或时间部分之间的分隔符。例如，'2012-12-31 11:30:45'，'2012^12^31 11+30+45'，'2012/12/31 113045'和'2012@12@31 11^30^45'是等价的。

期和时间部分与小数秒部分之间唯一可识别的分隔符是小数点。

日期和时间部分可以用T而不是空格分隔。例如，'2012-12-31 11:30:45' '2012-12-31T11:30:45'是等价的。

什么是epoch?
1970年1月1日00:00:00 UTC是一个重要的时间点，被称为"UNIX纪元"（UNIX epoch）或"UNIX时间戳起点"（UNIX timestamp origin）。在计算机科学和操作系统中，UNIX纪元是一个参考时间点，用来表示时间和日期。它被广泛用于UNIX、Linux以及其他许多操作系统和编程语言中。UNIX纪元定义了一个基准时间，以秒为单位计算自1970年1月1日00:00:00 UTC到某个时间点的时间差。通过在UNIX纪元基准时间上增加或减去特定的秒数，可以表示任意时间点。例如，UNIX时间戳表示从UNIX纪元开始到某个时间的秒数。对于UNIX时间戳而言，1970年1月1日00:00:00 UTC的时间戳为0，之后的时间戳是相对于这个基准时间的偏移量。NIX纪元的选择是为了方便计算机系统的时间表示和计算，同时具有易于处理和跨平台兼容的特性。

关于零值

如果SQL模式允许转换，无效的DATE、DATETIME或TIMESTAMP值将被转换为相应类型的“零”值('0000-00-00'或'0000-00-00 00:00:00')。

时间类型的存储要求

注：5.6.4版本之后，time、datetime、timestamp允许具有小数部分，它们需要0~3个字节。

例如，TIME(0)、TIME(2)、TIME(4)、TIME(6)分别使用3、4、5、6个字节。TIME和TIME(0)是等价的，需要相同的存储空间。

初始化

Timestamp和datetime列可以自动初始化并更新为当前日期和时间。对于表中的timestamp和datetime列，可以将当前时间戳指定为默认值，自动更新值（auto-update)值，或者两者兼而有之。默认值：DEFAULT CURRENT_TIMESTAMP 。对于未为该列指定值的插入行，将自动初始化列为当前时间戳。自动更新值（auto-updated):DEFAULT CURRENT_TIMESTAMP 。当行中的其他列值被更新时，自动更新列将自动更新为当前时间戳。

CURRENT_TIMESTAMP()， NOW()， LOCALTIME, LOCALTIME()， LOCALTIMESTAMP和LOCALTIMESTAMP() 都可以作为default值。

可以同时指定两者：

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


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和timestamp的区别

• 存储范围：DATETIME数据类型可以存储的日期范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'，精确到秒。而TIMESTAMP数据类型可以存储的日期范围是从 '1970-01-01 00:00:01' 到 '2038-01-19 03:14:07'，精确到秒。

• 存储方式：DATETIME以5~8字节(5是小数之前的部分，0-3是小数之后的部分)存储，而TIMESTAMP以4字节存储。

• 时区处理：DATETIME不考虑时区，保存的值是与数据库服务器的时区无关的。而TIMESTAMP保存的值是相对于时区的，会自动将存储的时间从当前会话的时区转换为UTC进行存储，然后在检索时再转换回会话的时区。

总结

mysql中，日期和时间值可以用五种数据类型表示, DATE、TIME、YEAR、DATETIME、TIMESTAMP。每个时态类型都有一个有效值范围。其中，MySQL允许为TIME, DATETIME和TIMESTAMP值设置小数秒，精度可达微秒(6位)。要定义包含小数秒部分的列，语法规则type_name(fsp)，其中type_name是TIME、DATETIME或TIMESTAMP, fsp是小数秒精度。fsp的值在0到6之间，值为0表示没有小数部分。如果省略fsp,默认精度是0

Timestamp和datetime列可以自动初始化并更新为当前日期和时间。对于表中的timestamp和datetime列，可以将当前时间戳指定为默认值：DEFAULT CURRENT_TIMESTAMP ，自动更新值（auto-update)值：DEFAULT CURRENT_TIMESTAMP，或者两者兼而有之。

参考：https://dev.mysql.com/doc/refman/5.7/en/datetime.html

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-literals.html

点个“赞 or 在看” 你最好看！

喜欢，就关注我吧！

👇👇👇 谢谢各位老板啦！！！