CODEWITHSUNDEEP
mysqlprecision
Mehran
Mehran

Reputation: 16831

MySQL operators' precision on double

I'm trying to store a currency value in MySQL (InnoDb) and I need to write queries to aggregate some records (sum) and I'm having problem with the precision of the the output!

I've set the field's type to double and my values are some what precise but the MySQL's operators are not as precise as I need. For what it is worth, PHP's default operators are not precise enough either but there's bc* functions in PHP which can do the trick.

I was wondering if there's any way to tune the precision of MySQL operators? Including aggregation functions?

For the record, storing to and retrieving from MySQL won't affect my values which means double is an ideal type for my fields.

Upvotes: 0

Views: 63

Answers (1)

juergen d
juergen d

Reputation: 204756

Since money needs an exact representation don't use data types that are only approximate like double which is a floating-point. You can use a fixed-point numeric data type for that like

 numeric(15,2)
  • 15 is the precision (total length of value including decimal places)
  • 2 is the number of digits after decimal point

See MySQL Numeric Types:

These types are used when it is important to preserve exact precision, for example with monetary data.

Upvotes: 1

Related Questions