MySQL 中你应该使用什么数据类型表示时间?

本文对比分析了MySQL中Datetime、Timestamp及INT类型在保存日期时间数据时的性能表现,通过基准测试揭示了不同场景下的最优选择。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

导读当你需要保存日期时间数据时,一个问题来了:你应该使用 MySQL 中的什么类型?使用 MySQL 原生的 DATE 类型还是使用 INT 字段把日期和时间保存为一个纯数字呢?

在这篇文章中,我将解释 MySQL 原生的方案,并给出一个最常用数据类型的对比表。我们也将对一些典型的查询做基准测试,然后得出在给定场景下应该使用什么数据类型的结论。
如果你想直接看结论,请翻到文章最下方。

原生的 MySQL Datetime 数据类型

Datetime 数据表示一个时间点。这可以用作日志记录、物联网时间戳、日历事件数据,等等。MySQL 有两种原生的类型可以将这种信息保存在单个字段中:Datetime 和 Timestamp。MySQL 文档中是这么介绍这些数据类型的:

DATETIME 类型用于保存同时包含日期和时间两部分的值。MySQL 以 'YYYY-MM-DD HH:MM:SS' 形式接收和显示 DATETIME 类型的值。
TIMESTAMP 类型用于保存同时包含日期和时间两部分的值。
DATETIME 或 TIMESTAMP 类型的值可以在尾部包含一个毫秒部分,精确度最高到微秒(6 位数)。
TIMESTAMP 和 DATETIME 数据类型提供自动初始化和更新到当前的日期和时间的功能,只需在列的定义中设置 DEFAULT CURRENTTIMESTAMP 和 ON UPDATE CURRENTTIMESTAMP。

作为一个例子:

CREATE TABLE `datetime_example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `measured_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `measured_on` (`measured_on`)
) ENGINE=InnoDB;
CREATE TABLE `timestamp_example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `measured_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `measured_on` (`measured_on`)
) ENGINE=InnoDB;

除了原生的日期时间表示方法,还有另一种常用的存储日期和时间信息的方法。即使用 INT 字段保存 Unix 时间(从1970 年 1 月 1 日协调世界时(UTC)建立所经过的秒数)。
MySQL 也提供了只保存时间信息中的一部分的方式,通过使用 Date、Year 或 Time 类型。由于这篇文章是关于保存准确时间点的最佳方式的,我们没有讨论这些不那么精确的局部类型。

使用 INT 类型保存 Unix 时间

使用一个简单的 INT 列保存 Unix 时间是最普通的方法。使用 INT,你可以确保你要保存的数字可以快速、可靠地插入到表中,就像这样:

INSERT INTO `vertabelo`.`sampletable`
(
 `id`,
 `measured_on` ### INT 类型的列
)
VALUES
(
 1,
 946684801
 ### 至 01/01/2000 @ 12:00am (UTC) 的 UNIX 时间戳 http://unixtimestamp.com
);

这就是关于它的所有内容了。它仅仅是个简单的 INT 列,MySQL 的处理方式是这样的:在内部使用 4 个字节保存那些数据。所以如果你在这个列上使用 SELECT 你将会得到一个数字。如果你想把这个列用作日期进行比较,下面的查询并不能正确工作:

SELECT
    id, measured_on, FROM_UNIXTIME(measured_on)
FROM
    vertabelo.inttimestampmeasures
WHERE
    measured_on > '2016-01-01' ### measured_on 会被作为字符串比较以进行查询
LIMIT 5;

这是因为 MySQL 把 INT 视为数字,而非日期。为了进行日期比较,你必须要么获取( LCTT 译注:从 1970-01-01 00:00:00)到 2016-01-01 经过的秒数,要么使用 MySQL 的 FROM_UNIXTIME() 函数把 INT 列转为 Date 类型。下面的查询展示了 FROM_UNIXTIME() 函数的用法:

SELECT
    id, measured_on, FROM_UNIXTIME(measured_on)
FROM
    vertabelo.inttimestampmeasures
WHERE
    FROM_UNIXTIME(measured_on) > '2016-01-01'
LIMIT 5;

这会正确地获取到日期在 2016-01-01 之后的记录。你也可以直接比较数字和 2016-01-01 的 Unix 时间戳表示形式,即 1451606400。这样做意味着不用使用任何特殊的函数,因为你是在直接比较数字。查询如下:

SELECT
    id, measured_on, FROM_UNIXTIME(measured_on)
