Detailed explanation of data types in MySQL-Mysql Tutorial-php.cn

Detailed explanation of data types in MySQL

Guanhui

Release： 2020-05-08 11:17:31

forward

2999 people have browsed it

Numeric type

MySQL’s integer type

##TINYINT10 ~ 2⁸-1-2⁷ ~ 2⁷-1Very small integerSMALLINT20 ~ 2¹⁶-1-2¹⁶ ~ 2¹⁶-1Small integerMEDIUMINT30 ~ 2²⁴-1-2²⁴ ~ 2²⁴-1MEDIUM INTEGERINT40 ~ 2³²-1-2³² ~ 2³²-1Standard Integer BIGINT80 ~ 2⁶⁴-1-2⁶⁴ ~ 2⁶⁴-1 Large integer

Type	occupied Storage space (unit)	Unsigned value range	There is a matching value range	Definition

##MySQL’s floating point type

TypeFLAOT##DOUBLE8##±1.7976931348623157E 308Double-precision floating-point number

Occupied storage space (unit)	Minimum absolute value non-0 value	Maximum absolute value non-0 value	Definition
4	±1.175494351E-38	±3.402823466E 38	Single precision floating point number
##±2.2250738585072014E-308

##The single-precision floating-point number type FLOAT is For example, its four-byte structure is as follows:

Set the maximum number of digits and decimal places

Detailed explanation of data types in MySQL In When defining the floating-point number type, you can also follow FLOAT or DOUBLE with two parameters, as follows

FLOAT(M, D)
DOUBLE(M, D)

Copy after login

M represents the maximum number of significant decimal digits required for the decimal D represents the decimal The number of decimal digits after the decimal point

Type

Value range-9999 ~ 9999##FLOAT(5, 1)-9999.9 ~ 9999.9FLOAT(6, 1)-99999.9 ~ 99999.9MySQL's fixed-point number type

	FLOAT(4, 0)
FLOAT(4, 2)	-99.96 ~ 99.99
FLOAT(4, 1)	-999.9 ~ 999.9


In the same situation as D , the larger M is, the larger the value range of this type is; when M is the same, the larger D is, the smaller the value range of this type is.	The value range of M is 1~255, the value range of D is 0~30, and the value of D must not be greater than M. M and D are optional. If we omit them, their values are stored according to the maximum value supported by the machine.

Because using floating point numbers to represent decimals may be inaccurate, in some cases we must ensure that the decimal is accurate, so MySQL proposes a data type called fixed-point number, which is also a way to store decimals.

Type

Storage space occupied (unit: bytes)

Value rangeDECLMALDepends on M and DDepends on M and D

Storage space

Take DECLMAL (12, 4) as an example

First determine that the maximum number of decimal digits that need to be stored for the integer to the left of the decimal point is 12, and the decimal point The number of decimal digits that need to be stored in the integer on the right is 4, as shown in the figure:

Detailed explanation of data types in MySQL

Starting from the decimal point position, each integer is divided into 1 every 9 decimal digits Group, the effect is like this:

Detailed explanation of data types in MySQL

For each decimal number in the group, convert it into a binary number for storage. Depending on the number of decimal digits contained in the group, The required storage space size is also different, see the following table for details: