Take the 2-minute tour ×

Which type (Float or decimal) is best used to store prices in a mysql database?

share|improve this question
Which database? Some like Oracle only have decimal –  Mark Sep 22 '10 at 11:28
It's for a mysql database but if there are differences between others I like to hear it. –  tom Sep 22 '10 at 11:36
@Mark: Oracle also has binary floats: download.oracle.com/docs/cd/B19306_01/server.102/b14220/… –  Michael Borgwardt Sep 22 '10 at 11:57

5 Answers 5

up vote 22 down vote accepted

Floats are not exact and can introduce cumulative rounding errors. Decimal is the best format for financial information that must be exact.

share|improve this answer
Decimal is not exact either - but it's not exact in a way we expect. –  Michael Borgwardt Sep 22 '10 at 11:49
If the meaning of Michael's comment is not clear, please see his answer below. –  MaxVT Sep 24 '10 at 16:11

For Financial calculations use Decimal

According to IEEE 754 Floats were always binary, only the new standard IEEE 754R defined decimal formats. Many of the fractional binary parts can never equal the exact decimal representation. Any binary number can be written as m/2^n (m, n positive integers), any decimal number as m/(2^n*5^n). As binarys lack the prime factor 5, all binary numbers can be exactly represented by decimals, but not vice versa.

0.3 = 3/(2^1 * 5^1) = 0.3

0.3 = [0.25/0.5] [0.25/0.375] [0.25/3.125] [0.2825/3.125]

     1/4       1/8     1/16       1/32

So for Financial calculations use Decimal not FLOAT

share|improve this answer

Prices are decimal values, and calculations on them are expected to behave like decimal fractions when it comes to rounding, literals, etc.

That's exactly what decimal types do.

Floats are stored as binary fractions, and they do not behave like decimal fractions - their behaviour is frequently not what people used to decimal math expect. Read The Floating-Point Guide for detailed explanations.

For money values, never never use binary float types - especially when you have a perfectly good decimal type available!

share|improve this answer

When we store a number in float we don't save the exact number,it is an approximation. The integer part gets the priority and fractional part is as close as the type size. So if you have calculations and need accurate result use Decimal.

share|improve this answer

Please Use BigDecimal , as it is the best for the prices , since pennies are rounded properly to dollar.

Joshua Bloch recommends BigDecimal.

share|improve this answer
Do you see a "java" tag somewhere? –  Michael Borgwardt Sep 22 '10 at 11:47
@Michael you got me!! . i use to look out for newer question filtering by java ,so i got this question. –  Dead Programmer Sep 22 '10 at 11:57

Your Answer


