Detailed explanation of data types in MySQL

Numeric type

MySQL’s integer type

##TINYINT 1 0 ~ 2⁸-1 -2⁷ ~ 2⁷-1 Very small integer SMALLINT 2 0 ~ 2¹⁶-1 -2¹⁶ ~ 2¹⁶-1 Small integer MEDIUMINT 3 0 ~ 2²⁴-1 -2²⁴ ~ 2²⁴-1 MEDIUM INTEGER INT 4 0 ~ 2³²-1 -2³² ~ 2³²-1 Standard Integer BIGINT 8 0 ~ 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

Type FLAOT ##DOUBLE 8 ##±1.7976931348623157E 308 Double-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

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)

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

Type

Value range -9999 ~ 9999 ##FLOAT(5, 1) -9999.9 ~ 9999.9 FLOAT(6, 1) -99999.9 ~ 99999.9 MySQL'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)

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:

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

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:

Value range DECLMAL Depends on M and D Depends on M and D ##5 or 6 3 ##7 or 8 The range of M is 1 ~ 65, the range of D is 0 ~ 30, and the value of D cannot exceed M.

#The number of decimal digits contained in the group	The storage space occupied by the unit (bytes )
1 or 2	1
3 or 4	2
4

Recommended tutorial: "

MySQL Tutorial

"

The above is the detailed content of Detailed explanation of data types in MySQL. For more information, please follow other related articles on the PHP Chinese website!