MySQL存储时间类型选择的问题讲解
MySQL中存储时间通常会用datetime类型，但现在很多系统也用int存储unix时间戳，它们有什么区别？本人总结如下：
int
（1）4个字节存储，INT的长度是4个字节，存储空间上比datatime少，int索引存储空间也相对较小，排序和查询效率相对较高一点点
（2）可读性极差，无法直观的看到数据
TIMESTAMP
（1）4个字节储存
（2）值以UTC格式保存
（3）时区转化，存储时对当前的时区进行转换，检索时再转换回当前的时区。
（4）TIMESTAMP值不能早于1970或晚于2037
datetime
（1）8个字节储存
（2）与时区无关
（3）以'YYYY-MM-DDHH:MM:SS'格式检索和显示DATETIME值。支持的范围为'1000-01-0100:00:00'到'9999-12-3123:59:59'
随着Mysql性能越来越来高，个人觉得关于时间的存储方式，具体怎么存储看个人习惯和项目需求吧
分享两篇关于intvstimestampvsdatetime性能测试的文章
Myisam:MySQLDATETIMEvsTIMESTAMPvsINT测试仪
CREATETABLE`test_datetime`( `id`int(10)unsignedNOTNULLAUTO_INCREMENT, `datetime`FIELDTYPENOTNULL, PRIMARYKEY(`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 1411114010 14369 130000000
TIMESTAMP 13888 13887 14122 90000000
INT 13270 12970 13496 90000000
执行mysql
mysql>select*fromtest_datetimeintooutfile‘/tmp/test_datetime.sql'; QueryOK,10000000rowsaffected(6.19sec) mysql>select*fromtest_timestampintooutfile‘/tmp/test_timestamp.sql'; QueryOK,10000000rowsaffected(8.75sec) mysql>select*fromtest_intintooutfile‘/tmp/test_int.sql'; QueryOK,10000000rowsaffected(4.29sec) altertabletest_datetimerenametest_int; altertabletest_intaddcolumndatetimeintINTNOTNULL; updatetest_intsetdatetimeint=UNIX_TIMESTAMP(datetime); altertabletest_intdropcolumndatetime; altertabletest_intchangecolumndatetimeintdatetimeintnotnull; select*fromtest_intintooutfile‘/tmp/test_int2.sql'; droptabletest_int;
SonowIhaveexactlythesametimestampsfromtheDATETIMEtest,anditwillbepossibletoreusetheoriginalsforTIMESTAMPtestsaswell.
mysql>loaddatainfile‘/export/home/ntavares/test_datetime.sql'intotabletest_datetime;
QueryOK,10000000rowsaffected(41.52sec)
Records:10000000Deleted:0Skipped:0Warnings:0mysql>loaddatainfile‘/export/home/ntavares/test_datetime.sql'intotabletest_timestamp;
QueryOK,10000000rowsaffected,44warnings(48.32sec)
Records:10000000Deleted:0Skipped:0Warnings:44mysql>loaddatainfile‘/export/home/ntavares/test_int2.sql'intotabletest_int;
QueryOK,10000000rowsaffected(37.73sec)
Records:10000000Deleted:0Skipped:0Warnings:0
Asexpected,sinceINTissimplystoredasiswhiletheothershavetoberecalculated.NoticehowTIMESTAMPstillperformsworse,eventhoughuseshalfofDATETIMEstoragesize.
Let'schecktheperformanceoffulltablescan:
mysql>SELECTSQL_NO_CACHEcount(id)FROMtest_datetimeWHEREdatetime>‘1970-01-0101:30:00′ANDdatetime<‘1970-01-0101:35:00′;
+———–+
|count(id)|
+———–+
|211991|
+———–+
1rowinset(3.93sec)
mysql>SELECTSQL_NO_CACHEcount(id)FROMtest_timestampWHEREdatetime>‘1970-01-0101:30:00′ANDdatetime<‘1970-01-0101:35:00′;
+———–+
|count(id)|
+———–+
|211991|
+———–+
1rowinset(9.87sec)
mysql>SELECTSQL_NO_CACHEcount(id)FROMtest_intWHEREdatetime>UNIX_TIMESTAMP('1970-01-0101:30:00′)ANDdatetime
Thenagain,TIMESTAMPperformsworseandtherecalculationsseemedtoimpact,sothenextgoodthingtotestseemedtobewithoutthoserecalculations:findtheequivalentsofthoseUNIX_TIMESTAMP()values,andusetheminstead:
mysql>selectUNIX_TIMESTAMP('1970-01-0101:30:00′)ASlower,UNIX_TIMESTAMP('1970-01-0101:35:00′)ASbigger;
+——-+——–+
|lower|bigger|
+——-+——–+
|1800|2100|
+——-+——–+
1rowinset(0.00sec)
mysql>SELECTSQL_NO_CACHEcount(id)FROMtest_intWHEREdatetime>1800ANDdatetime<2100;
+———–+
|count(id)|
+———–+
|211991|
+———–+
1rowinset(1.94sec)
Innodb:MySQLDATETIMEvsTIMESTAMPvsINTperformanceandbenchmarkingwithInnoDB
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对毛票票的支持。如果你想了解更多相关内容请查看下面相关链接