Summarize the differences between float, double, and decimal floating point types in MySQL

mysql video tutorialThe column summarizes the differences between the three floating point types in MySQL

The storage size and range of each floating point type is planned in the following table:

Type	Size	Range (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 value
==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 value
decimal	For decimal(M,D), if M>D, it is M 2 otherwise it is D 2	Depends on the values ​​​​of M and D	Depends on the values ​​​​of M and D	Decimal value

So these three types in MySQL are all floating point types. What are the differences between them? ?

1. float floating point type is used to represent == single precision floating point == numerical value,
2. double floating point type is used to represent == double precision floating point == Value

<strong>There must be some friends here who want to ask: What is single precision and what is double precision? Let’s take a brief look at it!</strong>

We know that a bytes (byte) occupies 8 bits, right!

If the float single-precision storage floating point type is ==4x8=32-bit length==, so the float single-precision floating-point number occupies 4 bytes in the memory and is described in 32-bit binary.

Then the double-precision floating-point storage type is== 8x8 =64-bit length==, so double-precision floating-point numbers occupy 8 bytes in memory and are described in 64-bit binary. Through calculation, 64-bit can get more mantissas!

Mantissa: == is the number of digits after the decimal point==

So the accuracy here mainly depends on the number of digits in the ==mantissa== part, So according to IEEE Binary floating point number arithmetic standard is used to calculate and conclude:

The single-precision decimal part of float can only be accurate to the next 6 digits, plus one digit before the decimal point, that is, the effective digits are 7 digits

The double-precision decimal part can be accurate to 15 digits after the decimal point, plus one effective digit before the decimal point, which is 16 digits.

Finally, the length of the digits after the decimal point is distinguished. The longer, the more accurate!

The difference between double and float:

1. The number of bytes occupied in the memory is different, single precision memory occupies 4 bytes, double precision memory occupies 8 bytes
2. The number of valid digits is different (mantissa) Single precision is valid after the decimal point 7 digits, 16 significant digits after the double precision decimal point
3. The value range is different and calculated according to IEEE standards!
4. The processing speed in the program is different. Generally speaking, CPU processing Single-precision floating-point numbers are faster than processing double-precision floating-point numbers

The advantages and disadvantages of double and float over each other:

float single precision

Advantages: float single precision is faster than double precision on some processors and only takes up half the space of double double precision

Disadvantages: But when the value is very large or small , it will become inaccurate.

double double precision

Advantages: Compared with double and float, double must have higher precision, and the mantissa can have 16 digits, while float has only 7 digits of mantissa precision

Disadvantages: Double double precision consumes memory, and is twice as fast as float single precision! Double's operation speed is much slower than float, because double has more mantissas than float, so there must be overhead in calculation. !

How to choose the usage scenario of double and float!

First of all: Do not use double precision when single precision is available to save memory and speed up operations!

float: Of course, when you need the decimal part and the accuracy requirements are not high, it is better to choose float single-precision floating point type!

double: Because of the high precision of the decimal place, double precision is used For high-speed mathematical calculations, scientific calculations, satellite positioning calculations, etc., the double-precision type on the processor is actually faster than the single-precision type, so: when you need to maintain the accuracy of calculations for multiple iterations, or when operating numbers with large values When this happens, double precision is the best choice.

To say so much is actually a question of how many digits are reserved after the decimal point!

==Summary of double and float:==

float represents fewer decimal places. Double can represent more decimal places and is more accurate! It’s that simple, just make your own choice depending on the situation!

What do the lengths m and d behind double and float represent?

double(m,d) and float( m,d) What do m,d here represent? Many friends are also unclear! Let me continue to explain

In fact, like the previous integer int(n), these types also have additional parameters: a display width m and a decimal point with The number of d

For example: The statement float(7,3) stipulates that the displayed value will not exceed 7 digits. The same is true for 3 digits after the decimal point and double

In MySQL , when defining table fields, the unsigned and zerofill modifiers can also be used by float, double and decimal data types, and the effect is the same as the int data type. It is the same as above and I won’t go into details here!

== Summary:==

In the MySQL statement, when actually defining the table fields, the M in

float(M,D) unsigned represents the number of digits that can be used, and D represents the decimal point The number of decimal places after, unsigned means that negative numbers are not allowed!

double(M,D) The M in unsigned represents the number of decimal places that can be used, and D represents the number of decimal places after the decimal point

==Note:== M>=D!

decimal type

==1.Introduce decimal==

in the same storage Range values ​​usually use less space than decimal. Float uses 4 bytes for storage and double uses 8 bytes.

And decimal depends on the values ​​of M and D, so decimal uses less space. Space

In actual enterprise-level development, we often encounter fields that need to store the amount (3888.00 yuan). At this time, we need to use the data type decimal. In the MySQL database, the syntax for using decimal is: decimal(M,D), where, The range of M is 165, The range of D is 030, And D cannot be greater than M.

==2. Maximum value==

What is the maximum value/range that can be stored in a field whose data type is decimal? For example: decimal(5,2), this field can store -999.99~999.99, and the maximum value is 999.99. That is to say, D represents the length of the decimal part, and (M-D) represents the length of the integer part.

==3.Storage== [Understand] The data storage format of the decimal type is to store each 9-digit decimal number as 4 bytes

(Official explanation: Values ​​for DECIMAL columns are stored using a binary format that packs nine decimal digits into 4 bytes).

It is possible that the number of digits set is not a multiple of 9. The official also provided the following table for comparison:

##001–21 3–425–637–94

Leftover Digits	Number of Bytes

==What does the table mean? For example: ==

1. Field decimal(18,9), 18-9=9, so the integer part and decimal part are both 9, and both sides occupy 4 bytes respectively; 2. Field decimal(20,6), 20-6=14, the decimal part is 6, which corresponds to the 3 bytes in the above table, and the integer part is 14, 14-9=5, which is 4 bytes Plus the 3 bytes in the table

So usually when we set decimals, we always use the decimal type!!

---

Small case 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)复制代码

small case 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)复制代码

Related free learning recommendations: mysql video tutorial##

The above is the detailed content of Summarize the differences between float, double, and decimal floating point types in MySQL. For more information, please follow other related articles on the PHP Chinese website!