MySQL data type--------floating-point type combat

1. Background

* MySQL supports floating-point types with single-precision type (float), double-precision type (double), and high-precision type (decimal), which are recommended for use with high-precision types (decimal) in digital currency types.

* MySQL floating-point and fixed-point types can be represented by the type name plus (m,d), m represents the total length of the value, D represents the length after the decimal point, and M and D are also known as precision and scale, such as float (7,4) Can be displayed as -999.9999,mysql is rounded when the value is saved, and if 999.00009 is inserted, the result is 999.0001. float and double do not specify precision, the default is the actual precision to display, and decimal when the precision is not specified, the default integer is 10, the decimal number is 0.

2. Storage and range of values required for integer types

Type	Occupy space	Precision	Accuracy
FLOAT	4	Single precision	Accurate to 7 decimal places after the decimal point
DOUBLE	8	Double precision	Accurate to 15 decimal places after the decimal point
DECIMAL	Variable length	High precision	Accurate to 65 decimal places after the decimal point

3. m/g * G (!=|=) in floating-point applications (not necessarily equal to) M

M = 3.1415 G = 2.1542, uniformly accurate to 6 digits after the decimal point

* single precision (float) Applications

* Create float_test table

Mysql> CREATE TABLE float_test (-M float (10,6), G float (10,6)) Engine=innodb charset=utf8mb4; Query OK, 0 rows affected (0.06 sec)

* Insert M and g data

Mysql> INSERT into Float_test SELECT 3.1415, 2.1542; Query OK, 1 row affected (0.02 sec) records:1 duplicates:0 warnings:0mysql> select * from float_test;+----------+-- --------+| m | G |+----------+----------+| 3.141500 | 2.154200 |+----------+----------+1 row in Set (0.00 sec)

* Perform m/g * G operation

Mysql> SELECT m/g * g, M from float_test;+--------------+----------+| m/g * g | M |+--------------+----------+| 3.1414999962 | 3.141500 |+--------------+----------+1 row in Set (0.00 sec)

* Dual precision (double) application

* Create double_test table

Mysql> CREATE TABLE double_test (-M double (10,6), G double (10,6)) Engine=innodb charset=utf8m b4; Query OK, 0 rows affected (0.08 sec)

* Insert M and g data

Mysql> INSERT into Double_test SELECT 3.1415, 2.1542; Query OK, 1 row affected (0.01 sec) records:1 duplicates:0 warnings:0mysql> select * from double_test;+----------+- ---------+| m | G |+----------+----------+| 3.141500 | 2.154200 |+----------+----------+1 row in Set (0.00 sec)

* Perform m/g * G operation

Mysql> SELECT m/g * g, M from double_test;+--------------+----------+| m/g * g | M |+--------------+----------+| 3.1415000000 | 3.141500 |+--------------+----------+1 row in Set (0.01 sec)

* High precision (decimal) Applications

* Create decimal_test table

Mysql> CREATE TABLE decimal_test (-M decimal (10,6), G decimal (10,6)) Engine=innodb Charset=ut F8MB4; Query OK, 0 rows affected (0.05 sec)

* Insert M and g data

Mysql> INSERT into Decimal_test SELECT 3.1415, 2.1542; Query OK, 1 row affected (0.02 sec) records:1 duplicates:0 warnings:0mysql> SELECT * from decimal_test;+----------+ ----------+| m | G |+----------+----------+| 3.141500 | 2.154200 |+----------+----------+1 row in Set (0.00 sec)

* Perform m/g * G operation

Mysql> SELECT m/g * g, M from decimal_test;+--------------------+----------+| m/g * g | M |+--------------------+----------+| 3.1415000000000000 | 3.141500 |+--------------------+----------+1 row in Set (0.00 sec)

4. Floating-point Type Application summary

* Floating-point number if the precision and scale are not written, it will be displayed according to the actual

*  system no error

* Decimal If you do not set the precision and scale, just follow the default (10,0) operation, if the data exceeds the accuracy and scale values, will be an error.

* Accounting, accounting system must be the type of decimal to determine the precise and financial security.

5. Summary

To demand-driven technology, the technology itself does not have a better point, only the division of business.

This article is from the "Sea" blog, be sure to keep this source http://lisea.blog.51cto.com/5491873/1943226

MySQL data type--------floating-point type combat