Difference between Decimal type and Float and Double in MySQL Detailed explanation of of

There are non-standard data types such as float and double in MySQL, and there are also standard data types such as decimal.

The difference is that non-standard types such as float and double store approximate values in DB, while Decimal stores numerical values in the form of strings.
float, double types can store floating-point numbers (that is, decimal types), but float has a disadvantage. When you give the data an integer, it will be processed by you as an integer. So we naturally have problems accessing currency values. My default value is 0.00 and the actual storage is 0. Similarly, I accessed currency is 12.00 and the actual storage is 12.

Fortunately, mysql provides two data types, decimal, which can easily solve the above problem: decimal type is implemented by MySQL in the same type, which is allowed in SQL 92 standard. They are used to store values that have important requirements for accuracy, such as data related to money.

Data definition

float (M, S) M is the full length and S is the length after decimal point. For inaccurate examples, there are many on the network, and Copy is 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----2 decimal places at most, so save can, automatically 4 round 5 into data truncation, but will report waning

12.34 --- OK

1234.5-Because the decimal part is less than 2 digits, it should be filled with 0. So save it should be 1234.50. Therefore, the whole number of digits exceeds 5, so save and report errors.

1.2----0 for the part less than the decimal. Save as per 1.20.

Default state comparison

If the floating-point number does not write longitude and scale, it will be saved according to the actual precision value. If there is precision and scale, it will automatically insert the result after 4 rounding 5, and the system will not report errors; If the fixed point number does not write the precision and scale, it will operate according to the default value decimal (10, 0). If the data exceeds the precision and scale value, the system will report an error.