The difference between a DECIMAL FLOAT double in MySQL

There are non-standard data types such as float,double in MySQL, as well as standard data types such as decimal. The difference is that non-standard types, such as float,double, hold approximate values in db, while decimal saves values as strings.
The float,double type can be a floating-point number (that is, a decimal type), but float has a downside, and when the data you give is an integer, it is treated as an integer. So we have a natural problem in accessing the currency value, my default value is: 0.00 and the actual storage is 0, the same I access the currency is 12.00, the actual storage is 12.
Fortunately MySQL offers two data types: decimal, which can easily solve the problem: the decimal type is implemented by MySQL in the same type, which is allowed in the SQL92 standard. They are used to hold values that have important requirements for accurate precision, such as money-related data.

Data Definitionfloat (m,s) M is full length and S is the length after the decimal point. For inaccurate examples , many on the Web, copy as follows:mysql> CREATE TABLE T1 (C1 float (10,2), C3decimal (10,2));Query OK, 0 rows affected (0.02 sec)mysql> INSERT INTO T1 values (9876543.21, 9876543.12); Query OK, 1 row Affected (0.00 sec)mysql> SELECT * from T1; +----------------+-----------------+| c1 | C3 |+----------------+-----------------+| 9876543.00 | 9876543.12 |+----------------+------------------+2 rows in Set (0.00 sec)Another example: DECIMAL (5,2)

mysql> CREATE table T1 (id1 float (5,2) default Null,id2 double (5,2) default NULL,
ID3 decimal (5,2) default null);

mysql> INSERT INTO T1 values (1.2345,1.2345,1.2345);
Query OK, 1 row affected, 1 warning (0.04 sec)

Mysql> Show warnings;
+-------+------+------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------+
| Note | 1265 | Data truncated for column ' ID3 ' at row 1 |
+-------+------+------------------------------------------+
1 row in Set (0.00 sec)

 1.2345---A maximum of 2 digits after the decimal point, so save can, auto-rounding data truncation, but will be reported waning, but float and double will not report a warning12.34---OK1234.5---Because the decimal part is less than 2 bits, 0 is to be filled. So save should be 1234.50. So the entire number of digits exceeds 5 and will be stored as 999.99. 1.2---The fractional part is 0. Save As 1.20.  Default state comparisonfloating-point number if not write precision and scale, will be in accordance with the actual accuracy of the value of the save, if there is precision and scale, will automatically be rounded after the results are inserted, the system will not error; If you do not write precision and scale, the default value of decimal (10,0), if the data exceeds the precision and scale values, The system will error. Second Post MySQL data type double and decimal differences detailed

A real number is one with a fractional part. However, they are not just for storing fractional parts, they can also use declmal to store integers larger than bigint. MySQL supports both exact types and imprecise types. The float and DOUBLE types support approximate calculations using standard floating-point operations. If you need to know how floating-point arithmetic is calculated, you need the specific implementation of the floating-point number of the platform used by the Institute.

The decimal type is used to store exact decimals. In MySQL 5.0 and later versions, the DECIMAL type supports accurate calculations. MySQL 4.1 and earlier use floating-point arithmetic for deciaml calculations, which can cause some strange results due to the loss of precision. In these versions of MySQL, Declmal is just a "storage type".

Because the CPU does not support direct computation of the declmal, MySQL server itself implements a high-precision calculation of DECIMAL in MySQL 5.0 and later versions. In contrast, the CPU directly supports native floating-point calculations, so floating-point operations are significantly faster.

Both floating-point and DECIMAL types can specify precision. For the decimal column, you can specify the maximum number of digits allowed before and after the decimal point. This affects the space consumption of the column. MySQL 5.0 and later save fixed-point numbers internally as binary format: The fixed-point number is divided into the front of the decimal point and the two parts of the decimal point, and each of them allocates 4 bytes, which can represent up to 9 digits (nine digits). That is, both decimal (6,3) and decimal (18,9) will occupy 9 bytes (the decimal point itself is one byte). If there are more numbers out there, the required bytes are stored as follows:

leftover Digits	number of Bytes
0	Span style= "color: #ff0000; font-size:15px; " > 0
1–2	1
3–4	2
JTG 5–6	3
---------"	4

The Declmal type in MySQL 5.0 and later versions allows up to 65 digits. In earlier versions of MySQL, this limit was 254 digits and was saved as an uncompressed string (one byte per digit). However, these (early) versions do not actually use such a large number in calculations, because DECIMAL is just a storage format: Declmal is converted to a DOUBLE type in the calculation.

There are several ways to specify the precision required for floating-point columns, which allows MySQL to quietly select different data types, or to trade-offs the values when stored. These precision definitions are non-standard, so we recommend specifying only the data type and not specifying the precision.

Floating-point types typically use less space than DECIMAL when they store values of the same range. FLOAT uses 4 bytes of storage. Double takes 8 bytes, which has a higher precision and a larger range than float. As with the integer type, only the storage type Imysql can be selected using DOUBLE as the internal floating-point calculation type.

Because additional space and computational overhead are required, you should try to use decimal one by one, for example, to store financial data only when the decimal is accurately calculated. However, when the data volume is relatively large, consider using BIGINT instead of decimal, and the currency units that need to be stored are multiplied by the number of decimal places. Assuming that you want to store the financial data exactly one out of 10,000 points, you can multiply all the amounts by 1 million, and then store the results in bigint, which avoids the problem of inaccurate floating-point storage computations and the high cost of DECIMAL precision calculations.

Third article, official documentation

Https://dev.mysql.com/doc/refman/5.7/en/precision-math-decimal-characteristics.html

The difference between a DECIMAL FLOAT double in MySQL