Mysql儲存日期類型用int、timestamp還是datetime?

標籤：

 通常儲存時間用datetime類型，現在很多系統也用int儲存時間，它們有什麼區別？個人更喜歡使用int這樣對於日期計算時比較好哦，下面我們一起來看到底那種會好些。int（1）.4個位元組儲存，INT的長度是4個位元組，儲存空間上比datatime少，int索引儲存空間也相對較小，排序和查詢效率相對較高一點點（2）可讀性極差，無法直觀的看到資料，可能讓你很惱火TIMESTAMP（1）4個位元組儲存（2）值以UTC格式儲存（3）時區轉化 ，儲存時對當前的時區進行轉換，檢索時再轉換回當前的時區。（4）TIMESTAMP值不能早於1970或晚於2037datetime（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 測試儀CREATE TABLE `test_datetime` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`datetime` FIELDTYPE NOT NULL,PRIMARY KEY (`id`)) ENGINE=MyISAM;機型配置kip-lockingkey_buffer = 128Mmax_allowed_packet = 1Mtable_cache = 512sort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 8Mmyisam_sort_buffer_size = 8Mthread_cache_size = 8query_cache_type = 0query_cache_size = 0thread_concurrency = 4測試DATETIME   14111 14010        14369     130000000TIMESTAMP  13888        13887        14122     90000000INT        13270        12970        13496     90000000執行mysqlmysql> select * from test_datetime into outfile ‘/tmp/test_datetime.sql’;Query OK, 10000000 rows affected (6.19 sec)mysql> select * from test_timestamp into outfile ‘/tmp/test_timestamp.sql’;Query OK, 10000000 rows affected (8.75 sec)mysql> select * from test_int into outfile ‘/tmp/test_int.sql’;Query OK, 10000000 rows affected (4.29 sec)alter table test_datetime rename test_int;alter table test_int add column datetimeint INT NOT NULL;update test_int set datetimeint = UNIX_TIMESTAMP(datetime);alter table test_int drop column datetime;alter table test_int change column datetimeint datetime int not null;select * from test_int into outfile ‘/tmp/test_int2.sql’;drop table test_int;So now I have exactly the same timestamps from the DATETIME test, and it will be possible to reuse the originals for TIMESTAMP tests as well.mysql> load data infile ‘/export/home/ntavares/test_datetime.sql’ into table test_datetime;Query OK, 10000000 rows affected (41.52 sec)Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0mysql> load data infile ‘/export/home/ntavares/test_datetime.sql’ into table test_timestamp;Query OK, 10000000 rows affected, 44 warnings (48.32 sec)Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 44mysql> load data infile ‘/export/home/ntavares/test_int2.sql’ into table test_int;Query OK, 10000000 rows affected (37.73 sec)Records: 10000000 Deleted: 0 Skipped: 0 Warnings: 0As expected, since INT is simply stored as is while the others have to be recalculated. Notice how TIMESTAMP still performs worse, even though uses half of DATETIME storage size.Let’s check the performance of full table scan: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′;+———–+| count(id) |+———–+|    211991 |+———–+1 row in set (3.93 sec)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′;+———–+| count(id) |+———–+|   211991 |+———–+1 row in set (9.87 sec)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′);+———–+| count(id) |+———–+|    211991 |+———–+1 row in set (15.12 sec)Then again, TIMESTAMP performs worse and the recalculations seemed to impact, so the next good thing to test seemed to be without those recalculations: find the equivalents of those UNIX_TIMESTAMP() values, and use them instead:mysql> select UNIX_TIMESTAMP(’1970-01-01 01:30:00′) AS lower, UNIX_TIMESTAMP(’1970-01-01 01:35:00′) AS bigger;+——-+——–+| lower | bigger |+——-+——–+|  1800 |   2100 |+——-+——–+1 row in set (0.00 sec)mysql> SELECT SQL_NO_CACHE count(id) FROM test_int WHERE datetime > 1800 AND datetime < 2100;+———–+| count(id) |+———–+|    211991 |+———–+1 row in set (1.94 sec)

 

Mysql儲存日期類型用int、timestamp還是datetime?