MySQL | sum float类型数据时，结果显示很多位小数

问题描述

表money列最多只有2位小数，但是求和时显示出很多位小数

mysql> select * from receive_red_packets limit 5;
+--------------+---------------------+------+-------+
| receive_date | receive_datetime    | uid  | money |
+--------------+---------------------+------+-------+
| 2019-03-01   | 2019-03-01 10:15:00 | 1    |  1.05 |
| 2019-03-01   | 2019-03-01 13:15:00 | 1    |   2.3 |
| 2019-03-01   | 2019-03-01 11:15:00 | 2    |   0.8 |
| 2019-03-01   | 2019-03-01 17:15:00 | 2    |  0.89 |
| 2019-03-01   | 2019-03-01 14:15:00 | 3    |  2.12 |
+--------------+---------------------+------+-------+
5 rows in set (0.00 sec)

mysql> desc receive_red_packets;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| receive_date     | date         | YES  | MUL | NULL    |       |
| receive_datetime | datetime     | YES  |     | NULL    |       |
| uid              | varchar(225) | YES  |     | NULL    |       |
| money            | float        | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

mysql> select sum(money) from receive_red_packets;
+-------------------+
| sum(money)        |
+-------------------+
| 74.70999916642904 |
+-------------------+
1 row in set (0.00 sec)

解决办法

了解到可能是float类型的精度问题，修改money列数据类型为decimal类型
float是浮点数，也称近似的数据类型，不能指定小数位
decimal可以精确地表示非常大或非常精确的小数，可指定精度。
decimal(m,n) n表示取n位小数，整数为m-n位

mysql> ALTER TABLE receive_red_packets MODIFY COLUMN money decimal(10,2);
Query OK, 80 rows affected, 80 warnings (0.12 sec)
Records: 80  Duplicates: 0  Warnings: 80

mysql> select sum(money) from receive_red_packets;
+------------+
| sum(money) |
+------------+
|      74.71 |
+------------+
1 row in set (0.00 sec)