FROM
    vertabelo.inttimestampmeasures
WHERE
   measured_on > 1451606400
LIMIT 5;

假如这种方式不够高效甚至提前做这种转换是不可行的话,那该怎么办?例如,你想获取 2016 年所有星期三的记录。要做到这样而不使用任何 MySQL 日期函数,你就不得不查出 2016 年每个星期三的开始和结束时间的 Unix 时间戳。然后你不得不写很大的查询,至少要在 WHERE 中包含 104 个比较。(2016 年有 52 个星期三,你不得不考虑一天的开始(0:00 am)和结束(11:59:59 pm)...)
结果是你很可能最终会使用 FROM_UNIXTIME() 转换函数。既然如此,为什么不试下真正的日期类型呢?

使用 Datetime 和 Timestamp

Datetime 和 Timestamp 几乎以同样的方式工作。两种都保存日期和时间信息,毫秒部分最高精确度都是 6 位数。同时,使用人类可读的日期形式如 "2016-01-01" (为了便于比较)都能工作。查询时两种类型都支持“宽松格式”。宽松的语法允许任何标点符号作为分隔符。例如,"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

其它宽松格式也是允许的;你可以在 MySQL 参考手册 找到所有的格式。
默认情况下,Datetime 和 Timestamp 两种类型查询结果都以标准输出格式显示 —— 年-月-日 时:分:秒 (如 2016-01-01 23:59:59)。如果使用了毫秒部分,它们应该以小数值出现在秒后面 (如 2016-01-01 23:59:59.5)。
Timestamp 和 Datetime 的核心不同点主要在于 MySQL 在内部如何表示这些信息:两种都以二进制而非字符串形式存储,但在表示日期/时间部分时 Timestamp (4 字节) 比 Datetime (5 字节) 少使用 1 字节。当保存毫秒部分时两种都使用额外的空间 (1-3 字节)。如果你存储 150 万条记录,这种 1 字节的差异是微不足道的:

150 万条记录 * 每条记录 1 字节 / (1048576 字节/MB) = 1.43 MB

Timestamp 节省的 1 字节是有代价的:你只能存储从 '1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999' 之间的时间。而 Datetime 允许你存储从 '1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999' 之间的任何时间。
另一个重要的差别 —— 很多 MySQL 开发者没意识到的 —— 是 MySQL 使用服务器的时区转换 Timestamp 值到它的 UTC 等价值再保存。当获取值是它会再次进行时区转换,所以你得回了你“原始的”日期/时间值。有可能,下面这些情况会发生。
理想情况下,如果你一直使用同一个时区,MySQL 会获取到和你存储的同样的值。以我的经验,如果你的数据库涉及时区变换,你可能会遇到问题。例如,服务器变化(比如,你把数据库从都柏林的一台服务器迁移到加利福尼亚的一台服务器上,或者你只是修改了一下服务器的时区)时可能会发生这种情况。不管哪种方式,如果你获取数据时的时区是不同的,数据就会受影响。
Datetime 列不会被数据库改变。无论时区怎样配置,每次都会保存和获取到同样的值。就我而言,我认为这是一个更可靠的选择。

MySQL 文档:
MySQL 把 TIMESTAMP 值从当前的时区转换到 UTC 再存储,获取时再从 UTC 转回当前的时区。(其它类型如 DATETIME 不会这样,它们会“原样”保存。) 默认情况下,每个连接的当前时区都是服务器的时区。时区可以基于连接设置。只要时区设置保持一致,你就能得到和保存的相同的值。如果你保存了一个 TIMESTAMP 值,然后改变了时区再获取这个值,获取到的值和你存储的是不同的。这是因为在写入和查询的会话上没有使用同一个时区。当前时区可以通过系统变量 time_zone 的值得到。更多信息,请查看 MySQL Server Time Zone Support。

对比总结

在深入探讨使用各数据类型的性能差异之前,让我们先看一个总结表格以给你更多了解。每种类型的弱点以红色显示。

