Time：2021-6-13

# value type

## Integer type of MySQL

type Storage space occupied (unit) The value range without sign There is a consistent range of values definition
TINYINT 1 0 ~ 2⁸-1 -2⁷ ~ 2⁷-1 A 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

## Floating point type of MySQL

type Storage space occupied (unit) Absolute minimum non-zero value Absolute value maximum non-zero value definition
FLAOT 4 ±1.175494351E-38 ±3.402823466E+38 Single-precision floating-point
DOUBLE 8 ±2.2250738585072014E-308 ±1.7976931348623157E+308 Double precision floating point number

Taking the single precision floating-point type float as an example, the structure of its four bytes is as follows: ### Set the maximum number of digits and decimal places

When defining a floating-point type, you can follow two parameters after float or double, as follows

``````FLOAT(M, D)
DOUBLE(M, D)``````
• M is the number of decimal significant digits that the decimal needs at most
• D is the number of decimal digits after the decimal point of the decimal
type Value range
FLOAT(4, 0) -9999 ~ 9999
FLOAT(4, 2) -99.96 ~ 99.99
FLOAT(4, 1) -999.9 ~ 999.9
FLOAT(5, 1) -9999.9 ~ 9999.9
FLOAT(6, 1) -99999.9 ~ 99999.9
• When D is the same, 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.

## Fixed point type of MySQL

• Because it may be inaccurate to use floating-point numbers to represent decimals, in some cases we must ensure that decimals are accurate, so MySQL proposes a data type called fixed-point number, which is also a way to store decimals.
type Storage space occupied (in bytes) Value range
DECLMAL It depends on M and D It depends on M and D

### Storage space

Take declmal (12,4) as an example

• First, determine that the maximum number of decimal digits to be stored for the integer on the left side of the decimal point is 12, and the number of decimal digits to be stored for the integer on the right side of the decimal point is 4, as shown in the figure • Starting from the decimal point, each integer is divided into one group every nine decimal places, and the effect is as follows: • The decimal digits in each group are converted into binary digits for storage. According to the number of decimal digits in the group, the required storage space is different. See the following table for details:
The number of decimal digits contained in the group Unit of storage space occupied (bytes)
1 or 2 1
3 or 4 2
5 or 6 3
7 or 8 4
• The range of M is 1 ~ 65, the range of D is 0 ~ 30, and the value of D cannot exceed M.