MySQL 中 datetime 和 timestamp 类型的比较

2023-11-13 14:25| 来源: 网络整理| 查看: 265

DATETIME、TIMESTAMP 是 MySQL 中常用的日期/时间数据类型。其中 DATETIME 和 TIMESTAMP 在表象上有相似的地方，但在本质上却有许多不同的地方，如果再涉及到时区（timezone），则又有一些不同的地方。

⒈ 格式

MySQL 中的日期/时间的格式可以是字符串类型或数值类型，具体取决于上下文。如果上下文中期望的输入是一个日期类型，那么 '2015-07-21' 、'20150721' 、20150721 都会被解释为 date 类型。

在标准 SQL 中，日期/时间类型必须由一个类型关键字和一个字符串指定。

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

MySQL 继承了标准 SQL 的规范，同时也做了一些扩展。首先，在 MySQL 中不需要指定类型关键字；另外，MySQL 还可以识别对应于标准 SQL 规范的 ODBC 规范。

{ d 'str' } { t 'str' } { ts 'str' } 在 MySQL 中，`TIMESTAMP` 语法最终生成的是一个 `DATETIME` 类型的值，因为 MySQL 中的 `DATETIME` 的范围更接近标准 SQL 中的 `TIMESTAMP` 类型（MySQL 中 `TIMESTAMP` 类型从 1970 年到 2038 年，标准 SQL 中 `TIMESTAMP` 类型从 0001 年到 9999 年） ⓵ MySQL 支持的 DATETIME 和 TIMESTAMP 的格式：

字符串格式 'YYYY-MM-DD hh:mm:ss' 或 'YY-MM-DD hh:mm:ss' 。其中，任何标点符号都可以作为日期或时间的分隔符，例如 '2012-12-31 11:30:45', '2012^12^31 11+30+45', '2012/12/31 11*30*45' 和'2012@12@31 11^30^45' 的结果都是等价的。另外，日期和时间中间的分隔符可以是空格，也可以是 T ，如 '2012-12-31 11:30:45' 和 '2012-12-31T11:30:45' 的结果也是等价的。

没有分隔符的字符串格式 'YYYYMMDDhhmmss' 或 'YYMMDDhhmmss'。例如 '20070523091528' 和 '070523091528' 都会被解释为 '2007-05-23 09:15:28' ，但 '071122129015' 会被解释为 '0000-00-00 00:00:00' ，因为 '90' 不是一个有效的小时数。

数值类型的 YYYYMMDDhhmmss 或 YYMMDDhhmmss。

如果格式中年为两位数，则 MySQL 的解析规则如下： 1. 70-99 之间的年会被解释为 1970-1999 2. 00-69 之间的年会被解释为 2000-2069

对于字符串格式，如果月份、日期、小时、分钟、秒的值小于 10，则可以不加前导 0，如 '2015-6-9 1:2:3' 会被解释为 2015-06-09 01:02:03 。

对于数值格式，长度应该为 6、8、12 或 14 位。如果长度为 8 或 14，则年的长度为 4 位，否则年的长度为 2 位。

⓶ 小数秒

MySQL 中 DATETIME 和 TIMESTAMP 类型支持小数秒，精度最长为 6 位（微秒）。定义小数秒的语法为 type_name(fsp) 。

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

当向数据表中写入带有小数秒的记录时，如果字段定义的精度小于写入的值的精度，则默认会将小数秒四舍五入到字段定义的精度。如果开启了 TIME_TRUNCATE_FRACTIONAL 模式，则将小数秒截取到字段定义的精度。

CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) ); // 四舍五入 INSERT INTO fractest VALUES('17:51:04.777', '2018-09-08 17:51:04.777', '2018-09-08 17:51:04.777'); mysql> SELECT * FROM fractest; +-------------+------------------------+------------------------+ | c1 | c2 | c3 | +-------------+------------------------+------------------------+ | 17:51:04.78 | 2018-09-08 17:51:04.78 | 2018-09-08 17:51:04.78 | +-------------+------------------------+------------------------+ 1 row in set (0.00 sec) // 截取 SET @@sql_mode = sys.list_add(@@sql_mode, 'TIME_TRUNCATE_FRACTIONAL'); INSERT INTO fractest VALUES('17:51:04.777', '2018-09-08 17:51:04.777', '2018-09-08 17:51:04.777'); mysql> SELECT * FROM fractest; +-------------+------------------------+------------------------+ | c1 | c2 | c3 | +-------------+------------------------+------------------------+ | 17:51:04.78 | 2018-09-08 17:51:04.78 | 2018-09-08 17:51:04.78 | | 17:51:04.77 | 2018-09-08 17:51:04.77 | 2018-09-08 17:51:04.77 | +-------------+------------------------+------------------------+ 2 rows in set (0.00 sec) 小数秒和 `DATETIME` 或 `TIMESTAMP` 之间的分隔符只能是 `.` ⒉ 范围

