29

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

BenMorel
  • 34,448
  • 50
  • 182
  • 322
tom
  • 8,189
  • 12
  • 51
  • 70
  • 1
    Which database? Some like Oracle only have decimal – mmmmmm Sep 22 '10 at 11:28
  • See [Use Float or Decimal for Accounting Application Dollar Amount?](http://stackoverflow.com/questions/61872/use-float-or-decimal-for-accounting-application-dollar-amount "StackOverflow"). – Andrew Sep 22 '10 at 11:32
  • 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: http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#i16209 – Michael Borgwardt Sep 22 '10 at 11:57

5 Answers5

42

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

MaxVT
  • 12,989
  • 6
  • 36
  • 50
23

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!

Michael Borgwardt
  • 342,105
  • 78
  • 482
  • 720
10

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

Azhar
  • 20,500
  • 38
  • 146
  • 211
2

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.

Anuja Pawar
  • 39
  • 2
0

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

Joshua Bloch recommends BigDecimal.

Dead Programmer
  • 12,427
  • 23
  • 80
  • 112