MySQL中datetime和timestamp的区别及使用详解

概述

MySQL中的datetime和timestamp类型都是用来存储时间的，两者使用起来有一些区别，主要体现在存储范围、存储方式、时区等方面。

datetime类型

datetime类型是用来存储日期和时间的，存储范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59'，精度为秒。在存储方式方面，datetime类型会将时间转换成UTC时间（协调世界时），存储在磁盘上。在查询时，将UTC时间转换为当前时区的时间。

创建datetime类型的字段

CREATE TABLE mytable (
    id INT NOT NULL PRIMARY KEY,
    create_time DATETIME
);

示例1：插入当前时间

INSERT INTO mytable (id, create_time) VALUES (1, NOW());

示例2：查询指定时间段内的数据

SELECT * FROM mytable WHERE create_time BETWEEN '2021-01-01 00:00:00' AND '2021-01-31 23:59:59';

timestamp类型

timestamp类型和datetime类型一样，也是用来存储日期和时间的，存储范围为'1970-01-01 00:00:01'到'2038-01-19 03:14:07'，精度为秒。在存储方式方面，timestamp类型会将时间转换成UTC时间（协调世界时），存储在磁盘上。在查询时，将UTC时间转换为当前时区的时间。

与datetime类型不同的是，如果没有指定timestamp类型的值，或者指定的值为NULL，那么该字段的默认值将设置为当前时间。而且，timestamp类型只占用了4个字节的存储空间，比datetime类型少了3个字节，因此timestamp类型一般比datetime类型更省空间。

创建timestamp类型的字段

CREATE TABLE mytable (
    id INT NOT NULL PRIMARY KEY,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

示例1：插入当前时间

INSERT INTO mytable (id) VALUES (1);

示例2：查询指定时间段内的数据

SELECT * FROM mytable WHERE create_time BETWEEN '2021-01-01 00:00:00' AND '2021-01-31 23:59:59';

datetime和timestamp的选择

当需要存储特定日期和时间时，使用datetime类型更加适合。而timestamp类型一般用于记录数据的创建和更新时间，在这种情况下，可以省去设置默认值的步骤，并且占用更小的存储空间。但是需要注意，如果数据库的时区和应用的时区不同，那么timestamp类型有可能会导致一些不便之处，因为它会根据数据库的时区返回时间。