在 MySQL 中，无论 DATETIME 类型的数据以哪种格式写入，最终 MySQL 解析和展示都是采用 YYYY-MM-DD hh:mm:ss 的格式，其支持的范围为 1000-01-01 00:00:00 到 9999-12-31 23:59:59 。

对于 `DATETIME` 类型，虽然支持的范围从 `1000-01-01 00:00:00` 开始，但早于这个时间的日期时间也可以写入： CREATE TABLE `time_format_test` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `dt` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='时间日期格式测试' mysql> insert into time_format_test (ts, dt) values (now(), '0800-01-01 00:00:00'); Query OK, 1 row affected (0.00 sec) mysql> select * from time_format_test; +----+---------------------+---------------------+ | id | ts | dt | +----+---------------------+---------------------+ | 1 | 2021-11-25 08:53:29 | 0800-01-01 00:00:00 | +----+---------------------+---------------------+ 1 row in set (0.00 sec)

MySQL 中 TIMESTAMP 所支持的范围要远小于 DATETIME ，其范围只有 UTC 1970-01-01 00:00:01 到 UTC 2038-01-19 03:14:07 。

`DATETIME` 和 `TIMESTAMP` 类型都可以包含小数秒，在往这两种数据类型的列中写入包含小数秒的值时，这些小数秒也会被存储。

包含小数秒之后，DATETIME 的范围变为 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999 。相应的，TIMESTAMP 的范围变为 UTC 1970-01-01 00:00:01.000000 到 UTC 2038-01-19 03:14:07.999999 。

⒊ 时区

MySQL 中，TIMESTAMP 类型的值在保存时会由当前时区转换成 UTC ，在读取时则会从 UTC 转换成指定的时区。而 DATETIME 类型的值在保存和读取时与时区无关，在底层会以 bigint 类型的值存储。默认情况下，MySQL 连接的时区采用的是 MySQL 服务的时区，而 MySQL 服务的时区默认情况下与所在服务器的时区保持一致。如果 MySQL 连接中指定了时区，则当前时区为 MySQL 连接中指定的时区.

CREATE TABLE `time_format_test` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `dt` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='时间日期格式测试' // 时区默认与服务器时区一致 mysql> select @@time_zone; +-------------+ | @@time_zone | +-------------+ | SYSTEM | +-------------+ 1 row in set (0.00 sec) mysql> insert into time_format_test (ts, dt) values (now(), now()); Query OK, 1 row affected (0.01 sec) mysql> select * from time_format_test; +----+---------------------+---------------------+ | id | ts | dt | +----+---------------------+---------------------+ | 1 | 2021-11-25 10:05:46 | 2021-11-25 10:05:46 | +----+---------------------+---------------------+ 1 row in set (0.00 sec) // 修改时区设置，TIMESTAMP 类型的值会随时区变化，DATETIME 类型的值不变 mysql> set time_zone = '+0:00'; Query OK, 0 rows affected (0.00 sec) mysql> select @@time_zone; +-------------+ | @@time_zone | +-------------+ | +00:00 | +-------------+ 1 row in set (0.00 sec) mysql> select * from time_format_test; +----+---------------------+---------------------+ | id | ts | dt | +----+---------------------+---------------------+ | 1 | 2021-11-25 02:05:46 | 2021-11-25 10:05:46 | +----+---------------------+---------------------+ 1 row in set (0.00 sec) mysql> set time_zone = '+8:00'; Query OK, 0 rows affected (0.00 sec) mysql> select @@time_zone; +-------------+ | @@time_zone | +-------------+ | +08:00 | +-------------+ 1 row in set (0.00 sec) mysql> select * from time_format_test; +----+---------------------+---------------------+ | id | ts | dt | +----+---------------------+---------------------+ | 1 | 2021-11-25 10:05:46 | 2021-11-25 10:05:46 | +----+---------------------+---------------------+ 1 row in set (0.00 sec)

TIMESTAMP 类型的值会随着时区变化，与之相关的函数 FROM_UNIXTIME() 的值也会随着时区变化。

mysql> select unix_timestamp('2021-11-25 10:05:46'); +---------------------------------------+ | unix_timestamp('2021-11-25 10:05:46') | +---------------------------------------+ | 1637805946 | +---------------------------------------+ 1 row in set (0.00 sec) mysql> set time_zone = '+0:00'; Query OK, 0 rows affected (0.00 sec) mysql> select from_unixtime(1637805946); +---------------------------+ | from_unixtime(1637805946) | +---------------------------+ | 2021-11-25 02:05:46 | +---------------------------+ 1 row in set (0.01 sec) mysql> set time_zone = '+8:00'; Query OK, 0 rows affected (0.00 sec) mysql> select from_unixtime(1637805946); +---------------------------+ | from_unixtime(1637805946) | +---------------------------+ | 2021-11-25 10:05:46 | +---------------------------+ 1 row in set (0.00 sec)

