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 ？？

- float Floating point types are used to represent single precision floating-point values
- 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/)