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

相关广告
  • Mysql存储日期类型用int、timestamp还是datetime? Mysql存储日期类型用int、timestamp还是datetime? Mysql存储日期类型用int、timestamp还是datetime?
相关阅读

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

2019/10/10 17:37:22 | 谷歌SEO算法 | DNS