Decimal vs Float in MySQL
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 found this post helpful, please consider sharing to your network. I'm also available to help you be successful with your distributed systems! Please reach out if you're interested in working with me, and I'll be happy to schedule a free one-hour consultation.