## 编程知识 cdmana.com

### The difference of three floating-point types in MySQL database

First, let's look at the storage size and range of each floating-point type , The following table ：

type size Range （ A signed ） Range （ Unsigned ） purpose
==float== 4 bytes (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) 0,(1.175 494 351 E-38,3.402 823 466 E+38) Single precision Floating point numbers
==double== 8 bytes (-1.797 693 134 862 315 7 E+308,-2.225073858507 2014E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) Double precision Floating point numbers
decimal Yes decimal(M,D) , If M>D, by M+2 Otherwise D+2 Depend on M and D Value Depend on M and D Value Small value

that MySQL All three of these are floating point types What's the difference between them ？？

1. float Floating point types are used to represent single precision floating-point values
2. double Floating point types are used to represent double precision floating-point values

There must be some friends here to ask What is single precision ？ What is double precision ？ Let's take a brief look at it !

First , We know one bytes( byte ) Occupy 8 position Right !

float Single precision If you store floating-point types Namely ==4x8=32 The length of bits == , therefore float Single precision floating-point numbers occupy memory 4 Bytes , And use 32 Bit binary to describe

that double Double precision Storing floating-point types is ==8x8 =64 The length of bits ==, therefore double Double precision floating-point numbers occupy memory 8 Bytes , And use 64 Bit binary to describe By calculation 、 that 64 You can get more mantissa !

mantissa

It's the number of digits after the decimal point .

So the precision here mainly depends on the number of digits in the mantissa , So according to IEEE Binary floating point arithmetic standard To come to the conclusion that , as follows :

float A single decimal part can only be accurate to the back 6 position , Add one digit before the decimal point , That is, the significant number is 7 position

double Double precision the decimal part can be accurate to the decimal point 15 position , Add one digit before the decimal point The number of significant digits is 16 position .

Finally, we can distinguish the length of the side digit after the decimal point , The longer, the more accurate !

double and float The difference between them :

The number of bytes occupied in memory is different , Single precision memory takes up 4 Bytes , Double precision memory takes up 8 Bytes

Significant digits are different ( mantissa ) Single precision significant digits after decimal point 7 position , Double precision significant digits after decimal point 16 position

The range of values is different according to IEEE Standard to calculate !

Different processing speed in the program , Generally speaking ,CPU Processing single precision floating-point numbers is faster than processing double precision floating-point numbers

double and float Each other's strengths and weaknesses

float Single precision

advantage : float Single precision is better than on some processors double Double precision is faster and takes only double Double precision half the space

shortcoming : But when it's big or small , It will become imprecise .

double Double precision

advantage : double Follow float Compare , It must be double High precision , The mantissa can have 16 position , and float The precision of mantissa is only 7 position

shortcoming : double Double precision is memory consuming , And is float Twice the single precision ! ,double The speed of computing is faster than float Much slower , because double Mantissa ratio float The mantissa of , So it must have cost to calculate !

How to choose double and float Usage scenarios of

First : Don't use double precision when you can use single precision To save memory , Speed up the calculation !

float: Of course, when you need a decimal part and you don't have high accuracy requirements , choice float Single precision floating point is better !

double: Because of the high precision of decimal places , So double precision is used for high-speed mathematical calculation 、 Scientific Computing 、 In fact, the double precision processor is faster than the single precision processor , therefore : When you need to maintain the accuracy of the calculations over and over again , Or when operating a number with a large value , Double precision is the best choice .

So much is actually about the number of reserved digits after the decimal point !

Summary

float There are few decimal places ,double There are many decimal places to express , More accurate ！ It's that simple It depends on the situation !

double and float The length of the back m,d What does it represent ?

double(m,d) and float(m,d) there m,d What does it represent ？ A lot of friends are not clear ! I'd better go on and explain

It's actually the same as the preceding integer int(n) equally , These types also have additional parameters ： A display width m And a decimal point followed by the number of d

such as : sentence float(7,3) It is stipulated that the value displayed will not exceed 7 Digit number , The decimal point is followed by 3 Digit number 、double It's the same thing

