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

What this article shares to you is about how to choose the time type storage format in MySQL, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Int type:

Storage length: 4 bytes

Scope of representation: date ('Y-m-d date, 4294967295) up to 2106-02-07 14:28:15, if an enterprise has lived so long, it needs to consider the bigint or datetime type in the database.

Whether it is empty or not: it can be empty, but it is recommended that the business logic design setting be non-empty

Storage format: numeric type storage, saving space

Time zone dependent: independent of time zone

Default value: the default value can be set to a certain time according to the business logic.

Advantages

1 the type is simple, and the operation of cpu to deal with this field will be faster, take up small bytes, and save space.

(2) the query speed is fast.

Datetime:

Storage length: 8 bytes

Range of expression: '1000-01-01 00 veg 0012-31 23 purl 59'

Null or not: null values are allowed, values can be customized, and insert and update operations do not automatically modify their values.

Save format: save in actual format (Just stores what you have stored and retrieves the same thing which you have stored.)

Time zone dependent: independent of time zone

Default value: when the default value is not specified, the MySQL will be initialized to '0000-00-0000: 00'

Mysql > CREATE TABLE `tm` (

-> `d1` int (10) unsigned NOT NULL default'0'

-> `d2` timestamp NOT NULL default CURRENT_TIMESTAMP

-> `d3` datetime NOT NULL

-> `d4` timestamp NOT NULL default CURRENT_TIMESTAMP on update current_timestamp

->)

Query OK, 0 rows affected (0.02 sec)

Mysql > insert into tm (d1Magic d4) values (1458612980 recorder now ())

Query OK, 1 row affected, 1 warning (0.00 sec)

Mysql > select * from tm

+-+

| | D1 | D2 | D3 | D4 |

+-+

| | 1458612980 | 2016-03-22 10:16:20 | 2016-03-22 15:21:21 | 2016-03-22 10:16:20 |

| | 1458612980 | 2016-03-22 15:22:17 | 0000-0000: 00:00 | 2016-03-22 15:22:17 |

+-+

2 rows in set (0.00 sec)

The advantage display is intuitive, and there is no need to use function to do conversion.

Timestamp:

Storage length: 4 bytes

Null or not: null values are allowed, but cannot be customized, so null values do not make any sense.

The range is: '1970-01-01 00-00-01-00-00-01-00-01-01-19 03:14:07

Null or not: null values are allowed, values can be customized, and insert and update operations do not automatically modify their values.

Storage format: values are saved in UTC format, that is, digital storage in milliseconds (it stores the number of milliseconds)

Time zone related: related to time, time zone conversion, convert the current time zone when storing, and then convert back to the current time zone when searching.

Default value: can be set to CURRENT_TIMESTAMP (), the current system time.

Gmt_modified timestamp not null default '0000-00-0000: 00 00' on update current_timestamp

Field property with "on update current_timestamp"

1 do not specify the value of the update timestamp field when updating the record, and the database will automatically modify the value of gmt_modified to the time of the current system

2 do not specify the value of the timestamp field and the timestamp field when inserting the record, and the value of this field will automatically become the current system time after insertion.

Compared to the init type that can be automatically updated to the current time of the system, the other has no advantage.

Three summaries

For how to select the model, there are three aspects of performance, storage space, and time range. Here I recommend using int or bigint, datetime types in terms of time range and storage space. Bigint and datetime take up the same amount of space, except that there may be differences in performance. Of course, if the company you work for has a 102-year dream, then I recommend using the datetime type directly. In 2038, although we (post-80s) are estimated to be old-age at home or at the top, it is not recommended to use the timestamp field in order to avoid leaving a hole for later operators.

The above is how to choose the time type storage format in MySQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please 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.