从 MySQL 8.0.19 开始，在写入 DATETIME 或 TIMESTAMP 类型的值时，可以指定时区。指定了时区的日期/时间在写入后会转换成数据库当前设置的时区。此后如果再更改时区设置，TIMESTAMP 类型会随时区变化，但 DATETIME 类型不会随时区变化。

在指定时区时，如果时区小于 10 ，则一定要加前导 0，否则会导致写入的值无效而变为 0 值 // 设置时区时，小于 10 的时区一定要加前导 0 mysql> insert into time_format_test (ts, dt) values ('2021-11-25 10:35:18+09:00', '2021-11-25 10:35:18+01:00'); Query OK, 1 row affected (0.00 sec) // 设置了时区的值会转换成当前时区的值 mysql> select * from time_format_test; +----+---------------------+---------------------+ | id | ts | dt | +----+---------------------+---------------------+ | 1 | 2021-11-25 09:35:18 | 2021-11-25 17:35:18 | +----+---------------------+---------------------+ 1 row in set (0.00 sec) mysql> set time_zone = '+0:00'; Query OK, 0 rows affected (0.00 sec) mysql> select * from time_format_test; +----+---------------------+---------------------+ | id | ts | dt | +----+---------------------+---------------------+ | 1 | 2021-11-25 01:35:18 | 2021-11-25 17:35:18 | +----+---------------------+---------------------+ 1 row in set (0.00 sec) ⒋ 自动初始化以及自动更新

对于 DATETIME 和 TIMESTAMP 类型的列，可以通过设置 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 来对列值进行自动初始化以及自动更新。

对于设置了自动更新的列，如果对同一行中其他的列进行了更新操作，但值没有发生变化，那么自动更细此时并不会起作用。

mysql> update time_format_test set dt = '2021-11-25 17:35:18' where id = 1; Query OK, 0 rows affected (0.01 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> select * from time_format_test; +----+---------------------+---------------------+ | id | ts | dt | +----+---------------------+---------------------+ | 1 | 2021-11-25 09:35:18 | 2021-11-25 17:35:18 | +----+---------------------+---------------------+ 1 row in set (0.00 sec) mysql> update time_format_test set dt = now() where id = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from time_format_test; +----+---------------------+---------------------+ | id | ts | dt | +----+---------------------+---------------------+ | 1 | 2021-11-25 11:19:40 | 2021-11-25 11:19:40 | +----+---------------------+---------------------+ 1 row in set (0.00 sec)

对于 TIMESTAMP 类型，如果系统变量 explicit_defaults_for_timestamp 的值为 0，并且列值不允许为 NULL，则在给相应的列赋 NULL 值时，列值会自动变成当前的日期时间值。

CREATE TABLE `time_format_test` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `ts` timestamp, `dt` datetime, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='时间日期格式测试'; Query OK, 0 rows affected (0.05 sec) mysql> select @@explicit_defaults_for_timestamp; +-----------------------------------+ | @@explicit_defaults_for_timestamp | +-----------------------------------+ | 0 | +-----------------------------------+ 1 row in set (0.00 sec) mysql> insert into time_format_test (ts, dt) values (null, null); Query OK, 1 row affected (0.00 sec) mysql> select * from time_format_test; +----+---------------------+------+ | id | ts | dt | +----+---------------------+------+ | 1 | 2021-11-25 11:27:58 | NULL | +----+---------------------+------+ 1 row in set (0.00 sec)

对于 TIMESTAMP 类型，如果系统变量 explicit_defaults_for_timestamp 的值为 0，并且列值不允许为 NULL ，那么第一个类型为 TIMESTAMP 的列会自动添加 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 。

CREATE TABLE `time_format_test` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `ts1` timestamp, `ts2` timestamp, `dt` datetime, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='时间日期格式测试'; Query OK, 0 rows affected (0.04 sec) mysql> show create table time_format_test; +------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | time_format_test | CREATE TABLE `time_format_test` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `ts1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `ts2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `dt` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='时间日期格式测试' | +------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

如果要避免上述这种情况，可以在定义数据表时为 TIMESTAMP 类型的列手动设置默认值或允许其值为 NULL 。除此之外，还可以将系统变量 explicit_defaults_for_timestamp 的值设为 1，这意味着 TIMESTAMP 类型的列的默认值以及自动更新需要明确指定。

如果 TIMESTAMP 或 DATETIME 类型的列在定义时指定了小数秒的精度，那么自始至终，同一个列的小数秒的精度应该相同。

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) );

【本文地址】

公司简介

联系我们