目标:使用小数取代整数

    整数是一个很有用的数据类型,但是只能存出整数,但是它不能表示像“2.5”这样的浮点型。如果数据对精度要求很好,你需要使用另一种数据类型来代替整形。当然还要保证运算结果必须正确。

反模式:使用FLOAT类型

    SQL中的float类型,和其他大多数编程语言的float一样,根据IEEE754标准使用二进制格式编码实数数据。你需要了解一些浮点数的定义规范,才能有效的使用这个数据类型。

舍入的必要性

    很多程序员并不清楚浮点类型的特性:并不是所有十进制中描述的信息都能使用二进制存储。出于一些必要因素,浮点数通常会舍入到一个非常近似的值。
    举例来说,1/3用一个无限循环的十进制可以表示为0.333~,其真实值无法完整的写出来,因此折中的方法就是限制精度,选择一个尽可能接近原始值的数据,比如0.333。然而,这个数字却不是我们所希望的值。

1
2
1/3 + 1/3 + 1/3 = 1
0.333 + 0.333 + 0.333 = 0.999

    即使提高精度,也无法将这三个值加起来等于1.0.这就是使用有限精度的数表示无限小数的必要妥协。

1
2
1/3 + 1/3 + 1/3 = 1
0.333333 + 0.333333 + 0.333333 = 0.999999

    IEEE754使用二进制表示浮点数。十进制中的无限小数在二进制中的表达方式是完全不同的。然后一些十进制有限小数,比如0.1,在二进制中确实无限小数。

1
2
3
4
5
6
7
8
9
10
0.1 * 2 = 0.2     -----0
0.2 * 2 = 0.4 -----0
0.4 * 2 = 0.8 -----0
0.8 * 2 = 1.6 -----1
0.6 * 2 = 1.2 -----1
0.2 * 2 = 0.4 -----0
.
.
.
//你懂的 0.0001100110011001100110011001100110011001100110011001101
在SQL中使用FLOAT

    有些数据库能够通过某种方式来弥补数据的不精准性,输出我们期望的值。

1
2
select hourly_rate from Account where account_id=123   ;
-- Result:59.95

    但float类型的列中实际存储的数据可能并不等于他的值。如果将这个值方法十亿倍,或者将他作为查询条件。

1
2
3
4
5
select hourly_rate * 100000000 from Account where account_id=123;
-- Result:59950000762.939

select * from account where hourly_rate = 59.95;
-- Result:empty set; no rows match

    所以你需要将查询条件修改成

1
select * from account where ABS(hourly_rate - 59.95) < 0.00001;

    另一个使用合计函数计算很多值的时候,影响比较明显,比如使用sum()计算某一列的所有值。举个例子,如果你用1*1.0,无论你执行多少次,结果都是1。但是你用1*0.999,结果就完全不同,执行一千次你的得到的结果约等于0.3677。

如何识别反模式:

    任何使用Float、Real或者Double Percent类型的设计都有可能是反模式。

合理使用反模式:

    如果要存储的值取值范围很大,大于integer、numeric的范围,那只能用float了。科学计算类的程序通常使用float。
    orale使用的float是精准值,而binary_float使用的是IEEE754标准编码。

解决方案:使用numeric、decimal类型

    使用的numeric或decimal类型来代替float及其类似的数据类型进行固定精度的小数存储。numeric、decimal,他们不会对存储的有理数进行舍入,所以他保存的值和原始的值是一致的。

1
alter table bug add column hours decimal(9, 2);

    其中9代表你可以存储123456789,而1234567890则为非法值。2表示你可以存储1234567.89,而12345678.91、123456.789都为非法值。
    你依旧不能存储1/3的值,它应该当作非精度的值来处理。

结论:尽可能不要使用浮点数