Mysql Data Type

1. Integer

Tinyint: 8-bit storage space

Smallint: 16 bit storage space

Mediumint: 24-bit storage space

INT: 32 bit storage space

Bigint: 64-bit storage space

Divided

Signen: an integer that can store positive and negative numbers. For example, the range indicated by tinyint signed is-127 ~ 128

Unsigned: unsigned integer. It can only store positive numbers, but its value is twice that of an integer. For example, tinyint unsigned indicates that the range is 0 ~ 255

The difference between an integer with or without a character only indicates the size of the value, and the storage space and efficiency are the same.

 

2. Real Number

Decimal: a positive number greater than bigint. You can specify the length of an integer and the number of decimal places when saving the value. For example, decimal (18, 9) indicates the total length of 18 digits and 9 digits after the decimal point, 9 bytes in total, 4 bytes before decimal point, 1 byte decimal point, 4 bytes after decimal point, usually used to store accurate data, such as bank accounts

Float: 32 bit, 0 ~ Precision 23

Double: 64 bit, 24 ~ 53 precision

Compared with decimal, double and float consume less storage, but the precision is not high.

 

3. String

3.1 char and varchar

CHAR:

A fixed-length string with a maximum length of 255. MySQL allocates a fixed storage space to store Char. Generally, it is used to update the variable length frequently, columns whose maximum length is similar to the average length

Char deletes the trailing space when obtaining the value, but saves the trailing space when comparing the value. For more information, see

Varchar:

A variable-length string with a maximum length of 65535. MySQL allocates space for the actual length of the string to store varchar. Generally, it is used to update a variable column value without frequent execution, but the maximum length is much greater than the average length of the column, because when the length of the column value changes (for example, a column from "Jack" => "I Want To gank "), the required storage space needs to be re-allocated, resulting in additional performance overhead.

In addition, varchar occupies 1 ~ more than the original length ~ 2 bytes, used to indicate the length of a string. When the length of a string is less than 255, 1 byte is occupied; otherwise, 2 byte is occupied.

When querying, char removes trailing Spaces

When the WHERE clause compares strings, char retains spaces at the end

 

Efficiency:

• In the SELECT statement, the efficiency of char is higher than that of varchar. Because the char length is fixed during the query, the location of the data can be quickly calculated, such as char (10 ), the fifth data address is 5*10, and varchar takes extra time to calculate the data address due to the fixed length.
• The efficiency of char is higher than that of varchar during update, because varchar is not necessarily long. When the Data Length changes, you need to re-allocate the storage space.

 

3.2 blob and text

The only difference between blob and text is that blob stores binary data and does not have character set sorting rules, while text stores character text with character set and sorting rules.

Their sorting rules are sorted by the first several characters of the length specified by max_short_length.

 

4. Date and Time types

Datetime: Save the date in the format of yyyymmddhhmmss, ranging from 1001 ~ 9999, occupied 8 bytes of space

Timestamp: Save the date as a Unix timestamp, ranging from 1970 ~ On February 16, 2038, it occupies 4 bytes of space and is related to the time zone. Its feature is that when a column without a timestamp is inserted, MySQL automatically saves the timestamp as the current time, therefore, it can be used to record the Last Logon Time.

 

5. Bit Set Data Type

Bit: a maximum of 64 bits. It is generally used to save a true/false value, such as bit (1). It occupies 1 bit and is much smaller than the character used for representation.

Set: stores multiple true/false values and can be used for permission management. For more information, see

 

6. Enumeration

Enum: similar to set, but only one value can be inserted. It can generally be used to indicate unique and fixed attributes such as gender (male, female ..)