stay MySQL in , When defining table fields , unsigned and zerofill Modifiers can also be float、double and decimal Data types use , And the effect is similar to int Same data type Just like the above, I won't say much about it here !

Summary

stay MySQL In the sentence , When you actually define a table field ,float(M,D) unsigned Medium M Represents the number of digits that can be used ,D It represents the number of decimal places after the decimal point , unsigned Negative numbers are not allowed .

double(M,D) unsigned Medium M Represents the number of digits that can be used ,D It represents the number of decimal places after the decimal point .

Be careful :M>=D!

decimal type

1. Introduce decimal

When storing the same range of values , Often than decimal Use less space ,float Use 4 Byte store ,double Use 8 Bytes , and decimal Depend on M and D Value , therefore decimal Use less space .

In real enterprise development , Often encounter the need to save money （3888.00 element ） Field of , In this case, you need to use the data type decimal. stay MySQL In the database ,decimal The use of grammar is ：decimal(M,D), among ,M The range is 1~65,D The range is 0~30, and D Not greater than M.

2. Maximum

The data type is decimal Field of , The maximum value that can be stored / What's the scope ？ for example ：decimal(5,2), The field can be stored -999.99~999.99, The maximum value is 999.99. in other words D It means the length of the decimal part ,(M-D) Represents the length of the integral part .

3. Storage [ understand ]

decimal The data storage form of type is , Will each 9 The decimal number is stored as 4 Bytes .（ Official explanation ：Values for DECIMAL columns are stored using a binary format that packs nine decimal digits into 4 bytes）.

It's possible that the number of digits set is not 9 Multiple , The official also gives the following table for comparison ：

Leftover Digits Number of Bytes
0 0
1–2 1
3–4 2
5–6 3
7–9 4

What does the form mean , for instance ：

1、 Field decimal(18,9),18-9=9, So the integer part and the decimal part are both 9, The two sides occupy 4 Bytes ;

2、 Field decimal(20,6),20-6=14, The decimal part is 6, It corresponds to the table above 3 Bytes , And the integral part is 14,14-9=5, Namely 4 Bytes plus... In the table 3 Bytes

So we usually set decimals , It's all for decimal type !!

Case study 1

mysql> drop table temp2;
Query OK, 0 rows affected (0.15 sec)

mysql> create table temp2(id float(10,2),id2 double(10,2),id3 decimal(10,2));
Query OK, 0 rows affected (0.18 sec)

mysql> insert into temp2 values(1234567.21, 1234567.21,1234567.21),(9876543.21,

``-> 9876543.12, 9876543.12);``

Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from temp2;
id id2 id3
1234567.25 1234567.21 1234567.21
9876543.00 9876543.12 9876543.12

2 rows in set (0.01 sec)

mysql> desc temp2;
Field Type Null Key Default Extra
id float(10,2) YES NULL
id2 double(10,2) YES NULL
id3 decimal(10,2) YES NULL

3 rows in set (0.01 sec)

Case study 2

mysql> drop table temp2;
Query OK, 0 rows affected (0.16 sec)

mysql> create table temp2(id double,id2 double);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into temp2 values(1.235,1,235);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into temp2 values(1.235,1.235);
Query OK, 1 row affected (0.03 sec)

mysql>

mysql> select * from temp2;
id id2
1.235 1.235

1 row in set (0.00 sec)

mysql> insert into temp2 values(3.3,4.4);
Query OK, 1 row affected (0.09 sec)

mysql> select * from temp2;
id id2
1.235 1.235
3.3 4.4

2 rows in set (0.00 sec)

mysql> select id-id2 from temp2;
id-id2
0
-1.1000000000000005

2 rows in set (0.00 sec)

mysql> alter table temp2 modify id decimal(10,5);
Query OK, 2 rows affected (0.28 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> alter table temp2 modify id2 decimal(10,5);
Query OK, 2 rows affected (0.15 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from temp2;
id id2
1.23500 1.23500
3.30000 4.40000

2 rows in set (0.00 sec)

mysql> select id-id2 from temp2;
id-id2
0.00000
-1.10000

2 rows in set (0.00 sec)

In this paper, the source : Biao Vatican interaction (https://www.biaofun.com/)

Scroll to Top