Summarize the difference between float, double and decimal in MySQL

Mysql Video TutorialThe column summarizes the difference between the three floating point types in MySQL

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

Then these three types in MySQL are floating point types and what is the difference between them? ?

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

Some friends here must ask what is single precision and what is double precision? Let's take a brief look at the following!

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

float single-precision storage float For the point type, it is ==4×8=32-bit length== , so float single-precision floating-point numbers occupy 4 bytes in memory, and are described in 32-bit binary.

Then double double-precision floating-point storage The point type is ==8×8 =64-bit length==, so double double-precision floating-point numbers occupy 8 bytes in memory, and use 64-bit binary to describe and calculate, then 64-bit can get more mantissas!

Mantissa : == is how many digits there are after the decimal point==

So the accuracy here mainly depends on the bits of == mantissa== Number, So according to the IEEE Binary Floating Point Arithmetic Standard to calculate the conclusion:

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

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

Finally, the length of the number of 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 memory is different, single-precision memory occupies 4 bytes, double-precision memory occupies 8 bytes
2. The number of significant digits is different (mantissa ) Single precision has 7 significant digits after the decimal point, and double precision has 16 significant digits after the decimal point
3. The value range is different according to the IEEE standard to calculate!
4. Processing speed in the program Different, generally speaking, the CPU processes single-precision floating-point numbers faster than processing double-precision floating-point numbers

The advantages and disadvantages of double and float:

float single precision

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

Disadvantage: But it will become imprecise when the value is large or small.

double double precision

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

Disadvantages: Double double precision consumes memory, and it is twice as fast as float single precision! , the operation speed of double is much slower than float, because the mantissa of double is more than that of float, so it must be calculated There is overhead!

How to choose the usage scenario of double and float!

First of all: Do not use double precision when you can use single precision to save memory. Speed ​​up the calculation!

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

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

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

==Summary of double and float:==

The decimal point represented by float The number is small, and the number of decimal places that double can represent is more, more accurate! It’s as simple as that and you can choose according to the situation!

What does the length m and d behind double and float represent?

double(m,d) And float(m,d) What do the m and 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 number d after the decimal point

For example: the statement float(7,3) specifies the display The value of the value will not exceed 7 digits, and the decimal point has 3 digits, double is the same

In MySQL, when defining a table field, unsigned and zerofill modifiers can also be used by float, double It is used with the decimal data type, and the effect is the same as the int data type. I won’t say more here!

==Summary:==

In the MySQL statement, the actual definition For table fields,

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

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

==Note:== M>=D!

decimal type

==1. Introducing decimal==

When storing values ​​in the same range, it is usually used more than decimal Less space, float uses 4 bytes for storage, double uses 8 bytes,

and decimal depends on the value of M and D, so decimal uses less space

In actual enterprise-level development, you often encounter fields that need to store the amount (3888.00 yuan), and you need to use the data type decimal at this time.
In the MySQL database, the usage syntax of 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 for a field whose data type is decimal?
For example: decimal(5,2), then this field can store -999.99~999.99, the maximum value is 999.99.
That is to say, D represents the length of the fractional part, and (M-D) represents the length of the integer part.

==3. Storage== [understand]
The data storage form of the decimal type is to store every 9 decimal digits into 4 bytes

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

The number of digits that may be set is not a multiple of 9. The official also gave the following table for comparison:

== What does the table mean, for example: ==

1, the field decimal(18,9), 18-9=9, so the integer part and the decimal The part is 9, and the two sides occupy 4 bytes respectively;
2. The field decimal(20,6), 20-6=14, where 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 the decimal, we 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 |
 +-------+---------------+------+-----+---------+--  -----+
 | 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 recommendation:mysql video tutorial

The above is a detailed summary of the differences between float, double, and decimal in MySQL. For more information, please pay attention to other related articles on 1024programmer.com!

p2 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 recommendation:mysql video tutorial

The above is a detailed summary of the differences between float, double, and decimal in MySQL. For more information, please pay attention to other related articles on 1024programmer.com!