Mysql存储日期类型用int、timestamp还是datetime?

通常存储时间用datetime类型,现在很多系统也用int存储时间,它们有什么区别？个人更喜欢使用int这样对于日期计算时比较好,下面我们一起来看到底那种会好些.

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-DD HH:MM:SS'格式检索和显示DATETIME值。支持的范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59'

mysql也是这两年才流行,性能越来越来,具体怎么存储看个人习惯和项目需求吧.

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

1. Myisam:MySQLDATETIMEvsTIMESTAMPvsINT测试仪
2. CREATETABLE`test_datetime`(
3. `id`int(10)unsignedNOTNULLAUTO_INCREMENT,
4. `datetime`FIELDTYPENOTNULL,
5. PRIMARYKEY(`id`)
6. )ENGINE=MyISAM;

机型配置

1. kip-locking
2. key_buffer=128M
3. max_allowed_packet=1M
4. table_cache=512
5. sort_buffer_size=2M
6. read_buffer_size=2M
7. read_rnd_buffer_size=8M
8. myisam_sort_buffer_size=8M
9. thread_cache_size=8
10. query_cache_type=0
11. query_cache_size=0
12. thread_concurrency=4

测试

1. DATETIME141111401014369130000000
2. TIMESTAMP13888138871412290000000
3. INT13270129701349690000000

执行mysql

1. mysql>select*fromtest_datetimeintooutfile‘/tmp/test_datetime.sql’;
2. QueryOK,10000000rowsaffected(6.19sec)
4. mysql>select*fromtest_timestampintooutfile‘/tmp/test_timestamp.sql’;
5. QueryOK,10000000rowsaffected(8.75sec)
7. mysql>select*fromtest_intintooutfile‘/tmp/test_int.sql’;
8. QueryOK,10000000rowsaffected(4.29sec)
10. altertabletest_datetimerenametest_int;
11. altertabletest_intaddcolumndatetimeintINTNOTNULL;
12. updatetest_intsetdatetimeint=UNIX_TIMESTAMP(datetime);
13. altertabletest_intdropcolumndatetime;
14. altertabletest_intchangecolumndatetimeintdatetimeintnotnull;
15. select*fromtest_intintooutfile‘/tmp/test_int2.sql’;
16. droptabletest_int;
17. SonowIhaveexactlythesametimestampsfromtheDATETIMEtest,anditwillbepossibletoreusetheoriginalsforTIMESTAMPtestsaswell.
18. mysql>loaddatainfile‘/export/home/ntavares/test_datetime.sql’intotabletest_datetime;
19. QueryOK,10000000rowsaffected(41.52sec)
20. Records:10000000Deleted:0Skipped:0Warnings:0
22. mysql>loaddatainfile‘/export/home/ntavares/test_datetime.sql’intotabletest_timestamp;
23. QueryOK,10000000rowsaffected,44warnings(48.32sec)
24. Records:10000000Deleted:0Skipped:0Warnings:44
26. mysql>loaddatainfile‘/export/home/ntavares/test_int2.sql’intotabletest_int;
27. QueryOK,10000000rowsaffected(37.73sec)
28. Records:10000000Deleted:0Skipped:0Warnings:0
29. Asexpected,sinceINTissimplystoredasiswhiletheothershavetoberecalculated.NoticehowTIMESTAMPstillperformsworse,eventhoughuseshalfofDATETIMEstoragesize.
30. Let’schecktheperformanceoffulltablescan:
31. mysql>SELECTSQL_NO_CACHEcount(id)FROMtest_datetimeWHEREdatetime>‘1970-01-0101:30:00′ANDdatetime<‘1970-01-0101:35:00′;
32. +———–+
33. |count(id)|
34. +———–+
35. |211991|
36. +———–+
37. 1rowinset(3.93sec)
39. mysql>SELECTSQL_NO_CACHEcount(id)FROMtest_timestampWHEREdatetime>‘1970-01-0101:30:00′ANDdatetime<‘1970-01-0101:35:00′;
40. +———–+
41. |count(id)|
42. +———–+
43. |211991|
44. +———–+
45. 1rowinset(9.87sec)
47. mysql>SELECTSQL_NO_CACHEcount(id)FROMtest_intWHEREdatetime>UNIX_TIMESTAMP(’1970-01-0101:30:00′)ANDdatetime<UNIX_TIMESTAMP(’1970-01-0101:35:00′);
48. +———–+
49. |count(id)|
50. +———–+
51. |211991|
52. +———–+
53. 1rowinset(15.12sec)
54. Thenagain,TIMESTAMPperformsworseandtherecalculationsseemedtoimpact,sothenextgoodthingtotestseemedtobewithoutthoserecalculations:findtheequivalentsofthoseUNIX_TIMESTAMP()values,andusetheminstead:
55. mysql>selectUNIX_TIMESTAMP(’1970-01-0101:30:00′)ASlower,UNIX_TIMESTAMP(’1970-01-0101:35:00′)ASbigger;
56. +——-+——–+
57. |lower|bigger|
58. +——-+——–+
59. |1800|2100|
60. +——-+——–+
61. 1rowinset(0.00sec)
63. mysql>SELECTSQL_NO_CACHEcount(id)FROMtest_intWHEREdatetime>1800ANDdatetime<2100;
64. +———–+//开源软件:phpfensi.com
65. |count(id)|
66. +———–+
67. |211991|
68. +———–+
69. 1rowinset(1.94sec)