Data types in MySQL

Selection of data types

Select the appropriate data type for the fields in the table :
When a column can select more than one data type, it should take precedence over the number type, followed by the date or binary type,
Finally, the character type. For data types of the same level, it is preferable to select a data type that occupies little space.

Integer type

　　
Floating-point type

　　
Description: Decimal type, 9 digits per 4 bytes, 1 bytes decimal point
For example, a DECIMAL (18,9) requires 9 bytes to be stored.

varchar and char types
· varchar is used to store variable-length strings, occupying only the necessary storage space
· A varchar column with a maximum length of less than 255 takes only one extra byte to record the length of the string
· A varchar column with a maximum length greater than 255 consumes two extra bytes to record the length of the string
· The char type is fixed-length
· string stored in a column of type char will delete the trailing space
· The maximum width of a char type is 255

Date Type
　　 datatime Type
ToYYYY-MM-DD HH:MM:SS[. fraction] Format Store date time
Atetime = Yyyy-mm-dd HH:MM:SS
DateTime (6) = Yyyy-mm-dd HH:MM:SS.fraction
Datatime type is independent of time zone and consumes 8 bytes of storage space
Time rangefrom 1000-01-01 00:00:00 to 9999-12-31 23:59:59
　　 Timestamp type
Stores the number of seconds from Greenwich time January 1, 1970 to the current time
ToYYYY-MM-DD hh:mm:ss[.fraction] format display, taking up 4 bytes
Time range from1970-01-01 to 2038-01-19
Timestamp type display depends on the time zone specified
The value of the timestamp column can be automatically modified when the row data is modified
　　 Date Type and time type
Store a user's birthday with only the date part to store
Advantages of the date type:
1. Consumes fewer bytes than using string, datetime, and int, only 3 bytes are required using the date type
2. Use the date type to make calculations between dates using the time function
　　　　 Date type time range from 1000-01-01 to 9999-12-31
　　　　 Time Type the user stores the temporal data in the format HH:MM:SS
Precautions:
1. Do not use string types to store datetime data
Reason:
· DateTime types are typically smaller than the amount of storage space used by strings
· Date-time types can be used to compare dates when searching for filters
· Date and time types also have rich processing functions that allow for convenient date calculation of date-time types
2. Do not use string types to store datetime data

Data types in MySQL