A focus on web technology after 80 I don’t have to fight smart, I only need to fight those lazy people I will surpass most people! Geek xiaojun @ nuggets first original article personal blog: 👉 cnblogs.com 👈

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

type The size of the Range (signed) Scope (unsigned) use
==float== 4 bytes (-3.402 823 466 E+38, -1.494 369 E+38), 0, (1.494 369 E+38, 3.402 823 466 369 E+38) 0, (1.175 494 369 E-38, 3.402 823 466 E+38) Single-precision floating point value
==double== 8 bytes (-1.797 693 134 862 397 7e +308, -2.22507385507 7e +308), 0, (2.2259385804 7e +308, 1.797 693 134 862 315 7 E+308) 0, (1.797 693 134 862 315 7 E+308) Double – precision floating – point value
decimal For Decimal (M,D), M+2 if M>D otherwise D+2 Depends on the values of M and D Depends on the values of M and D Small numerical

So what’s the difference between these three floating point types in MySQL?

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

I'm sure some of you are asking what is single precision and what is double precision? Let's have a brief understanding of it.

We know that a bytes is 8 bits!

Float single-precision stores floating point types ==4×8= 32-bit length ==, so a float single-precision floating-point number takes up four bytes in memory and is described as a 32-bit binary

So double double memory floating point type is ==8×8 =64 bits of length ==, so double double memory is 8 bytes, and use 64 bits of binary description through calculation, then 64 bits can get more mantras!

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

Therefore, the accuracy here mainly depends on the number of bits in the mantissa == part of ==, so it can be calculated according to IEEE binary floating point arithmetic standard:

The single-precision decimal part of a float can only be accurate to the last six digits, plus the one before the decimal point, which gives a significant number of seven digits

The decimal part is accurate to 15 decimal places, plus one significant digit before the decimal point.

Finally distinguish the length of the number of digits behind the decimal point, the longer the more accurate!

Double and float differ from each other:

  1. The number of bytes in memory is different, single precision memory takes up 4 bytes, double precision memory takes up 8 bytes
  2. Different significant digits (mantissa) single precision significant digits after the decimal point 7, double precision significant digits after the decimal point 16
  3. Value range is different according to IEEE standards to calculate!
  4. Processing speeds vary in programs. In general, the CPU can process single-precision floating-point numbers faster than double-precision floating-point numbers

Pros and cons of double and float:

Float single precision

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

Cons: But when the value is very large or very small, it becomes imprecise.

Double double

A double can have 16 mantissa digits, whereas a float has only 7 mantissa digits

Cons: Double doubles are memory consuming and are twice as accurate as float single! Double is much slower than float because it has more mantissa than float.

How to select double and float usage scenarios!

First of all: don’t use double precision when you can use single precision to save memory and speed up the operation!

Float: Of course, if you need a decimal part and you don’t need much precision, use single-precision float.

Double: because of small digital high precision, so double used for high-speed mathematics and scientific computing, computation of satellite positioning processor type double actually faster than single precision, so that when you need to keep the repeated iteration calculation accuracy, or in the large number of operating value, the double type is the best choice.

Say so much in fact is the number of reserved digits behind the decimal point!

== double and float:==

Float can represent fewer decimal places and double can represent more decimal places. So simple to see the situation to choose!

The length m after double and float, what does d stand for?

Double (m,d) and float(m,d) what does m,d stand for here? Many small partners are unclear! So let me go ahead and explain

Like the integer int(n), these types take additional arguments: a display width m and a number d followed by a decimal point

For example, the float(7,3) statement specifies that no more than seven digits will be displayed, as will three digits after the decimal point and double

In MySQL, the unsigned and Zerofill modifiers can also be used by float, double, and Decimal data types when defining table fields, and have the same effect as int data types.

= = summary: = =

When you actually define a table field in a MySQL statement,

Float (M,D) the number of digits allowed in an unsigned float(M,D) is the number of digits allowed in an unsigned float.

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

== M>=D!

A decimal type

= = 1. Introduce a decimal = =

Storing the same range of values typically uses less space than decimal, with float using 4 bytes and double using 8 bytes,

Whereas Decimal relies on the values of M and D, Decimal uses less space

In real enterprise development, it is common to encounter a field where you need to store an amount ($3888.00), and the data type Decimal is needed. In the MySQL database, the syntax for decimal is: decimal(M,D), where the range of M is 1 65 and the range of D is 0 30, and D cannot be greater than M.

==2. Maximum value ==

What is the maximum/range that can be stored for a field of data type DECIMAL? For example: decimal(5,2), the field can store -999.99 to 999.99, with a maximum value of 999.99. So D is the decimal part, and M minus D is the integer part.

==3. Storage == [understand] The form of data storage for decimal types is that every 9 decimal digits are stored as 4 bytes

Values for DECIMAL columns are stored using a binary format that packs nine DECIMAL digits into 4 bytes.

It is possible to set the number of digits that are not multiples of 9.

Leftover Digits Number of Bytes
0 0
1–2 1
3, 4 2
5–6 3
7–9 4

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

1. Field DECIMAL (18,9), 18-9=9, so that both the integer part and the decimal part are 9, taking up 4 bytes on each side; 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 we usually use decimal when setting decimals!!

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.12 + 9876543.12 9876543.00------------+------------+------------+
2 rows in set (0.01 sec)

mysql> desc temp2;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+| | id float (1, 2) | YES | | NULL | | | id2 | double (10, 2) | YES | | NULL | | | id3 | a decimal (10, 2) | YES | | NULL | | +-------+---------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
Copy the code

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 | | | | | | 4.4 + 3.3-------+-------+
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.15sec) Records: 2 Duplicates: 0 Warnings: 0 mysql>select * from temp2;
+---------+---------+
| id      | id2     |
+---------+---------+1.23500 1.23500 | | | | | | 4.40000 + 3.30000---------+---------+
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)
Copy the code

If you like my article, please 👉 “like” “comment” “follow” 👈 one key three even, everyone’s support is my motivation to stick to it!

If there are any mistakes or inaccuracies in the above content, please leave a comment at 👇 below to point out, or you have better ideas, welcome to exchange and learn together