MySQL (1) ---------- data type, mysql ----------

MySQL (1) ---------- data type, mysql ----------
Data Type

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.

 

MySQL supports three types of databases: numeric, string, and date and time.

 

 

The number types can be divided into integer and floating-point types.

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

 

The length is measured in bytes.

Length comparison of various data types

 

 

Name	Length	Usage
TINYINT (M) BIT, BOOL, BOOLEAN	1	If it is an unsigned number, the number from 0 to 255 can be stored; otherwise, the number from-128 to 127 can be stored.
SMALLINT (M)	2	If it is an unsigned number, the number from 0 to 65535 can be stored; otherwise, the number from-32768 to 32767 can be stored.
MEDIUMINT (M)	3	If it is an unsigned number, the number from 0 to 16777215 can be stored; otherwise, the number from-8388608 to 8388607 can be stored.
INT (M) INTEGER (M)	4	If it is an unsigned number, the number from 0 to 4294967295 can be stored; otherwise, the number from-2147483648 to 2147483647 can be stored.
BIGINT (M)	8	If it is an unsigned number, the number from 0 to 18446744073709551615 can be stored; otherwise, the number from-9223372036854775808 to 9223372036854775807 can be stored.
FLOAT (precision)	4 or 8	The precision here is an integer that can go directly to 53. If precision <= 24, it is converted to FLOAT. If precision> 24 and precision <= 53, it is converted to DOUBLE.
FLOAT (M, D)	4	Single-precision floating point number.
DOUBLE (M, D), double precision, REAL	8	Double-precision floating point.
DECIMAL (M, D), DEC, NUMERIC, FIXED	M + 1 or M + 2	Unpackaged floating point number.
DATE	3	Display in YYYY-MM-DD format.
Datetime hh: MM: SS	8	Display in YYYY-MM-DD format.
TIMESTAMP	4	Display in YYYY-MM-DD format.
TIME	3	It is displayed in HH: MM: SS format.
YEAR	1	It is displayed in YYYY format.
CHAR (M)	M	Fixed Length string.
VARCHAR (M)	Maximum M	Variable-length string. M <= 255.
TINYBLOB, TINYTEXT	255 Max	TINYBLOB is case sensitive, while TINYTEXT is not case sensitive.
BLOB, TEXT	Maximum 64 K	BLOB is case sensitive, while TEXT is not case sensitive.
MEDIUMBLOB, MEDIUMTEXT	Maximum 16 M	MEDIUMBLOB is case sensitive, while MEDIUMTEXT is not size sensitive.
LONGBLOB, LONGTEXT	Maximum 4 GB	LONGBLOB is size-sensitive, while LONGTEXT is not size-sensitive.
ENUM (VALUE1 ,....)	1 or 2	Up to 65535 different values can be entered.
SET (VALUE1 ,....)	Up to 8	Up to 64 different values can be entered.