博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：http://blog.chinaunix.net/blog/post/id/5753131.html
1、创建测试表
root@localhost [dbwatcher]> create table float_table01(fund float(7,4));
Query OK, 0 rows affected (0.06 sec)
root@localhost [dbwatcher]> show tables;
+------------------+
| Tables_in_dbwatcher |
+------------------+
| float_table01 |
+------------------+
1 row in set (0.00 sec)
开始测试：
root@localhost [dbwatcher]> insert into float_table01 values(123.312);
Query OK, 1 row affected (0.04 sec)
root@localhost [dbwatcher]> insert into float_table01 values(123.3121);
Query OK, 1 row affected (0.00 sec)
root@localhost [dbwatcher]> insert into float_table01 values(1234.214);
ERROR 1264 (22003): Out of range value for column 'fund' at row 1
root@localhost [dbwatcher]> insert into float_table01 values(1234.2142);
ERROR 1264 (22003): Out of range value for column 'fund' at row 1
root@localhost [dbwatcher]> insert into float_table01 values(1234.22);
ERROR 1264 (22003): Out of range value for column 'fund' at row 1
root@localhost [dbwatcher]> insert into float_table01 values(134.22002);
Query OK, 1 row affected (0.00 sec)
root@localhost [dbwatcher]> insert into float_table01 values(134.22006);
Query OK, 1 row affected (0.00 sec)
root@localhost [dbwatcher]> insert into float_table01 values(999.00009); --小数位数超了，自动取近似值，进位
Query OK, 1 row affected (0.00 sec)
root@localhost [dbwatcher]> insert into float_table01 values(999.00001); --小数位数超了，自动取近似值，进位
Query OK, 1 row affected (0.00 sec)
root@localhost [dbwatcher]> commit;
Query OK, 0 rows affected (0.00 sec)
root@localhost [dbwatcher]> select * from float_table01;
+----------+
| fund |
+----------+
| 123.3120 |
| 123.3121 |
| 134.2200 |
| 134.2201 |
| 999.0001 |
| 999.0000 |
+----------+
说明：小数位超了，自动四舍五入
继续往下：
root@localhost [dbwatcher]> insert into float_table01 values(124.2); --小数点位数不够，自动补足
Query OK, 1 row affected (0.00 sec)
root@localhost [dbwatcher]> commit;
Query OK, 0 rows affected (0.00 sec)
结果如下：
root@localhost [dbwatcher]> select * from float_table01;
+----------+
| fund |
+----------+
| 123.3120 |
| 123.3121 |
| 134.2200 |
| 134.2201 |
| 999.0001 |
| 999.0000 |
| 124.2000 |
+----------+
7 rows in set (0.00 sec)
说明：小数点位数不够，自动补足
root@localhost [dbwatcher]> insert into float_table01 values(1000.9999); --超出范围，无法插入
ERROR 1264 (22003): Out of range value for column 'fund' at row 1
root@localhost [dbwatcher]> insert into float_table01 values(999.9999);
Query OK, 1 row affected (0.00 sec)
root@localhost [dbwatcher]> commit;
Query OK, 0 rows affected (0.00 sec)
结果如下：
root@localhost [dbwatcher]> select * from float_table01;
+----------+
| fund |
+----------+
| 123.3120 |
| 123.3121 |
| 134.2200 |
| 134.2201 |
| 999.0001 |
| 999.0000 |
| 124.2000 |
| 999.9900 |
| 999.9999 |
+----------+
9 rows in set (0.00 sec)
说明：最大值为：999.9999
root@localhost [dbwatcher]> select * from float_table02;
+-----------+
| fund |
+-----------+
| 999999.25 |
+-----------+
1 row in set (0.00 sec)
root@localhost [dbwatcher]> insert into float_table02 values(131071.21);
Query OK, 1 row affected (0.00 sec)
(root@localhost [dbwatcher]> insert into float_table02 values(131072.21);
Query OK, 1 row affected (0.00 sec)
root@localhost [dbwatcher]> commit;
Query OK, 0 rows affected (0.00 sec)
查询结果：
root@localhost [dbwatcher]> select * from float_table02;
+-----------+
| fund |
+-----------+
| 999999.25 |
| 131071.21 |
| 131072.20 |
+-----------+
3 rows in set (0.00 sec)
root@localhost [dbwatcher]> insert into float_table02 values(131072.00);
Query OK, 1 row affected (0.00 sec)
root@localhost [dbwatcher]> select * from float_table02;
+-----------+
| fund |
+-----------+
| 999999.25 |
| 131071.21 |
| 131072.20 |
| 131072.00 |
+-----------+
4 rows in set (0.00 sec)
说明：最大值为131072，超过最大值发现存储出现误差
root@localhost [dbwatcher]> create table q(
-> money float(10,3));
Query OK, 0 rows affected (0.07 sec)
root@localhost [dbwatcher]> insert into q values(1234567.3);
Query OK, 1 row affected (0.00 sec)
root@localhost [dbwatcher]> commit;
Query OK, 0 rows affected (0.00 sec)
root@localhost [dbwatcher]> select * from q;
+-------------+
| money |
+-------------+
| 1234567.250 |
+-------------+
1 row in set (0.00 sec)
总结：
float列类型默认长度查不到结果，必须指定精度，否则可能查不到记录
FLOAT(M,D)或REAL(M,D)或DOUBLE PRECISION(M,D)。这里，“(M,D)”表示该值一共显示M位整数，其中D位位于小数点后面。
例如，定义为FLOAT(7,4)的一个列可以显示为-999.9999。MySQL保存值时进行四舍五入，因此如果在FLOAT(7,4)列内插入999.00009，近似结果是999.0001；
MySQL数据精度控制相关数据类型
如下三种：
float(M,D)，如果D不指定的话，默认是3位精度（小数点位数）
double(M,D)，如果D不指定的话，默认是14位精度（小数点位数）
DECIMAL(M,D)，如果D不指定的话，默认是0位精度（小数点位数）
---the end
