A brief discussion on the differences and summary of the three floating point types of float, double and decimal in MySQL

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

So these three are floating point types in MySQL, what are the differences between them? ?

• float floating point type is used to represent single precision floating point values.
• The double floating point type is used to represent double-precision floating point values.

Some of you must be asking here, what is single precision and what is double precision? Let’s take a brief look at it below!

We know that a byte occupies 8 bits, right?

The length of float single-precision floating point type is 4x8=32 bits, so float single-precision floating point number occupies 4 bytes in memory and is described by 32 bits of binary.

Then the double double-precision floating point type is ==8x8=64 bits in length==, so a double double-precision floating point number takes up 8 bytes in memory and is described using 64 bits of binary. Through calculation, 64 bits 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 the IEEE binary floating point arithmetic standard, we can calculate and conclude:

• The single-precision float decimal part can only be accurate to the last 6 digits, plus the one before the decimal point, that is, the effective digit is 7 digits
• The double-precision decimal part can be accurate to 15 decimal places, plus the one significant 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:

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

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 Disadvantages: But when the value is very large or very small, it will become inaccurate.

Advantages of double precision: Compared with float, double precision is higher, and the mantissa can have 16 bits, while float has only 7 bits of mantissa precision. Disadvantages: double precision consumes memory and is twice as much as float single precision! Double calculation speed is much slower than float, because double mantissa is more than float mantissa, so calculation must be expensive!

How to choose the usage scenarios of double and float!

First of all: don't use double precision when single precision is available to save memory and speed up calculations!
float: Of course, if you need a decimal part and don't require high precision, it's better to choose float single-precision floating point type!
Double: Because of the high precision of decimal places, double precision is used for high-speed mathematical calculations, scientific calculations, satellite positioning calculations, etc. Double precision is actually faster than single precision on processors, so: When you need to maintain the accuracy of calculations for multiple repeated iterations, or when operating on large numbers, double precision is the best choice.
All this is actually a question of how many digits to keep after the decimal point!

==Summary of double and float:==

Float can represent fewer decimal places, while double can represent more decimal places and is more precise! It's that simple, just choose according to your situation!

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

double(m,d) and float(m,d) What do m and d here represent? Many of you are also confused! Let me explain. Like the integer int(n) above, these types also have additional parameters: a display width m and a number of digits after the decimal point d.
For example: the statement float(7,3) specifies that the displayed value will not exceed 7 digits, with 3 digits after the decimal point. The same is true for double. In MySQL, when defining table fields, the unsigned and zerofill modifiers can also be used with float, double, and decimal data types, and the effect is the same as the int data type. I won't go into details here!

==Summary:==

In MySQL statements, when actually defining table fields,
float(M,D) unsigned The M in it represents the number of digits that can be used, and D represents the number of decimal places after the decimal point. Unsigned means that negative numbers are not allowed!
double(M,D) unsigned The M in it 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. Introduction to decimal==
When storing values ​​in the same range, it usually uses less space than decimal, float uses 4 bytes to store, and double uses 8 bytes.
Decimal depends on the values ​​of M and D, so decimal uses less space. In actual enterprise-level development, we often encounter fields that need to store amounts (3888.00 yuan). At this time, we need to use the data type decimal.
In MySQL database, the 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 in a field with a decimal data type?
For example: decimal(5,2), the field can store -999.99~999.99, with a maximum value of 999.99.
That is to say, D represents the length of the decimal part, and (MD) represents the length of the integer part.
==3. Storage== [Understand]
The data storage format of the decimal type is to store each 9 decimal digits 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 provides the following table for comparison:

==What does the table mean? For example: ==
1. Field decimal(18,9), 18-9=9, so both the integer part and the decimal part are 9, and each side occupies 4 bytes;
2. Field decimal(20,6), 20-6=14, where the decimal part is 6, which corresponds to 3 bytes in the table above, and the integer part is 14, 14-9=5, which is 4 bytes plus 3 bytes in the table. So usually when we set decimals, 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 | 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)

This concludes this article on the differences and summary of the three floating-point types of float, double, and decimal in MySQL. For more relevant MySQL float, double, and decimal content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!