Data type of MySQL

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:

Data type of MySQL

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

Data type of MySQL

  • Starting from the decimal point, each integer is divided into one group every nine decimal places, and the effect is as follows:

Data type of MySQL

  • 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.

This work adoptsCC agreementReprint must indicate the author and the link of this article

Recommended Today

Programming Xiaobai must understand the network principle

How is the network composed? Why can we easily surf the Internet now?Whether you are a computer major or not, you may always have such questions in your heart!And today we will solve this matter and tell you the real answer! Basic composition of network First, let’s look at this sentence Connect all computers together […]