On the difference and summary of float, double and decimal floating point types in MySQL (2024)

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

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

• Float floating point type is used to represent = = single precision floating point = = numeric value,
• The double floating point type is used to represent = = double precision floating point = = numeric value

There must be some friends here to ask, what is single precision and what is double precision? Now let’s have a brief look!

We know that a byte occupies 8 bits, right!

Float single precision storage floating-point type is = = 4×8 = 32-bit length = =, so float single precision floating-point number occupies 4 bytes in memory and is described by 32-bit binary

Then the double double precision storage floating-point type is = = 8×8 = 64 bit length = =, so the double double precision floating-point number occupies 8 bytes in memory and is described in 64 bit binary. Through calculation, more mantissas can be obtained in 64 bits!

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

Therefore, the accuracy here mainly depends on = = mantissa = = the number of bits of the part, so it is calculated according to IEEE binary floating-point arithmetic standard to draw a conclusion:

• The single precision decimal part of float can only be accurate to the last 6 digits, plus one digit before the decimal point, that is, the significant digit is 7 digits
• Double double precision decimal part can be accurate to 15 digits after the decimal point, plus one digit before the decimal point, and the effective digits are 16 digits.
• Finally, the length of the number of digits after the decimal point is distinguished. The longer the length, the more accurate!

The difference between double and float:

• The number of bytes in memory is different. Single precision memory accounts for 4 bytes and double precision memory for 8 bytes
• The number of significant digits is different (mantissa). There are 7 significant digits after the single precision decimal point and 16 significant digits after the double precision decimal point
• The range of values is different} and calculated according to IEEE standard!
• 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 of the space of double double precision

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

Double double

Advantages: compared with float, double must have high precision. The mantissa can have 16 bits, while the mantissa precision of {float is only 7 bits

Disadvantages: double double precision consumes memory and is twice as high as float single precision, The operation speed of double is much slower than that of float, because the mantissa of double is more than that of float ， so it must be expensive to calculate!

How to choose the usage scenario of double and float!

First of all: when single precision can be used, do not use double precision to save memory and speed up operation!

Float: of course, when you need decimal parts and have low requirements for precision, it is 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 calculation, scientific calculation, satellite positioning calculation, etc. the double precision type is actually faster than the single precision type on the processor. Therefore, when you need to maintain the calculation accuracy of repeated iterations for many times, or when the operation value is a large number, double precision type is the best choice.

Saying so much is actually a question of the number of reserved digits after the decimal point!

==Summary double and float:==

Float represents fewer decimal places, and double can represent more decimal places, which is more accurate! It’s that simple. It depends on the situation. Make your own choice!

What are the lengths m and d after double and float?

Double (m, d) and float (m, d) what do m and D stand for here? Many little 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 followed by a decimal point

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

In mysql, when defining table fields, unsigned and zerofill modifiers can also be used by float, double and decimal data types, and the effect is the same as that of int data types!

==Summary:==

When actually defining table fields in MySQL statements,

If D represents the number of decimal places, d after M represents the number of decimal places, and M represents the number of decimal places!

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

==Note: = = M & gt= 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 and double uses 8 bytes,

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

In the actual enterprise development, we often encounter the field that needs to store the amount (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==

What is the maximum value / range that can be stored for a field with a data type of decimal?

For example: decimal (5,2), this field can store – 999.99 ~ 999.99, and the maximum value is 999.99.

In other words, D represents the length of fractional part, and (M-D) represents the length of integer part.

==3. Storage = = [understand]

The data storage form of decimal type is to store every 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).

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

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

1. Field decimal (18,9), 18-9 = 9, so that the integer part and decimal part are 9, and the two sides occupy 4 bytes respectively;

2. Field decimal (20,6), 20-6 = 14, where the decimal part is 6, which corresponds to 3 bytes in the above table, while the integer part is 14, 14-9 = 5, which is 4 bytes plus 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 | 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 is the end of this article about the differences and summary of the three floating-point types of float, double and decimal in MySQL. For more information about MySQL float double decimal, please search the previous articles of script home or continue to browse the relevant articles below. I hope you will support script home in the future!