Those things about MySQL (2) How time type data is stored

Almost every time the database is modeled, there is a problem with the type of data storage.

MySQL storage time typically chooses these four types: datetime, timestamp, int and bigint four ways, exactly what type to use, need to look at the specific business.

We discuss each of these four types separately.

1. DateTime

This type can store 8 bytes, representing data from 1000-9,999. This type can be either a null value or a custom value, and the default initial value is "0000-00-00 00:00:00".

The advantage of storing this type of storage is that the data is readable and does not require a function to be converted.

2, timestamp

This type of storage length is 4 bytes, representing the range: ' 1970-01-01 00:00:01 '-' 2038-01-19 03:14:07 ', can be null, or you can customize the value. It is important to note that when this field is created, when you add "on Update Current_timestamp", the field is automatically changed to the current system time when it inserts a value.

3, int

This type of storage length is 4 bytes, which is typically used to store the Linux timestamp in the form of a storage precision of seconds. Indicates that the maximum is date (' y-m-d h:i:s ', 4294967295) up to 2106-02-07 14:28:15, can be null, but the business logic is designed to use a non-null value whenever possible. It has the advantage of numerical storage, space-saving. But the readability is poor. In addition, if the business often needs to arrange or query by time, try to use the int type.

4, bigint

This type of storage length is 8 bytes, from -2^63 (-9223372036854775808) to 2^63-1 (9223372036854775807) for integer data (all numbers). Ideal for business-accurate time storage with milliseconds of storage accuracy. Please refer to int for advantages and disadvantages.

In addition, the time storage in MySQL has the following types:

Year: The number of bytes is 1, and the value range is "1901--2155".

DATE, the number of bytes is 4, and the value range is "1000-01-01--9999-12-31".

Time, the number of bytes is 3, and the value range is " -838:59:59--838:59:59".

In the actual database modeling process, use less.

In conclusion, we believe that the storage of time needs to be based on specific business scenarios. Generally speaking, the type of int used more, on the one hand query efficiency is high, on the other hand can display time more flexible way, the general language provides conversion functions, such as well-known PHP in the date () function.

Those things about MySQL (2) How time type data is stored