Shulou(Shulou.com)06/01 Report--

This article is about the difference between float, double, and decimal floating-point types in MySQL. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

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

Type size range (signed) range (unsigned) use = = float==4 bytes (- 3.402 823466 Efu38 2014E-308), 0, (1.175 494,351 Elyle 38Met 3.402 823466 351 Elu38) 0, (1.175 494351 Emura 38 3.402 823 466 Emil38) single precision floating point value = double==8 bytes (- 1.797 693 134 862 315 7 Ecolors 308 music 2.225073858507), 0, (2.225073858507) 1.797 693 134,862,3157 Ehammer 308) 0, (2.225 073,858 507 201 4 Emur308) double-precision floating-point values decimal to decimal (M > D), if M > D, otherwise the values of M and D depend on the decimal values of M and D.

So what is the difference between these three floating-point types in MySQL?

The float floating point type is used to represent = = single precision floating point = = numeric value, and the double floating point type is used to represent = = double precision floating point = = numeric value

There must be some friends here who want to ask what is single precision and what is double precision? Let's have a brief understanding of it.

We know that a bytes occupies 8 bits, right?

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

Then the double double-precision storage floating-point type is = = 8x8 = 64-bit length = =, so the double double-precision floating-point number occupies 8 bytes in memory and is described in 64-bit binary. By calculation, then 64-bit can get more Mantissa!

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

So the accuracy here mainly depends on the number of digits of the = = Mantissa = = part, so the conclusion is calculated according to the IEEE binary floating point arithmetic standard:

The single-precision decimal part of float can only be accurate to the last 6 digits, plus one place before the decimal point, that is, the significant number is 7 digits.

The double double-precision decimal part can be accurate to 15 places after the decimal point, plus 16 significant digits before the decimal point.

Finally, it distinguishes the length of the number of decimal places after the decimal point, the longer the more accurate!

The difference between double and float:

The number of bytes occupied in memory is different, single-precision memory accounts for 4 bytes, double-precision memory accounts for 8 bytes of effective digits different (Mantissa) single-precision effective digits after the decimal point 7 digits, double-precision decimal point after the effective number of 16 digits value range is different according to the IEEE standard to calculate! The processing speed is different in the program. Generally speaking, 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 takes up only half the space of double double precision.

Disadvantages: but when the value is large or small, it will become imprecise.

Double double precision

Advantages: compared with float, double must have higher double accuracy, Mantissa can have 16 digits, while float Mantissa accuracy is only 7 digits.

Disadvantages: the double precision of double consumes memory and is twice the single precision of float! 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 you can use single precision, do not use double precision to save memory and speed up the operation speed!

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

Double: because of the high decimal accuracy, double precision is actually faster than single precision for high-speed mathematical calculation, scientific calculation, satellite positioning calculation and other processors, so: when you need to maintain the calculation accuracy of many iterations, or in the operation of large numbers, double precision is the best choice.

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

= = Summary of double and float:==

Float represents a small number of decimal places, double can represent more decimal places, more accurate! It's that simple. It depends on the situation. Choose for yourself.

What does the length mline d after double and float stand for?

What does double (m ~ (m) d) and float (m _ (m)) stand for here? A lot of friends are also unclear! I'd better continue to explain.

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

For example, the statement float (7 < 3) stipulates that the value displayed should not exceed 7 digits, and the decimal point is followed by 3 digits. The same is true of double.

In MySQL, when defining table fields, the unsigned and zerofill modifiers can also be used by the float, double, and decimal data types, and the effect is the same as the int data type as above.

= = Summary:

In the MySQL statement, when the table field is actually defined

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

The M in double (MMagar 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 Typ

= = 1. Introduction to decimal==

When storing the same range of values, it usually uses less space than decimal, float uses 4 bytes of storage and double uses 8 bytes

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

In actual enterprise development, we often encounter fields that need to store the amount of money (3888.00 yuan), so we need to use the data type decimal. In the MySQL database, the syntax for using decimal is: decimal (MMagne D), where the range of M is 165,030 and D cannot be greater than M.

= = 2. Maximum = =

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

= = 3. Storage = [understand] the data storage form of the decimal type is to store every 9-bit 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).

The number of digits that may be set is not a multiple of 9, and the official has also given the following table for comparison:

Leftover DigitsNumber of Bytes001-213,425,637-94

What does a table mean, for example: =

1. Field decimal (18-9), 18-9, so that both the integer part and the decimal part are 9, and the two sides occupy 4 bytes. 2. Field decimal (20-6), 20-6 bytes 14, where the decimal part is 6, corresponding to the 3 bytes in the table above, while the integer part is 14-9 bytes, that is, 4 bytes plus 3 bytes in the table

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

Small case 1mysql > drop table temp2;Query OK, 0 rows affected (0.15 sec) mysql > create table temp2 (id float (10 sec 2), id2 double (10 sec 2), id3 decimal (10 sec 2)); Query OK, 0 rows affected (0.18 sec) mysql > insert into temp2 values (1234567.21), (9876543.21,-> 9876543.12); Query OK, 2 rows affected (0.06 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > select * from temp2 +-+ | id | id2 | id3 | +-+ | 1234567.25 | 1234567.21 | 1234567.21 | | 9876543.00 | 9876543.12 | 9876543.12 | +- -+ 2 rows in set (0.01sec) mysql > desc temp2 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | float (10Magazine 2) | YES | | NULL | | id2 | double (10Magazine 2) | YES | | NULL | | id3 | decimal (10Magin2) | YES | | NULL | | +-+ -+ 3 rows in set (0.01sec) copy code small case 2mysql > 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 sec); ERROR 1136 (21S01): Column count doesn't match value count at row 1mysql > insert into temp2 values (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 sec 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 (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 (10L5) Query OK, 2 rows affected (0.28 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > alter table temp2 modify id2 decimal (10 sec 5); Query OK, 2 rows affected (0.15 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > select * from temp2 +-+-+ | id | id2 | +-+-+ | 1.23500 | 1.23500 | | 3.30000 | 4.40000 | +-+-+ 2 rows in set (sec) mysql > select id-id2 from temp2 +-+ | id-id2 | +-+ | 0.00000 | |-1.10000 | +-+ 2 rows in set (0.00 sec) Thank you for reading! On MySQL float, double, decimal three floating-point types what is the difference to share here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.