mysql 慎用 float字段类型

出现的问题

非常大的坑，因为分的数据量的值太小了，但有使用了float类型存储，结果sum的时候值对不上。
一开始不知道什么原因，后面看执行SQL，突然发现，怎么显示出来的位数很小，才发现是字段类型导致。

验证

mysql> set @d = 0.03137322132693297;
Query OK, 0 rows affected (0.00 sec)

mysql> select cast(@d as float),cast(@d as double);
+-------------------+---------------------+
| cast(@d as float) | cast(@d as double)  |
+-------------------+---------------------+
|         0.0313732 | 0.03137322132693297 |
+-------------------+---------------------+
1 row in set (0.00 sec)

mysql> mysql> select cast(@d as float),cast(@d as double),cast(@d as decimal(20,10));
+-------------------+---------------------+----------------------------+
| cast(@d as float) | cast(@d as double)  | cast(@d as decimal(20,10)) |
+-------------------+---------------------+----------------------------+
|         0.0313732 | 0.03137322132693297 |               0.0313732213 |
+-------------------+---------------------+----------------------------+
1 row in set (0.00 sec)

mysql> select cast(@d as float),cast(@d as double),cast(@d as decimal(200,50));
ERROR 1426 (42000): Too-big precision 200 specified for '@d'. Maximum is 65.

mysql> select cast(@d as float),cast(@d as double),cast(@d as decimal(60,50));
ERROR 1425 (42000): Too big scale 50 specified for column '@d'. Maximum is 30.

mysql> select cast(@d as float),cast(@d as double),cast(@d as decimal(30,20));
+-------------------+---------------------+----------------------------+
| cast(@d as float) | cast(@d as double)  | cast(@d as decimal(30,20)) |
+-------------------+---------------------+----------------------------+
|         0.0313732 | 0.03137322132693297 |     0.03137322132693297000 |
+-------------------+---------------------+----------------------------+
1 row in set (0.00 sec)

mysql> 

慎用float

float是32位，存进去会自动截断。double是64位，可以存2倍的精度。