Mysql Data type optimization

One, date and time type

MySQL can use many types to hold date and time values.

Such as: Year and date. The minimum time granularity that MySQL can store is seconds (MARIADB supports microsecond-level time types). But MySQL can also use subtle levels of granularity for temporary operations, and we'll show you how to circumvent this storage limit.

Datetime

This type can hold a wide range of values, from 1001 to 9999, with a precision of seconds. It encapsulates the date and time in an integer formatted as YYYYMMMDDHHHMMSS, regardless of the time zone. Use 8 bytes of storage space.

By default, MySQL displays datetime values in a sortable, unambiguous format, such as "2015-10-24 09:57:33". This is the date and time representation method defined by the ANSI standard.

TIMESTAMP

Like its name, the timestamp type holds the number of seconds since January 1, 1970 0 o'clock (Greenwich Mean Time), which is the same as the Unix timestamp. Timestamp uses only 4 bytes of storage space, so it has a much smaller range than DateTime: It can only be stored for 1970-2038 years.

If you store or access data in more than one time zone, the behavior of Timestamp and datetime will be very different.

The value provided by timestamp is related to the time zone, which just retains the date and time of the text representation.

You can also specify the behavior of add and update for timestamp, and the default for the timestamp column is not NULL

In addition to special behavior, you should always use timestamp as much as possible, because it is more efficient than datetime space Sometimes people will store the Unix timestamp as an integer value, but this will not bring any benefit. The format of saving timestamps with integers is often inconvenient, so this is not recommended.

What if you need to store a smaller granularity date and time than the second?

MySQL does not currently provide the appropriate data type, but it can be stored by using its own format: You can use the bigint type to store a micro-level timestamp, or use a double to store the decimal part after the second. Either way, you can, or you can use MARIADB instead of MySQL

Mysql Data type optimization