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

 

Detailed description of mysql Data Types

A MySQL database table is a two-dimensional table consisting of one or more data columns.
Each data column has its specific type, which determines how MySQL views the column data. We can store integer values in character-type columns, mySQL treats it as a string.
MySQL has three types of columns: numeric, string, and date/time.
In the big category, the column type is the same as the numerical type, and there are only three types. However, each column type can be subdivided.
The following describes the types of columns in detail.

Data column type of the numeric class
The numeric column types include integer and floating point types.

TINYINT: a positive integer with a very small size of 1 byte, with the symbol:-128 ~ 127, without symbols: 0 ~ 255
SMALLINT: 2-byte small integer, with the symbol:-32768 ~ 32767, without symbols: 0 ~ 65535
MEDIUMINT: A 3-byte integer of medium size, with the symbol-8388608 ~ 8388607, without symbols: 0 ~ 16777215
INT: 4-byte standard integer, with the symbol:-2147483648 ~ 2147483647, without symbols: 0 ~ 4294967295
BIGINT: 8-byte big integer, with the symbol:-9223372036854775808 ~ 9233372036854775807, without symbols: 0 ~ 18446744073709551615
FLOAT: 4-byte Single-precision floating point number. Minimum non-zero value: +-1.175494351E-38; maximum non-zero value: +-3.402823466E + 38
DOUBLE: 8-byte DOUBLE-precision floating point number, minimum non-zero value: +-2.225074255072014e-308, maximum non-zero value: +-1.7976931348623157E + 308
DECIMAL: A floating point number expressed as a string in M + 2 bytes. Its value range is variable, determined by the values of M and D.

MYSQL supports a large number of column types, which can be divided into three types: Number type, date and time type, and string (character) type. This section first provides an overview of available types, summarizes the storage requirements required for each type, and then provides a more detailed description of the Type categories in each type. The overview is intentionally simplified. For more details, refer to the additional information of the close-up column type. For example, you can specify the allowed format for the value.

The column types supported by MySQL are listed below. The following code is used to describe:

M indicates the maximum display size. The maximum display length is 255. D: Applicable to floating point type. Number of digits following the decimal point. The maximum value may be 30, but should not be greater than the M-2.
Square brackets ("[" and "]") Specify the optional type modifier.

NOTE: If ZEROFILL is specified for a column, MySQL automatically adds the UNSIGNED attribute to the column.

Warning you should know that when Subtraction is used in two integer type values, if one is UNSIGNED, the result is also UNSIGNED. See Section 6.3.5 Cast function.

TINYINT [(M)] [UNSIGNED] [ZEROFILL]-128 to 127. The unsigned range is 0 to 255.
BITBOOL: they are synonyms of TINYINT (1.
SMALLINT [(M)] [UNSIGNED] [ZEROFILL] is a small integer. The signed range is-32768 to 32767. The unsigned range is 0 to 65535.
MEDIUMINT [(M)] [UNSIGNED] [ZEROFILL] is a medium-sized integer. The signed range is-8388608 to 8388607. The unsigned range is 0 to 16777215.
INT [(M)] [UNSIGNED] [ZEROFILL] is an integer of the normal size. The signed range is-2147483648 to 2147483647. The unsigned range is 0 to 4294967295.
INTEGER [(M)] [UNSIGNED] [ZEROFILL] INT ...... the remaining full text>
 
Detailed description of mysql Data Types

You need to go to a professional website to check this, or buy a book to systematize it!