一般在oracle的数字类型都设置为number不带长度,这个扩展性比较好。
不太需要去关心是整数还是小数,而且长度所占的空间也是和存储的数值相关。
mysql中数值类型占的长度比较固定,对于float的使用心里没什么底。 直接用
数值进行小测一下。
##################################################################
mysql> show create table tmp_xf_test\G
*************************** 1. row ***************************
Table: tmp_xf_test
Create Table: CREATE TABLE `tmp_xf_test` (
`t1` float(7,4) DEFAULT NULL,
`t2` float DEFAULT NULL,
`t3` double(7,4) DEFAULT NULL,
`t4` double DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1. mysql> insert into tmp_xf_test values(5.1,5.1,5.1,5.1);
| 5.1000 | 5.1 | 5.1000 | 5.1 |
--设置了具体精度的,小数后面会用0补全
2. 123456789.123456789
| 999.9999 | 1.23457e+08 | 999.9999 | 123456789.123457 |
--限制(7,4)的 溢出了
3. 123.4567
| 123.4567 | 123.457 | 123.4567 | 123.4567 |
4. 123.45678
| 123.4568 | 123.457 | 123.4568 | 123.45678 |
5. 1234.456
| 999.9999 | 1234.46 | 999.9999 | 1234.456 | --溢出
6. 0.00009
| 0.0001 | 9e-05 | 0.0001 | 9e-05 |
7. 0.123456789012345678901
| 0.1235 | 0.123457 | 0.1235 | 0.123456789012346 | --16位有效长度
8. 12345678901234567890.123456789012345678901
| 999.9999 | 1.23457e+19 | 999.9999 | 1.23456789012346e+19 |
-- float 7位有效长度,double 16位有效长度
9. 12345678901234567890
| 999.9999 | 1.23457e+19 | 999.9999 | 1.23456789012346e+19 |
-----------------------具体参考手册 http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html
The FLOAT and DOUBLE data types are used to represent approximate numeric
data values.
For FLOAT, the SQL standard allows an optional specification of the precision
(but not the range of the exponent) in bits following the keyword FLOAT in parentheses.
MySQL also supports this optional precision specification, but the precision value is
used only to determine storage size. A precision from 0 to 23 results in a four-byte
single-precisionFLOAT column. A precision from 24 to 53 results in an eight-byte
double-precision DOUBLE column.
MySQL allows a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE
PRECISION(M,D). Here, “(M,D)” means than values can be stored with up to M digits
in total, of which D digits may be after the decimal point. For example, a column defined
as FLOAT(7,4) will look like -999.9999 when displayed. MySQL performs rounding when
storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate
result is 999.0001.
--关于浮点数可以参考http://www.ibm.com/developerworks/cn/java/j-math2.html
mysql> select * from tmp_xf_test;
+----------+-------------+----------+----------------------+
| t1 | t2 | t3 | t4 |
+----------+-------------+----------+----------------------+
| 5.1000 | 5.1 | 5.1000 | 5.1 |
| 999.9999 | 1.23457e+08 | 999.9999 | 123456789.123457 |
| 123.4567 | 123.457 | 123.4567 | 123.4567 |
| 123.4568 | 123.457 | 123.4568 | 123.45678 |
| 999.9999 | 1234.46 | 999.9999 | 1234.456 |
| 0.0001 | 9e-05 | 0.0001 | 9e-05 |
| 0.1235 | 0.123457 | 0.1235 | 0.123456789012346 |
| 999.9999 | 1.23457e+19 | 999.9999 | 1.23456789012346e+19 |
| 999.9999 | 1.23457e+19 | 999.9999 | 1.23456789012346e+19 |
+----------+-------------+----------+----------------------+
9 rows in set (0.00 sec)