Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to solve the problem of MySQL storage time type selection", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to solve the problem of MySQL storage time type selection" this article.

Storage time in MySQL usually uses the datetime type, but now many systems also use int to store unix timestamps. What's the difference between them? I would like to summarize as follows:

Int

(1) 4-byte storage, the length of INT is 4 bytes, the storage space is less than datatime, the storage space of int index is also relatively small, and the sorting and query efficiency is a little bit higher.

(2) the readability is so poor that the data cannot be seen intuitively.

TIMESTAMP

(1) 4 bytes of storage

(2) the value is saved in UTC format

(3) time zone conversion, the current time zone is converted when storing, and then back to the current time zone when searching.

(4) the TIMESTAMP value cannot be earlier than 1970 or later than 2037.

Datetime

(1) 8 bytes of storage

(2) has nothing to do with time zone

(3) retrieve and display DATETIME values in YYYY-MM-DD HH:MM:SS' format. The range of support is' 1000-01-01 00 0000'to '9999-12-31 23 purl 59'.

As the performance of Mysql is getting higher and higher, I think it depends on personal habits and project requirements about how to store time.

Share two articles about int vs timestamp vs datetime performance testing

Myisam:MySQL DATETIME vs TIMESTAMP vs INT tester

CREATE TABLE `test_ datetime` (`id` int (10) unsigned NOT NULL AUTO_INCREMENT, `datetime` FIELDTYPE NOT NULL,PRIMARY KEY (`id`)) ENGINE=MyISAM

Model configuration

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

test

DATETIME 14111 14010 14369 130000000

TIMESTAMP 13888 13887 14122 90000000

INT 13270 12970 13496 90000000

Execute 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.29sec) 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 datetimeint 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: 0

Mysql > 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: 44

Mysql > 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-01 01 Fraser 30 id 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 FROM test_timestamp WHERE datetime 30 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-01-01-01-01-01-01-01-01-01-01-01-30 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 01) AS lower, UNIX_TIMESTAMP (' 1970-01-01 01 UNIX_TIMESTAMP) 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

The above is all the contents of the article "how to solve the problem of choosing the type of MySQL storage time". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.