storing money amounts in mysql

Question

I want to store 3.50 into a mysql table. I have a float that I store it in, but it stores as 3.5, not 3.50. How can I get it to have the trailing zero?

Answer 1

Does it really matter if it stores is as 3.5, 3.50 or even 3.500?

What is really important is how it is displayed after it is retrieved from the db.

Or am I missing something here?

Also don't use a float, use a decimal. Float has all sorts of rounding issue and isn't very big.

Answer 2

Do not store money values as float, use the DECIMAL or NUMERIC type:

http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html

EDIT & clarification: Float values are vulnerable to rounding errors are they have limited precision so unless you do not care that you only get 9.99 instead of 10.00 you should use DECIMAL/NUMERIC as they are fixed point numbers which do not have such problems.

Answer 3

Why do you want to store "3.50" into your database? 3.5 == 3.50 == 3.5000 as far as the database is concerned.

Your presentation and formatting of figures/dates/etc should be done in the application, not the database.

Answer 4

If you use DECIMAL or NUMERIC types, you can declare them as for example DECIMAL(18, 2) which would force 2 decimals even if they were 0. Depending on how big values you expect you can change the value of the first parameter.

Answer 5

To store values you can use a DECIMAL(10,2) field, then you can use the FORMAT function:

SELECT FORMAT(`price`, 2) FROM `table` WHERE 1 = 1

Answer 6

It's not generally a good idea to store money as a float as rounding errors can occurr in calculations.

Consider using DECIMAL(10,2) instead.