特性DatetimeTimestampInt (保存 Unix 时间)
原生时间表示否,所以大多数操作需要先使用转换函数,如 FROM_UNIXTIME()
能保存毫秒是,最高 6 位精度是,最高 6 位精度
合法范围'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'若使用 unsigned, '1970-01-01 00:00:01.000000; 理论上最大到 '2106-2-07 06:28:15'
自动初始化(MySQL 5.6.5+)
宽松解释 (MySQL docs否,必须使用正确的格式
值被转换到 UTC 存储
可转换到其它类型是,如果值在合法的 Timestamp 范围中是,总是是,如果值在合法的范围中并使用转换函数
存储需求(MySQL 5.6.4+)5 字节(如果使用了毫秒部分,再加最多 3 字节)4 字节 (如果使用了毫秒部分,再加最多 3 字节)4 字节 (不允许毫秒部分)
无需使用函数即可作为真实日期可读否,你必须格式化输出
数据分区是,使用 UNIX_TIMESTAMP();在 MySQL 5.7 中其它表达式是不允许包含 TIMESTAMP 值的。同时,注意分区裁剪时的这些考虑是,使用 INT 上的任何合法操作
基准测试 INT、Timestamp 和 Datetime 的性能

为了比较这些类型的性能,我会使用我创建的一个天气预报网络的 150 万记录(准确说是 1,497,421)。这个网络每分钟都收集数据。为了让这些测试可复现,我已经删除了一些私有列,所以你可以使用这些数据运行你自己的测试。
基于我原始的表格,我创建了三个版本:

 datetimemeasures表在 measured_on 列使用 Datetime 类型,表示天气预报记录的测量时间
 timestampmeasures表在 measured_on 列使用 Timestamp 类型
 inttimestampmeasures表在 measured_on 列使用 INT (unsigned) 类型

这三个表拥有完全相同的数据;唯一的差别就是 measured_on 字段的类型。所有表都在 measured_on 列上设置了一个索引。

基准测试工具

为了评估这些数据类型的性能,我使用了两种方法。一种基于 Sysbench,它的官网是这么描述的:

... 一个模块化、跨平台和多线程的基准测试工具,用以评估那些对运行高负载数据库的系统非常重要的系统参数。

这个工具是 MySQL 文档中推荐的。

如果你使用 Windows (就像我),你可以下载一个包含可执行文件和我使用的测试查询的 zip 文件。它们基于 一种推荐的基准测试方法。

为了执行一个给定的测试,你可以使用下面的命令(插入你自己的连接参数):

sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo --MySQL-user=root --MySQL-host=localhost --MySQL-password= --test=sysbench_test_file.lua --num-threads=8 --max-requests=100 run

这会正常工作,这里 sysbench_test_file.lua 是测试文件,并包含了各个测试中指向各个表的 SQL 查询。

为了进一步验证结果,我也运行了 mysqlslap。它的官网是这么描述的:

“mysqlslap 是一个诊断程序,为模拟 MySQL 服务器的客户端负载并报告各个阶段的用时而设计。它工作起来就像是很多客户端在同时访问服务器。”

记得这些测试中最重要的不是所需的绝对时间。而是在不同数据类型上执行相同查询时的相对时间。这两个基准测试工具的测试时间不一定相同,因为不同工具的工作方式不同。重要的是数据类型的比较,随着我们深入到测试中,这将会变得清楚。

基准测试

我将使用三种可以评估几个性能方面的查询:

  • 时间范围选择
    • 在 Datetime 和 Timestamp 数据类型上这允许我们直接比较而不需要使用任何特殊的日期函数。
    • 同时,我们可以评估在 INT 类型的列上使用日期函数相对于使用简单的数值比较的影响。为了做到这些我们需要把范围转换为 Unix 时间戳数值。
  • 日期函数选择
    • 与前个测试中比较操作针对一个简单的 DATE 值相反,这个测试使得我们可以评估使用日期函数作为 “WHERE” 子句的一部分的性能。
    • 我们还可以测试一个场景,即我们必须使用一个函数将 INT 列转换为一个合法的 DATE 类型然后执行查询。
  • count() 查询
    • 作为对前面测试的补充,这将评估在三种不同的表示类型上进行典型的统计查询的性能。

我们将在这些测试中覆盖一些常见的场景,并看到三种类型上的性能表现。

关于 SQL_NO_CACHE

当在查询中使用 SQL_NO_CACHE 时,服务器不使用查询缓存。它既不检查查询缓存以确认结果是不是已经在那儿了,也不会保存查询结果。因此,每个查询将反映真实的性能影响,就像每次查询都是第一次被调用。

测试 1:选择一个日期范围中的值

这个查询返回总计 1,497,421 行记录中的 75,706 行。
查询 1 和 Datetime:

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.datetimemeasures m
WHERE
    m.measured_on > '2016-01-01 00:00:00.0'
      AND m.measured_on < '2016-02-01 00:00:00.0';

性能

响应时间 (ms)Sysbenchmysqlslap
最小152296
最大12613203
平均362809
Sysbench cmd> sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo --MySQL-user=root --MySQL-host=localhost --MySQL-password= --test=datetime.lua --num-threads=8 --max-requests=100 run
mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE measured_on FROM vertabelo.datetimemeasures m WHERE m.measured_on > '2016-01-01 00:00:00.0' AND m.measured_on < '2016-02-01 00:00:00.0'" --host=localhost --user=root --concurrency=8 --iterations=100  --no-drop --create-schema=vertabelo

查询 1 和 Timestamp:

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.timestampmeasures m
WHERE
    m.measured_on > '2016-01-01 00:00:00.0'
        AND m.measured_on < '2016-02-01 00:00:00.0';

性能

响应时间 (ms)Sysbenchmysqlslap
最小214359
最大13893313
平均4311004
Sysbench cmd> sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo --MySQL-user=root --MySQL-host=localhost --MySQL-password= --test=timestamp.lua --num-threads=8 --max-requests=100 run
mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE measured_on FROM vertabelo.timestampmeasures m WHERE m.measured_on > '2016-01-01 00:00:00.0' AND m.measured_on < '2016-02-01 00:00:00.0'" --host=localhost --user=root --concurrency=8 --iterations=100  --no-drop --create-schema=vertabelo

查询 1 和 INT:

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.inttimestampmeasures m
WHERE
    FROM_UNIXTIME(m.measured_on) > '2016-01-01 00:00:00.0'
        AND FROM_UNIXTIME(m.measured_on) < '2016-02-01 00:00:00.0';

性能

响应时间 (ms)Sysbenchmysqlslap
最小24727968
最大655410312
平均41078527
Sysbench cmd> sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo --MySQL-user=root --MySQL-host=localhost --MySQL-password= --test=int.lua --num-threads=8 --max-requests=100 run
mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE measured_on FROM vertabelo.inttimestampmeasures m WHERE FROM_UNIXTIME(m.measured_on) > '2016-01-01 00:00:00.0' AND FROM_UNIXTIME(m.measured_on) < '2016-02-01 00:00:00.0'" --host=localhost --user=root --concurrency=8 --iterations=100  --no-drop --create-schema=vertabelo

另一种 INT 上的查询 1:
由于这是个相当直接的范围搜索,而且查询中的日期可以轻易地转为简单的数值比较,我将它包含在了这个测试中。结果证明这是最快的方法 (你大概已经预料到了),因为它仅仅是比较数字而没有使用任何日期转换函数:

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.inttimestampmeasures m
WHERE
    m.measured_on > 1451617200
        AND m.measured_on < 1454295600;

性能

响应时间 (ms)Sysbenchmysqlslap
最小88171
最大2752157
平均165514
Sysbench cmd> sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo --MySQL-user=root --MySQL-host=localhost --MySQL-password= --test=basic_int.lua --num-threads=8 --max-requests=100 run
mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE measured_on FROM vertabelo.inttimestampmeasures m WHERE m.measured_on > 1451617200 AND m.measured_on < 1454295600" --host=localhost --user=root --concurrency=8 --iterations=100  --no-drop --create-schema=vertabelo

测试 1 总结

平均响应时间 (ms)Sysbench相对于 Datetime 的速度mysqlslap相对于 Datetime 的速度
Datetime362-809-
Timestamp431慢 19%1004慢 24%
INT4107慢 1134%8527慢 1054%
另一种 INT 查询165快 55%514快 36%

两种基准测试工具都显示 Datetime 比 Timestamp 和 INT 更快。但 Datetime 没有我们在另一种 INT 查询中使用的简单数值比较快。

测试 2:选择星期一产生的记录

这个查询返回总计 1,497,421 行记录中的 221,850 行。
查询 2 和 Datetime:

SELECT SQL_NO_CACHE measured_on
FROM
    vertabelo.datetimemeasures m
WHERE
    WEEKDAY(m.measured_on) = 0; # MONDAY

性能

响应时间 (ms)Sysbenchmysqlslap
最小18744343
最大61687797
平均31276103
Sysbench cmd> sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo --MySQL-user=root --MySQL-host=localhost --MySQL-password= --test=datetime_1.lua --num-threads=8 --max-requests=100 run
mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE measured_on FROM vertabelo.datetimemeasures m WHERE WEEKDAY(m.measured_on) = 0" --host=localhost --user=root --concurrency=8 --iterations=25 --no-drop --create-schema=vertabelo

查询 2 和 Timestamp:

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.timestampmeasures m
WHERE
    WEEKDAY(m.measured_on) = 0; # MONDAY

性能

响应时间 (ms)Sysbenchmysqlslap
最小26885953
最大666613531
平均36538412
Sysbench cmd> sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo --MySQL-user=root --MySQL-host=localhost --MySQL-password= --test=timestamp_1.lua --num-threads=8 --max-requests=100 run
mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE measured_on FROM vertabelo.timestampmeasures m WHERE WEEKDAY(m.measured_on) = 0" --host=localhost --user=root --concurrency=8 --iterations=25 --no-drop --create-schema=vertabelo

查询 2 和 INT:

SELECT SQL_NO_CACHE
    measured_on
FROM
    vertabelo.inttimestampmeasures m
WHERE
    WEEKDAY(FROM_UNIXTIME(m.measured_on)) = 0; # MONDAY

性能

响应时间 (ms)Sysbenchmysqlslap
最小20515844
最大700710469
平均34868088
Sysbench cmd> sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo --MySQL-user=root --MySQL-host=localhost --MySQL-password= --test=int_1.lua --num-threads=8 --max-requests=100 run
mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE measured_on FROM vertabelo.inttimestampmeasures m WHERE WEEKDAY(FROM_UNIXTIME(m.measured_on)) = 0" --host=localhost --user=root --concurrency=8 --iterations=25  --no-drop --create-schema=vertabelo

测试 2 总结

平均响应时间 (ms)Sysbench相对于 Datetime 的速度mysqlslap相对于 Datetime 的速度
Datetime3127-6103-
Timestamp3653慢 17%8412慢 38%
INT3486慢 11%8088慢 32%

再次,在两个基准测试工具中 Datetime 比 Timestamp 和 INT 快。但在这个测试中,INT 查询 —— 即使它使用了一个函数以转换日期 —— 比 Timestamp 查询更快得到结果。

测试 3:选择星期一产生的记录总数

这个查询返回一行,包含产生于星期一的所有记录的总数(从总共 1,497,421 行可用记录中)。
查询 3 和 Datetime:

SELECT SQL_NO_CACHE
    COUNT(measured_on)
FROM
    vertabelo.datetimemeasures m
WHERE
    WEEKDAY(m.measured_on) = 0; # MONDAY

性能

响应时间 (ms)Sysbenchmysqlslap
最小17204063
最大45947812
平均27975540
Sysbench cmd> sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo --MySQL-user=root --MySQL-host=localhost --MySQL-password= --test=datetime_1_count.lua --num-threads=8 --max-requests=100 run
mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE COUNT(measured_on) FROM vertabelo.datetimemeasures m WHERE WEEKDAY(m.measured_on) = 0" --host=localhost --user=root --concurrency=8 --iterations=25 --no-drop --create-schema=vertabelo

查询 3 和 Timestamp:

SELECT SQL_NO_CACHE
    COUNT(measured_on)
FROM
    vertabelo.timestampmeasures m
WHERE
    WEEKDAY(m.measured_on) = 0; # MONDAY

性能

响应时间 (ms)Sysbenchmysqlslap
最小19074578
最大543710235
平均34087102
Sysbench cmd> sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo --MySQL-user=root --MySQL-host=localhost --MySQL-password= --test=timestamp_1_count.lua --num-threads=8 --max-requests=100 run
mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE COUNT(measured_on) FROM vertabelo.timestampmeasures m WHERE WEEKDAY(m.measured_on) = 0" --host=localhost --user=root --concurrency=8 --iterations=25 --no-drop --create-schema=vertabelo

查询 3 和 INT:

SELECT SQL_NO_CACHE
    COUNT(measured_on)
FROM
    vertabelo.inttimestampmeasures m
WHERE
    WEEKDAY(FROM_UNIXTIME(m.measured_on)) = 0; # MONDAY

性能

响应时间 (ms)Sysbenchmysqlslap
最小21085609
最大47649735
平均33077416
Sysbench cmd> sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo --MySQL-user=root --MySQL-host=localhost --MySQL-password= --test=int_1_count.lua --num-threads=8 --max-requests=100 run
mysqlslap cmd> mysqlslap --query="SELECT SQL_NO_CACHE COUNT(measured_on) FROM vertabelo.inttimestampmeasures m WHERE WEEKDAY(FROM_UNIXTIME(m.measured_on)) = 0" --host=localhost --user=root --concurrency=8 --iterations=25  --no-drop --create-schema=vertabelo

测试 3 总结

平均响应时间 (ms)Sysbench相对于 Datetime 的速度mysqlslap相对于 Datetime 的速度
Datetime2797-5540-
Timestamp3408慢 22%7102慢 28%
INT3307慢 18%7416慢 33%

再一次,两个基准测试工具都显示 Datetime 比 Timestamp 和 INT 快。不能判断 INT 是否比 Timestamp 快,因为 mysqlslap 显示 INT 比 Timestamp 略快而 Sysbench 却相反。

注意: 所有测试都是在一台 Windows 10 机器上本地运行的,这台机器拥有一个双核 i7 CPU,16GB 内存,运行 MariaDB v10.1.9,使用 innoDB 引擎。

结论

基于这些数据,我确信 Datetime 是大多数场景下的最佳选择。原因是:

更快(根据我们的三个基准测试)。
无需任何转换即是人类可读的。
不会因为时区变换产生问题。
只比它的对手们多用 1 字节
支持更大的日期范围(从 1000 年到 9999 年)

如果你只是存储 Unix 时间戳(并且在它的合法日期范围内),而且你真的不打算在它上面使用任何基于日期的查询,我觉得使用 INT 是可以的。我们已经看到,它执行简单数值比较查询时非常快,因为只是在处理简单的数字。
Timestamp 怎么样呢?如果 Datetime 相对于 Timestamp 的优势不适用于你特殊的场景,你最好使用时间戳。阅读这篇文章后,你对三种类型间的区别应该有了更好的理解,可以根据你的需要做出最佳的选择。


via: http://www.vertabelo.com/blog/technical-articles/what-datatype-should-you-use-to-represent-time-in-mysql-we-compare-datetime-timestamp-and-int

作者:Francisco Claria 译者:bianjp 校对:wxy

本文由 LCTT 原创编译,Linux中国 荣誉推出

本文地址:http://www.linuxprobe.com/mysql-time.html
免费提供最新Linux技术教程书籍,为开源技术爱好者努力做得更多更好,开源站点:http://www.linuxprobe.com/


BOS 技术整理-02 Oracle 数据库安装配置使用 直接安装 打开服务 并尝试连接数据库 安装远程连接工具 PLSQLDeveloper 安装配置使用 2.在 PLSQL Developer 启动,配置 oracle instanceclient 目录 4、 默认 PLSQL Developer 连接 Oracle 使用客户端字符集 utf-8 , 基于工具使用 SQL 插入中文有乱码问题,设置客户端字符集 gb2312 配置 环境变量 NLS_LANG = SIMPLIFIED CHINESE_CHINA.ZHS16GBK 弹窗了,就表示成功了! 工具补充: Navicat Premium 安装和使用 直接安装 ok jquery easyui 总合 (直接看API文档 后期加上如何使用) Orcale 创建用户 先进入 Orcale 命令行 针对 BOS 系统建立新的用户 创建 bos/bos 账户, 授予角色 connect、resource -- Create the user create user BOS identified by "" default tablespace SYSTEM temporary tablespace TEMP profile DEFAULT password expire; -- Grant/Revoke role privileges grant connect to BOS; grant resource to BOS; -- Grant/Revoke system privileges grant unlimited tablespace to BOS; Spring 环境搭建 IOC 导入 context 依赖 beans、core、expression AOP 导入 测试集成 Web 集成 配置监听器 整合持久层框架 hibernate、jpa web.xml 配置 spring 监听器 src/main/resource 下 建立 applicationContext.xml rc/main/resource 提供 config.properties 外部属性文件、log4j.properties 日志配置文件 配置文件如下: applicationContext.xml  struts2 注解 类上 @ParentPackage 包继承谁? extends=”struts-default” @Namespace 包命名空间 namespace=”/” @Actions struts2 的 Action 配置,用于配置多个 Action 方法上 @Action 配置访问路径,和方法绑定 @Result 结果集配置  struts2 和 spring 整合 struts2 的 Action 使用 spring 注解,被 spring 管理 @Controller spring 表现层 bean @Scope 配置 Action 为多例 Spring Data JPA 代替了 Hibernate "CURD " 但是和Hibernate一样 实现DAO(数据)层的功能 "增删改查" Hibernate 实体类 生成表结构 ---- 领域(Domain)驱动建模 这次使用的是驱动建模,传统的设计模式是: 先设计表,再创建实体 JPA 注解使用 @Entity 标明是实体类 @Table 映射表名称 @Id 主键标识 @GeneratedValue 自动生成主键的值 @Column 设置映射数据表中列信息 @Temporal 控制数据表保存日期哪个部分(日期时间日期时间) @OneToMany(mappedBy 相当于 inverse ) @ManyToOne 结合 @JoinColumn 添加一列外键 @ManyToMany 一方写 mappedBy 放弃外键维护、另一方 @JoinTable 维护中间表 收派标准修添加能实现 html 请求 后台处理 服务器端Service和DAO编写整合springdatajpa 业务层接口 修改 Action 代码调用 Service 业务层实现类 在applicationContext添加一条 用来整合 Spring Data jpa <jpa:repositories base-package="cn.itcast.bao.dao" /> 注意: 这里的扫描包,必须指定到类的上级包 比如 一个类 com.itStone.bao.dao.test.java 那么它的上级包 就是 com.itStone.bao.dao 根级包是 : 就是 com 只需要编写 DAO 接口,继承 JpaRepository即可 不需要对 DAO 接口做任何实现, 实际上 spring 会调用 SimpleJpaRepository 实现 struts 2 注解
<think>好的,我现在需要帮助用户确定在MySQL数据库中`create_at`字段适用的数据类型。根据用户提供的引用内容和之前的对话历史,我需要仔细分析不同数据类型的优缺点,并结合实际应用场景给出建议。 首先,用户之前已经得到关于`DATETIME`、`TIMESTAMP`和`INT UNSIGNED`的对比,现在用户引用了三个新的资料,需要结合这些资料再次确认最适合的数据类型。引用[1]提到MySQL建表时的数据类型规范,引用[2]讨论了CreateAt和UpdateAt字段的意义,其中提到使用bigint类型,而引用[3]则展示了如何使用DATETIME并设置自动更新。 首先,我需要理解用户的需求。用户可能正在设计数据库表结构,特别是需要记录创建时间的字段。常见的数据类型选项包括`TIMESTAMP`、`DATETIME`和`INT UNSIGNED`(或BIGINT)。每个类型有各自的优缺点,需要根据具体场景来选择。 根据引用[2],CreateAt字段建议使用bigint类型存储时间戳,这可能是因为bigint可以存储更大的时间范围,避免了TIMESTAMP的2038年问题。但引用[3]中又展示了使用DATETIME类型并设置默认值为CURRENT_TIMESTAMP,并支持自动更新。这说明不同的应用场景可能需要不同的类型选择。 接下来,我需要考虑以下几点: 1. **时间范围需求**:如果应用需要存储超过2038年的时间,TIMESTAMP就不合适,必须使用DATETIME或BIGINT。 2. **存储空间**:TIMESTAMP占4字节,DATETIME占8字节,BIGINT占8字节。如果存储空间是考虑因素,TIMESTAMP更优。 3. **自动更新和默认值**:TIMESTAMP和DATETIME都支持默认值设置,如CURRENT_TIMESTAMP,而BIGINT需要应用层处理。 4. **框架兼容性**:例如,Laravel等ORM框架对时间字段的处理可能更友好于TIMESTAMP或DATETIME,因为它们可以自动转换格式,而BIGINT可能需要额外配置。 5. **索引效率**:整型(如BIGINT)的索引效率通常比日期时间类型高,尤其是在大数据量下,查询速度可能更快。 6. **可读性**:TIMESTAMP和DATETIME在查询时可以直接显示为易读的日期格式,而BIGINT需要转换才能理解。 用户之前的回答中已经对比了这些因素,现在需要结合新的引用内容来调整建议。引用[2]提到使用bigint类型,但用户之前的回答中更推荐TIMESTAMP作为默认选择,除非有特殊需求。因此,需要确认是否存在矛盾,或者是否有新的信息需要整合。 另外,引用[3]中的例子显示使用DATETIME并设置自动更新,但用户的问题是关于create_at字段,通常不需要自动更新,只需在创建时设置默认值。因此,可能需要强调create_at和update_at的不同处理方式。 可能还需要考虑不同MySQL版本的支持情况,例如,在MySQL 5.6.5之前,DATETIME不支持默认值为CURRENT_TIMESTAMP,而TIMESTAMP支持。如果用户使用MySQL版本较旧,这可能会影响选择。 综合以上因素,需要给出明确的建议,并解释不同场景下的最佳选择。同时,需要引用提供的资料来支持结论,例如引用[2]中提到使用bigint,但也要指出其优缺点,以及是否适用于用户的具体情况。 最后,用户可能需要知道如何在MySQL中具体实现这些数据类型,包括建表语句的示例,以及如何在框架(如Laravel)中配置,确保回答实用且可操作。</think>### MySQL `create_at` 字段数据类型选择指南 #### 一、核心选项与对比 根据MySQL规范和实际应用场景,`create_at`字段推荐以下三种数据类型: | **类型** | **存储空间** | **时间范围** | **自动填充** | **索引效率** | **应用场景** | |------------------|--------------|------------------|--------------|--------------|----------------------------| | `TIMESTAMP` | 4字节 | 1970-01-01 至 2038-01-19 | 支持`CURRENT_TIMESTAMP` | 高 | 通用场景(推荐默认选择)[^3] | | `DATETIME` | 8字节 | 1000-01-01 至 9999-12-31 | 支持(需MySQL ≥5.6.5) | 一般 | 需要大时间范围支持[^3] | | `BIGINT` | 8字节 | 无限制(Unix时间戳) | 需手动维护 | **最高** | 高频查询/性能优化场景[^2] | --- #### 二、具体选择建议 1. **默认选择:`TIMESTAMP`** - **优点**: - 自动填充时间(通过`DEFAULT CURRENT_TIMESTAMP`)[^3]。 - 存储空间小(4字节),索引效率高。 - Laravel等框架原生支持时间格式转换(如`created_at`自动转为Carbon对象)。 - **缺点**: - **2038年问题**(无法存储超过2038年的时间)。 ```sql -- 建表示例(自动填充创建时间) CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); ``` 2. **需要长期存储:`DATETIME`** - **适用场景**: - 历史数据需保留超过2038年(如金融、法律系统)。 - 需要直观的时间格式(如`2023-10-01 12:00:00`)。 - **配置注意**: - MySQL 5.6.5+ 才支持`DEFAULT CURRENT_TIMESTAMP`[^3]。 ```sql -- 建表示例(MySQL 5.6.5+) CREATE TABLE contracts ( id INT PRIMARY KEY, signed_at DATETIME DEFAULT CURRENT_TIMESTAMP ); ``` 3. **性能优先:`BIGINT`(Unix时间戳)** - **优点**: - 整型索引效率**比时间类型高30%+**(B+树排序更快)[^1]。 - 避免时区转换问题(统一存储UTC时间戳)。 - **缺点**: - 需手动维护时间(应用层生成时间戳)。 - 可读性差(需用`FROM_UNIXTIME()`转换)[^2]。 ```sql -- 建表示例(手动维护时间戳) CREATE TABLE logs ( id INT PRIMARY KEY, event_time BIGINT UNSIGNED NOT NULL ); ``` --- #### 三、框架适配与实战技巧 1. **Laravel模型配置** - **`TIMESTAMP`/`DATETIME`**: ```php class User extends Model { protected $casts = [ 'created_at' => 'datetime' // 自动转为Carbon对象 ]; } ``` - **`BIGINT`时间戳**: ```php class Log extends Model { protected $dateFormat = 'U'; // 使用Unix时间戳格式 public $timestamps = false; // 禁用自动维护 } ``` 2. **自动更新陷阱** - `created_at`字段**禁止**使用`ON UPDATE`(仅`updated_at`需要)[^3]。 - 错误示例: ```sql -- ❌ 错误:create_at不应自动更新 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ``` --- #### 四、最终推荐 | **场景** | **推荐类型** | **理由** | |-----------------------|--------------|-----------------------------------------------| | 通用业务系统 | `TIMESTAMP` | 平衡存储、性能、易用性 | | 历史档案/长期数据存储 | `DATETIME` | 避免2038年限制 | | 高频写入/查询优化 | `BIGINT` | 整型索引效率最优[^2] | --- ### 相关问题 1. **如何在MySQL中处理时区问题?** - 使用`TIMESTAMP`类型会自动转换时区,而`DATETIME`和`BIGINT`需手动处理时区转换。 2. **`created_at`字段是否应该建立索引?** - 如果常按时间范围查询(如“最近7天记录”),建议对`created_at`添加索引,`BIGINT`类型索引效率更高。 3. **如何修复`TIMESTAMP`的2038年问题?** - 升级到MySQL 8.0,使用`TIMESTAMP`的6位微秒精度扩展(支持到9999年),或改用`DATETIME`[^3]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值