如何解決MySQL存儲(chǔ)時(shí)間類型選擇的問(wèn)題
這篇文章主要為大家展示了“如何解決MySQL存儲(chǔ)時(shí)間類型選擇的問(wèn)題”，內(nèi)容簡(jiǎn)而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“如何解決MySQL存儲(chǔ)時(shí)間類型選擇的問(wèn)題”這篇文章吧。
我們提供的服務(wù)有：網(wǎng)站設(shè)計(jì)制作、網(wǎng)站制作、微信公眾號(hào)開(kāi)發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、五通橋ssl等。為近1000家企事業(yè)單位解決了網(wǎng)站和推廣的問(wèn)題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的五通橋網(wǎng)站制作公司
MySQL中存儲(chǔ)時(shí)間通常會(huì)用datetime類型，但現(xiàn)在很多系統(tǒng)也用int存儲(chǔ)unix時(shí)間戳，它們有什么區(qū)別？本人總結(jié)如下：
int
（1）4個(gè)字節(jié)存儲(chǔ)，INT的長(zhǎng)度是4個(gè)字節(jié)，存儲(chǔ)空間上比datatime少，int索引存儲(chǔ)空間也相對(duì)較小，排序和查詢效率相對(duì)較高一點(diǎn)點(diǎn)
（2）可讀性極差，無(wú)法直觀的看到數(shù)據(jù)
TIMESTAMP
（1）4個(gè)字節(jié)儲(chǔ)存
（2）值以UTC格式保存
（3）時(shí)區(qū)轉(zhuǎn)化 ，存儲(chǔ)時(shí)對(duì)當(dāng)前的時(shí)區(qū)進(jìn)行轉(zhuǎn)換，檢索時(shí)再轉(zhuǎn)換回當(dāng)前的時(shí)區(qū)。
（4）TIMESTAMP值不能早于1970或晚于2037
datetime
（1）8個(gè)字節(jié)儲(chǔ)存
（2）與時(shí)區(qū)無(wú)關(guān)
（3）以'YYYY-MM-DD HH:MM:SS'格式檢索和顯示DATETIME值。支持的范圍為'1000-01-01 00:00:00'到'9999-12-31 23:59:59'
隨著Mysql性能越來(lái)越來(lái)高，個(gè)人覺(jué)得關(guān)于時(shí)間的存儲(chǔ)方式，具體怎么存儲(chǔ)看個(gè)人習(xí)慣和項(xiàng)目需求吧
分享兩篇關(guān)于int vs timestamp vs datetime性能測(cè)試的文章
Myisam:MySQL DATETIME vs TIMESTAMP vs INT 測(cè)試儀
CREATE TABLE `test_datetime` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `datetime` FIELDTYPE NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM;
機(jī)型配置
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
測(cè)試
DATETIME 14111 14010 14369 130000000
TIMESTAMP 13888 13887 14122 90000000
INT 13270 12970 13496 90000000
執(zhí)行mysql
mysql> 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: 0
As 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)Innodb:MySQL DATETIME vs TIMESTAMP vs INT performance and benchmarking with InnoDB
以上是“如何解決MySQL存儲(chǔ)時(shí)間類型選擇的問(wèn)題”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！
當(dāng)前名稱：如何解決MySQL存儲(chǔ)時(shí)間類型選擇的問(wèn)題
鏈接地址：http://www.jiaotiyi.com/article/gdjhjd.html