如何解决MySQL存储时间类型选择问题

MySQL中通常使用datetime类型来存储时间，但是现在很多系统也使用int来存储unix时间戳。它们之间有什么区别？我的总结如下：

int

（1）4字节存储，INT的长度为4字节，存储空间小于datatime ，int索引存储空间也比较小，排序和查询效率比较高

(2)可读性极差，无法直观地看到数据

TIMESTAMP

(1)4字节存储

(2)值以UTC格式保存

(3)时区转换，转换当前时间存储时输入时区，检索时转换回当前时区。

(4) TIMESTAMP 值不能早于 1970 年或晚于 2037 年

日期时间

(1 ) 存储 8 字节

(2) 与时区无关

(3) 以“YYYY-MM-DD HH:MM:SS”检索格式化并显示 DATETIME 值。支持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'

随着Mysql的性能越来越高，我个人感觉时间的存储方式，如何存储取决于个人习惯和项目需求

分享两篇关于int vs timestamp vs datetime性能测试的文章

Myisam：MySQL DATETIME vs TIMESTAMP vs INT测试工具

创建表 `test_datetime` (`id` int(10) unsigned NOT NULL AUTO_INCRMENT,`datetime` FIELDTYPE NOT NULL,PRIMARY KEY (`id`)) ENGINE=MyISAM;

模型配置

kip-locking

key_buffer = 128M

max_allowed_packet = 1M

table_cache = 512

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

myisam_sort_buffer_size = 8M

thread_cache_size = 8

query_cache_type = 0

query_cache_size = 0

thread_concurrency = 4

测试

DATETIME 14111 14010 14369 130000000
TIMESTAMP 13888 13887 14122 90000000
INT 13270 12970 13496 90000000

执行mysql

mysql> select * from test_datetime intooutfile '/tmp/test_datetime.sql';查询正常，10000000行受影响（6.19秒）mysql> select * from test_timestamp into outfile '/tmp/ test_timestamp.sql';查询正常，影响10000000行（8.75秒）mysql> select * from test_int into outfile '/tmp/test_int.sql';查询正常，影响10000000行（4.29秒）alter table test_datetime rename test_int;alter table test_int 添加列 datetimeint INT NOT NULL;更新 test_int set datetimeint = UNIX_TIMESTAMP( datetime);更改表 test_int drop列datetime;alter table test_int更改列datetimeint datetime int not null;select * from test_int into outfile '/tmp/test_int2.sql';drop table test_int;

所以现在我有完全相同的时间戳DATETIME 测试，并且也可以重用原始数据进行 TIMESTAMP 测试。

< p>mysql> 将文件 '/export/home/ntavares/test_datetime.sql' 中的数据加载到表 test_datetime 中；
查询正常，受影响的 10000000 行（41.52 秒）
记录：10000000 删除：0 跳过：0 警告：0

mysql> load data infile '/export/home/ntavares/ test_datetime.sql' 进入表 test_timestamp;
查询正常，受影响 10000000 行，44 条警告（48.32 秒）
记录：10000000 删除：0 跳过：0 警告：44

mysql>将文件“/export/home/ntavares/test_int2.sql”中的数据加载到表 test_int;
查询正常，受影响的 10000000 行（37.73 秒）
记录：10000000 删除：0 跳过：0 警告：0< /p>

正如预期的那样，因为 INT 只是按原样存储，而其他则必须重新计算。请注意，即使使用 DATETIME 存储大小的一半，TIMESTAMP 的性能仍然较差。

让我们检查全表扫描的性能：

mysql> SELECT SQL_NO_CACHE count(id) FROM test_datetime WHERE datetime > '1970-01-01 01:30:00' AND datetime < '1970-01-01 01:35:00';+————–+|计数(id) |+————–+| 211991 |+ ————–+ 集合中的 1 行（3.93 秒）mysql> SELECT SQL_NO_CACHE count(id) FROM test_timestamp WHERE datetime > '1970-01-01 01:30:00' AND datetime < '1970-01- 01 01:35 :00′;+————–+|计数(id) |+————–+| 211991 |+————–+集合中的1行（9.87秒）mysql> SELECT SQL_NO_CACHE count(id) FROM test_int WHERE datetime > UNIX_TIMESTAMP('1970-01-01 01:30:00′) AND datetime < UNIX_TIMESTAMP( '1970-01-01 01:35:00');+————–+|计数(id) |+————–+| 211991 |+————–+集合中的 1 行（15.12 秒）

然后，TIMESTAMP 表现更差，重新计算似乎会产生影响，因此下一个要测试的好东西似乎是没有 t软管重新计算：找到这些 UNIX_TIMESTAMP() 值的等效值，并使用它们：

mysql> select UNIX_TIMESTAMP('1970-01-01 01:30:00′ ) AS 较小，UNIX_TIMESTAMP('1970-01-01 01:35:00′) AS 较大;+——-+——–+|降低|更大 |+——-+——–+| 1800 | 1800 2100 |+——-+——–+集合中的1行（0.00秒）mysql> SELECT SQL_NO_CACHE count(id) FROM test_int WHERE datetime > 1800 AND datetime < 2100;+————–+|计数(id) |+————–+| 211991 |+————–+1 行集合（1.94 秒）

Innodb：MySQL DATETIME、TIMESTAMP、INT 性能以及 InnoDB 基准测试

The以上就是《如何解决MySQL存储时间类型选择问题》本文的全部内容，感谢您的阅读！相信大家都有一定的了解，希望分享的内容对大家有所帮助。如果您想了解更多知识，请关注行业资讯频道！