MYSQL数据库时间字段到底应该用什么类型？是INT,TIMESTAMP还是DATETIME？ 且看三者性能效率比较

最新推荐文章于 2024-08-11 13:40:22 发布
最新推荐文章于 2024-08-11 13:40:22 发布
阅读量6.1k 收藏 14
点赞数 2
分类专栏： 数据库 文章标签： INT TIMESTAMP DATETIME
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/a33130317/article/details/103777956
版权

一、前言

每次新做项目设计数据库的时候，项目组里的几个人总会为了时间字段的设计争论个不停。

在MYSQL中，时间字段可以使用int、timestamp、datetime三种类型来存储，那么这三种类型哪一种用来存储时间性能比较高，效率好呢？

接下来我们就来看一下具体的比较吧!

先立个flag：int存时间戳绝对是无敌的！

二、准备

使用的MYSQL版本号：5.5.19

1. 建表：

-- mysql
CREATE TABLE IF NOT EXISTS `datetime_test` (
  `id` int(11) NOT NULL,
  `d_int` int(11) NOT NULL DEFAULT '0',
  `d_timestamp` timestamp NULL DEFAULT NULL,
  `d_datetime` datetime DEFAULT NULL
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

ALTER TABLE `datetime_test`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1;

2. 插入100万条测试数据：

<?php 
header( 'Content-Type: text/html; charset=UTF-8' );
set_time_limit(300);             // 设置最大执行时间为300秒
 
// 连接数据库
$pdo = new PDO("mysql:host=localhost;dbname=test","root","123"); 

// 循环插入数据
for ($i = 1; $i <= 1000000; $i++) { 
    $pdo->exec("insert into datetime_test(d_int,d_timestamp,d_datetime) 
    values($i,FROM_UNIXTIME($i),FROM_UNIXTIME($i))");
}

3. 取中间的20万条做查询测试：

SELECT FROM_UNIXTIME(400000), FROM_UNIXTIME(600000)
1970-01-05 23:06:40, 1970-01-08 06:40:00

 

三、 开始比较

1. MyISAM引擎， d_int/d_timestamp/d_datetime这三个字段都没有索引

1.1 d_int（int类型）

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_int >400000 AND d_int<600000
-- 查询花费 0.0780 秒

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_int>UNIX_TIMESTAMP('1970-01-05 23:06:40') AND d_int<UNIX_TIMESTAMP('1970-01-08 06:40:00')
-- 查询花费 0.0780 秒

小结: int类型的效率不错。

1.2 d_timestamp(timestamp类型)

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_timestamp>'1970-01-05 23:06:40' AND d_timestamp<'1970-01-08 06:40:00'
-- 查询花费 0.4368 秒

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE UNIX_TIMESTAMP(d_timestamp)>400000 AND UNIX_TIMESTAMP(d_timestamp)<600000
-- 查询花费 0.0780 秒

小结: 对于timestamp类型，使用UNIX_TIMESTAMP内置函数查询效率很高，几乎和int相当；直接和日期比较效率低。

1.3 d_datetime(datetime类型)

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_datetime>'1970-01-05 23:06:40' AND d_datetime<'1970-01-08 06:40:00'
-- 查询花费 0.1370 秒

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE UNIX_TIMESTAMP(d_datetime)>400000 AND UNIX_TIMESTAMP(d_datetime)<600000
-- 查询花费 0.7498 秒

小结: 对于datetime类型，使用UNIX_TIMESTAMP内置函数查询效率很低，不建议；直接和日期比较，效率还行。

 

2. MyISAM引擎， d_int/d_timestamp/d_datetime这三个字段都有索引

2.1 d_int（int类型）

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_int >400000 AND d_int<600000
-- 查询花费 0.3900 秒

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_int>UNIX_TIMESTAMP('1970-01-05 23:06:40') AND d_int<UNIX_TIMESTAMP('1970-01-08 06:40:00')
-- 查询花费 0.3824 秒

小结: 对于int类型，有索引的效率反而低了，飘易的猜测是由于设计的表结构问题，多了索引，反倒多了一个索引查找。

2.2 d_timestamp(timestamp类型)

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_timestamp>'1970-01-05 23:06:40' AND d_timestamp<'1970-01-08 06:40:00'
-- 查询花费 0.5696 秒

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE UNIX_TIMESTAMP(d_timestamp)>400000 AND UNIX_TIMESTAMP(d_timestamp)<600000
-- 查询花费 0.0780 秒

小结: 对于timestamp类型，有没有索引貌似区别不大。

2.3 d_datetime(datetime类型)

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE d_datetime>'1970-01-05 23:06:40' AND d_datetime<'1970-01-08 06:40:00'
-- 查询花费 0.4508 秒

SELECT SQL_NO_CACHE count(id) FROM `datetime_test` WHERE UNIX_TIMESTAMP(d_datetime)>400000 AND UNIX_TIMESTAMP(d_datetime)<600000
-- 查询花费 0.7614 秒

小结: 对于datetime类型，有索引反而效率低了。

 

3. InnoDB引擎， d_int/d_timestamp/d_datetime这三个字段都没有索引

3.1 d_int（int类型）

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_int >400000 AND d_int<600000
-- 查询花费 0.3198 秒

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_int>UNIX_TIMESTAMP('1970-01-05 23:06:40') AND d_int<UNIX_TIMESTAMP('1970-01-08 06:40:00')
-- 查询花费 0.3092 秒

小结: InnoDB引擎的查询效率明细比MyISAM引擎的低，低3倍+。

3.2 d_timestamp(timestamp类型)

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_timestamp>'1970-01-05 23:06:40' AND d_timestamp<'1970-01-08 06:40:00'
-- 查询花费 0.7092 秒

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE UNIX_TIMESTAMP(d_timestamp)>400000 AND UNIX_TIMESTAMP(d_timestamp)<600000
-- 查询花费 0.3160 秒

小结: 对于timestamp类型，使用UNIX_TIMESTAMP内置函数查询效率同样高出直接和日期比较。

3.3 d_datetime(datetime类型)

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_datetime>'1970-01-05 23:06:40' AND d_datetime<'1970-01-08 06:40:00'
-- 查询花费 0.3834 秒

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE UNIX_TIMESTAMP(d_datetime)>400000 AND UNIX_TIMESTAMP(d_datetime)<600000
-- 查询花费 0.9794 秒

小结: 对于datetime类型，直接和日期比较，效率高于UNIX_TIMESTAMP内置函数查询。

 

4. InnoDB引擎， d_int/d_timestamp/d_datetime这三个字段都有索引

4.1 d_int（int类型）

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_int >400000 AND d_int<600000
-- 查询花费 0.0522 秒

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_int>UNIX_TIMESTAMP('1970-01-05 23:06:40') AND d_int<UNIX_TIMESTAMP('1970-01-08 06:40:00')
-- 查询花费 0.0624 秒

小结: InnoDB引擎有了索引之后，性能较MyISAM有大幅提高。

4.2 d_timestamp(timestamp类型)

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_timestamp>'1970-01-05 23:06:40' AND d_timestamp<'1970-01-08 06:40:00'
-- 查询花费 0.1776 秒

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE UNIX_TIMESTAMP(d_timestamp)>400000 AND UNIX_TIMESTAMP(d_timestamp)<600000
-- 查询花费 0.2944 秒

小结: 对于timestamp类型，有了索引，反倒不建议使用MYSQL内置函数UNIX_TIMESTAMP查询了。

4.3 d_datetime(datetime类型)

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE d_datetime>'1970-01-05 23:06:40' AND d_datetime<'1970-01-08 06:40:00'
-- 查询花费 0.0820 秒

SELECT SQL_NO_CACHE count(id) FROM `datetime_test2` WHERE UNIX_TIMESTAMP(d_datetime)>400000 AND UNIX_TIMESTAMP(d_datetime)<600000
-- 查询花费 0.9994 秒

小结: 对于datetime类型，同样有了索引，反倒不建议使用MYSQL内置函数UNIX_TIMESTAMP查询了。

 

四、总结

经过整理后得出：

1. 对于MyISAM引擎，不建立索引的情况下（推荐），效率从高到低：

int > UNIX_TIMESTAMP(timestamp) > datetime（直接和时间比较）>timestamp（直接和时间比较）>UNIX_TIMESTAMP(datetime) 。

2. 对于MyISAM引擎，建立索引的情况下，效率从高到低： 

UNIX_TIMESTAMP(timestamp) > int > datetime（直接和时间比较）>timestamp（直接和时间比较）>UNIX_TIMESTAMP(datetime) 。

3. 对于InnoDB引擎，没有索引的情况下(不建议)，效率从高到低：

int > UNIX_TIMESTAMP(timestamp) > datetime（直接和时间比较） > timestamp（直接和时间比较）> UNIX_TIMESTAMP(datetime)。

4. 对于InnoDB引擎，建立索引的情况下，效率从高到低：

int > datetime（直接和时间比较） > timestamp（直接和时间比较）> UNIX_TIMESTAMP(timestamp) > UNIX_TIMESTAMP(datetime)。

 

总结来说，

对于MyISAM引擎，采用 UNIX_TIMESTAMP(timestamp) 比较；

对于InnoDB引擎，建立索引，采用 int 或 datetime直接时间比较。

老刘pro
关注
专栏目录
mysql 不要用int来存放时间
____
07-03 639
今天跟别人讨论才突然发现了这个严重的问题，不能用int来存放时间，时间应该用datetime，当时为何用int来存放，为了防止时区问题，所以用int，然后起初是研究discuz代码，看到他的所有时间都是用int类型来存放 …… 现在如果使用int类型存放时间，2039年之后的时间无法转换，都是0. mysql> set @tm = '2049-01-02'; Query OK, 0 rows affected (0.01 sec) mysql> set @unix = unix_timesta
警告！别再使用 TIMESTAMP 作为日期字段～
程序员高级码农的博客
02-19 867
日期类型通常就是使用DATETIME和TIMESTAMP两种类型，然而由于类型TIMESTAMP存在性能问题，建议你还是尽可能使用类型DATETIME。我总结一下今天的重点内容：MySQL 5.6 版本开始DATETIME和TIMESTAMP精度支持到毫秒；DATETIME占用 8 个字节，TIMESTAMP占用 4 个字节，依然占用 8 个字节，占用 7 个字节；TIMESTAMP日期存储的上限为，业务用TIMESTAMP存在风险；
时间戳：以不变应万变
最新发布
三余知行
08-11 933
时间戳是计算机系统中处理、记录和计算时间的重要工具，广泛应用于各种编程语言和平台中。在整体工程上对时间戳的处理往往需要综合考虑“数据库服务端”、“ORM 层”和“应用层”的最佳实践，确保在数据库中存储时间以 UTC 格式，并在 ORM 和应用程序层面一致地使用时间。在展示给用户时，适当转换为用户所在的时区，从而最大程度地减少出错机会，确保数据的正确性和用户体验。
MySQL学习日记：在存储时间日期时，我们应该使用哪种类型？
C语言C++学习俱乐部：765860056
03-19 876
构建数据库写程序避免不了使用日期和时间，对于数据库来说，有多种日期时间字段可供选择，如 timestamp 和 datetime 以及使用 int 来存储 unix timestamp。 不仅新手，包括一些有经验的程序员还是比较迷茫，究竟我该用哪种类型来存储日期时间呢？ 那我们就一步一步来分析他们的特点，这样我们根据自己的需求选择合适的字段类型来存储 (优点和缺点是比较出来的 , 跟父母从小喜欢拿邻居小孩子跟自己比一样的) datetime 和 timestamp datetime 更像日历上...
MySQL 保存日期，用哪种数据类型合适？datetime？timestamp？还是 int？
web15286201346的博客
06-08 890
日期算是我们在日常开发中经常用到的数据类型，一般来说一张表都有 createTime 和 updateTime 字段，MySQL 中针对日期也提供了很多种不同的数据类型，如：datetimetimestampint等等。甚至也有人直接将日期存为字符串的。那么到底该用哪种类型来保存日期呢？在这些类型中，首先应该排除掉的就是字符串了，很多新手小伙伴爱用字符串存储日期，但实际上这并不是一个很好的方案。使用字符串存储日期，第一个显而易见的问题就是无法使用 MySQL 中提供的日期函数，这会为很多查询带来不便。例如用
mysql 比较时间_面试官：mysql怎样设置可以跟踪语句各阶段性能开销？
weixin_39602737的博客
12-23 95
概述PROFILE 可以跟踪查询语句各个阶段 Time，IO，CPU，MEMORY 等资源使用情况，比较详细。所以系统一般不会记录太多。启用是全局的，所以每个连接都保持语句的资源使用情况。The SHOW PROFILE and SHOW PROFILES statements display profiling information that indicates resource usage ...
MySQL 数据库如何存储时间
热门推荐
wujizhishui的博客
01-14 1万+
数据库如何存储时间1.切记不要用字符串存储日期2.Datetime 和 Timestamp 之间抉择2.1 DateTime 类型没有时区信息的2.2 DateTime 类型耗费空间更大3 再看 MySQL 日期类型存储空间4.数值型时间戳是更好的选择吗？5.总结 1.切记不要用字符串存储日期 字符串占用的空间更大！ 字符串存储的日期比较效率比较低（逐个字符进行比对），无法用日期相关的 API 进...
MySQL字段的时间类型该如何选择？千万数据下性能提升10%~30%
关注菜菜的后端私房菜，获取更多技术干货~
10-16 1639
在MySQL中时间类型的选择有很多，比如：date、time、year、datetime、timestamp...在某些情况下还会使用整形int、bigint来存储时间戳根据节省空间的原则，当只需要存储年份、日期、时间时，可以使用year、date、time如果需要详细的时间，可以选择datetime、timestamp或者使用整形来存储时间戳以下是不同类型的格式、时间范围、占用空间相关信息类型格式范围空间（字节Byte）dateYYYY-MM-DD1000-01-01to。
mysql datetime timestamp 索引_MySQL时间设计 int timestamp datatime 查询效率性能比较
weixin_34493871的博客
01-18 1685
在数据库设计的时候，我们经常会需要设计时间字段，在MYSQL中，时间字段可以使用int、timestamp、datetime三种类型来存储，那么这三种类型哪一种用来存储时间性能比较高，效率好呢？飘易就这个问题，来一个实践出真知吧。MYSQL版本号：5.5.19建立表：CREATE TABLE IF NOT EXISTS`datetime_test` (`id`int(11) NOT NULL,`d...
关于MySQL 时间类型 datetime、bigint、timestamp，你用哪个？
07-27
### MySQL时间类型详解：datetime、bigint、timestamp 在MySQL中，根据不同的应用场景和需求，可以选择多种方式来存储日期和时间信息。本篇文章将详细介绍三种常用的时间类型：`datetime`、`bigint` 和 `timestamp`...
Debezium系列之：MySQL数据库的字段类型与Kafka消息字段类型的映射关系，处理datetime、timestamp、bigint unsigned、decimal、blob等特殊字段类型
zhengzaifeidelushang的博客
09-22 309
Debezium系列之：MySQL数据库的字段类型与Kafka消息字段类型的映射关系，处理时间字段datetime、timestamp、bigint unsigned、decimal、blob等特殊字段类型
[转载]MySQL将DateTime时间类型格式化
weixin_30475039的博客
06-29 1268
DATE_FORMAT(date,format)根据format字符串安排date值的格式。select from_unixtime(time,'%Y-%m-%d %H:%i:%s');select from_unixtime(now(),'%Y-%m-%d %H:%i:%s');以下说明符可用在 format字符串中：说明符说明%a工作日的缩写名称 (...
MySQL 时间类型 datetime、bigint、timestamp，选哪个？
猫头虎：授渔优于赠鱼，兴趣引领智慧，探索之乐尤显珍贵。商务合作：Libin9iOak，共赴知识与乐趣的探索之旅！
08-31 1252
数据库中可以用datetime、bigint、timestamp来表示时间，那么选择什么类型来存储时间比较合适呢？ 前期数据准备 通过程序往数据库插入50w数据 数据表： CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `time_date` datetime NOT NULL, `time_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
数据库中存时间戳的2种方式
玫瑰与鹿°
06-22 1573
回表查询 先定位主键值，再定位行记录，它的性能较扫一遍索引树更低 怎么避免？ 要避免索引回表，就要保证查询的数据只包含主键和索引字段本身
mysql使用int存时间戳
weixin_40723828的博客
07-19 100
MySQL使用int存时间戳 在数据库设计中，时间戳是一个常见的数据类型，通常用来记录数据的创建、修改或删除时间。在MySQL中，时间戳通常使用DATETIME或TIMESTAMP类型存储。但是，有时候我们可能会遇到一些特殊需求，比如需要将时间戳存储为整数类型。本文将介绍如何在MySQL中使用int类型存储时间戳，并展示相...
MySQL 时间类型用 datetime, timestamp 还是 integer？
m0_74132984的博客
10-11 2668
快照本身是动态生成和回收的，即距离现在越远的快照，留下来得越少（更稀疏的快照意味着相邻快照之间的变更记录会更多，本来是一天一个快照，展示这一天内的变更记录即可，后来变成了一周一个快照，于是需要展示这一周内的变更记录）TIMESTAMP 底层采用 4 个字节存储（2的31次方-1，还记得么），能支持的时间范围比 DATETIME 要小一倍，但它的特点在于，当我们写入时，MySQL会根据当前 server 所在的时区进行转换，将值变成 UTC 时区的时间，再存储。你存进去的是什么时间，读到的就是什么时间。
Mysql存储日期类型用int、datetime还是timestamp?
zhaizhai的博客
04-28 5594
常存储时间用datetime类型，现在很多系统也用int存储时间，它们有什么区别？1）int  (1) 4个字节存储，INT的长度是4个字节，存储空间上比datatime少，int索引存储空间也相对较小，排序和查询效率相对较高一点点（2）可读性极差，无法直观的看到数据，可能让你很恼火2)TIMESTAMP（1）4个字节储存（2）值以UTC格式保存（3）时区转化 ，存储时对当前的时区进行转换，检索时...
MySQL数据库时间类型datetime、bigint、timestamp的查询效率比较
方志朋的博客
07-10 450
以下文章来源方志朋的博客，回复”666“获面试宝典来源：https://juejin.im/post/6844903701094596615数据库中可以用datetime、bigint、t...
timestamp数据类型python
06-08
在Python中，`timestamp`通常指的是`datetime.datetime.timestamp()`方法返回的结果，它表示的是自1970年1月1日（UTC/GMT）以来的秒数。这个数据类型是浮点数，精确到小数点后9位，代表了从纪元（Epoch）开始的总秒数。例如，如果你有一个日期对象，调用其timestamp()方法，你会得到一个表示该时间距离1970-01-01 00:00:00 UTC之间差了多少秒的数值。 Python的标准库中并没有一个名为`timestamp`的数据类型，但你可以使用`datetime`模块中的`datetime`对象和它的timestamp属性来处理这样的时间戳。 ```python import datetime # 创建一个datetime对象 dt = datetime.datetime.now() # 转换为timestamp timestamp = dt.timestamp() print(f"The current timestamp is: {timestamp}") ```
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值