Rusty Razor Blade
While I’ve known how floating points are stored, I didn’t know if decimal was stored any differently, or if it was more or less accurate. According to a post on the MySQL list:
Bruno Rodrigues Silva wrote:
> Dear all.
>
> The MySQL Manual inform that Decimal Data Type is used
> for represent exact-number, but like Float Point Data
> Type, Decimal use rounding case the fractional part is
> not sufficient. Therefore, what the difference?Hi Bruno,
FLOAT rounds as floating point and DECIMAL rounds as you would expect it
to. Floating point arithmetic is not exact and that’s why they’ve called
DECIMAL an “exact packed decimal number” because apparently you can rely
on it’s roundings.The primary use for DECIMAL is money, where floating point would fail
miserably and produce lots of errors (money leak) at the end of the month.As floating point arithmetic has it’s own separated section on your
processor (unless you have a 386 or older computer) it would be *much*
faster than DECIMAL, so use it only if you are absolutely sure you need it.cheers,
–renato
Not exactly breaking news, but I had never looked at decimal very closely. Good to know.
If you enjoyed this article, please consider sharing it!
Storage and straight retrieval has never been the issue, but prior to MySQL 5.0 any calculations on a DECIMAL type column would be done using floating point arithmetic. Starting with MySQL 5.0, the server uses a separate fixed-point arithmetic library for this. This is essential to not lose precision with money (and thus not lose/gain fractions of cents all the time
Thx for the historical clarification of float versus decimal. It helped clear things up. –Richard