MySQL Data Types

MySQL Data Types

Note: The following content is applicable to MySQL and later versions.

MySQL has many data types. Selecting the correct data type is crucial for achieving high performance. This article is based on some blogs I have seen on the Internet and the content in the book "High Performance MySQL.

Three Principles:

1. Smaller data types are usually better. Try to use the minimum data type that can properly store data.

2. Simple. Simple data operations usually require less CPU cycles.

3. Avoid NULL as much as possible. If the query contains columns that can be NULL, it is more difficult for MySQL to optimize because columns that can be NULL make the index, index statistics, and values more complex.

1. Integer

MySQL Data Type	Meaning (Signed)
Tinyint (m)	1 byte range (-128 ~ 127)
Smallint (m)	2 bytes range (-32768 ~ 32767)
Mediumint (m)	3 bytes range (-8388608 ~ 8388607)
Int (m)	4 bytes range (-2147483648 ~ 2147483647)
Bigint (m)	8 bytes (+-9.22*10 to the power of 18)

If the value range is unsigned, the maximum value is doubled. For example, the value range of tinyint unsigned is (0 ~ 256 ).
M in int (m) indicates the display width of the SELECT query result set, and does not affect the actual value range.

(MySQL can specify the width for the integer type, for example, Int (11). This is meaningless for most applications: it does not limit the valid range of values, it only specifies the number of characters used by some MySQL interaction tools (such as the MySQL command line client. For storage and computing, Int (1) and Int (20) are the same .)

2. float (float and double)

MySQL Data Type	Description
Float (m, d)	Single-precision floating point 8-bit precision (4 bytes) Total number of m, d decimal places
Double (m, d)	Double-precision floating point 16-bit precision (8 bytes) Total number of m, d decimal places

Set a field to float (5, 3). If a number of 123.45678 is inserted, the actual database stores 123.457, but the total number is still subject to the actual situation, that is, 6 digits.

3. Count

The floating point type stores approximate values in the database, while the fixed point type stores precise values in the database.
The decimal (m, d) Parameter m <65 is the total number, d <30, and d <m is the decimal place.

MySQL5.0 and later versions package the numbers and save them to a binary string (9 digits for every 4 digits ). For example, decimal () Stores nine numbers on both sides of the decimal point, using a total of nine Bytes: the number before the decimal point uses four bytes, And the number after the decimal point uses four bytes, the decimal point occupies 1 byte.

When the floating point type stores values of the same range, it usually uses less space than decimal. Float is stored in 4 bytes. Double occupies 8 bytes.

Because extra space and computing overhead are required, we recommend that you use decimal only when accurately calculating decimal places-for example, to store financial data. However, when the data volume is large, you can consider using bigint instead of decimal.

4. String (char, varchar, _ text)

MySQL Data Type	Description
Char (n)	Fixed Length, up to 255 characters
Varchar (n)	Fixed Length, up to 65535 characters
Tinytext	Variable Length, up to 255 characters
Text	Variable Length, up to 65535 characters
Mediumtext	Variable Length, up to 2 to 24 to 1 Characters
Longtext	Variable Length, up to 2 to 32 to 1 Characters

Char and varchar:
1. char (n) if the number of characters to be saved is less than n, fill it with space, and then remove the space when querying. Therefore, there cannot be spaces at the end of the string stored in char type. varchar is not limited to this.
2. char (n) has a fixed length. char (4) occupies 4 bytes no matter how many characters are stored, varchar is the actual number of characters stored plus 1 byte (n <n> 255). Therefore, varchar (4) takes up 4 bytes to store 3 characters.
3. the string retrieval speed of the char type is faster than that of the varchar type.

Varchar and text:
1. varchar can be specified as n, text cannot be specified, internal storage varchar is the actual number of characters saved + 1 byte (n <n> 255), text is the actual number of characters + 2 bytes.
2. The text type cannot have default values.
3. varchar can directly create an index. text must specify the first number of characters to create an index. Varchar queries are faster than text. text indexes do not seem to work when indexes are created.

5. binary data (_ Blob)

1. _ BLOB and _ text are stored in different ways. _ TEXT is stored in text format, and English is case sensitive. _ Blob is stored in binary format, regardless of case.
2. _ the data stored in BLOB can only be read as a whole.
3. _ TEXT can be used to specify character sets. _ BLO does not need to specify character sets.

6. Date and Time types

MySQL Data Type	Description
Date	Date '2017-12-2'
Time	Time '12: 25: 36'
Datetime	Date and Time '2017-12-2 22:06:44'
Timestamp	Automatic Storage record modification time

If a field is defined as timestamp, the time data in this field will be automatically refreshed when other fields are modified. Therefore, this data type field can store the last modification time of this record.

Attribute of Data Type

MySQL keywords	Description
NULL	The data column can contain NULL values.
NOT NULL	The data column cannot contain NULL values.
DEFAULT	Default Value
PRIMARY KEY	Primary Key
AUTO_INCREMENT	Auto increment, applicable to integer type
UNSIGNED	Unsigned
Character set name	Specify a Character Set