Mysql中有关Datetime和Timestamp的使用总结

Datetime和Timestamp的概述

DateTime和Timestamp是Mysql中常用的两种日期/时间类型。

• 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和Timestamp的相同点

• 都可以保存日期和时间。
• 存储的时间精确到秒。

Datetime和Timestamp的不同点

• 存储的时间范围不同。
• 存储方式不同。
  • DateTime占用8个字节。
  • Timestamp占用4个字节。

Datetime和Timestamp的使用场景

• 如果需要记录比2038年大的时间，必须使用DateTime。
• 如果需要记录比1970年小的时间，也必须使用DateTime。
• 如果需要记录1970年到2038年之间的时间，可以使用Timestamp。
• 如果只是记录近期时间或用于更新时间戳，建议使用Timestamp。

示例说明

下面给出两个Datetime和Timestamp的实例说明。

示例1

首先创建一个名为test的表。

CREATE TABLE test (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL DEFAULT '',
  create_time DATETIME DEFAULT NULL,
  update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

可以看到表中有两个时间类型的字段，create_time和update_time。

插入一条测试数据。

INSERT INTO test (name) VALUES ('test');

可以看到新增的数据中，create_time和update_time都是null。

SELECT * FROM test;

id	name	create_time	update_time
1	test	NULL	2021-06-02 10:56:36

现在再执行一条update语句。

UPDATE test SET name='newtest' WHERE id=1;

执行完之后再次查询，可以看到update_time已经被赋值为当前时间（当前时间是2021-06-02 11:01:56）。

SELECT * FROM test;

id	name	create_time	update_time
1	newtest	NULL	2021-06-02 11:01:56

示例2

接着创建一个名为test2的表。

CREATE TABLE test2 (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL DEFAULT '',
  create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

可以看到表中只有一个时间类型的字段create_time。

插入一条测试数据。

INSERT INTO test2 (name) VALUES ('test');

查询数据。

SELECT * FROM test2;

可以看到新增的数据中，create_time已经被赋值为当前时间（当前时间是2021-06-02 11:06:57）。

id	name	create_time
1	test	2021-06-02 11:06:57

再执行一条update语句。

UPDATE test2 SET name='newtest' WHERE id=1;

查询数据。

SELECT * FROM test2;

可以看到create_time的值没变。

id	name	create_time
1	newtest	2021-06-02 11:06:57

可以看到，对于Timestamp类型的字段，只有新增时会自动赋值为当前时间，而更新时不会改变它的值，必须手动赋值。