storing money amounts in mysql

mysql floating-point currency fixed-point
David picture David · Feb 12, 2010 · Viewed 50.8k times · Source

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

Morfildur picture Morfildur · Feb 12, 2010

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

Documentation for MySQL Numeric Types

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.

Related questions

What MySQL data type should be used for Latitude/Longitude with 8 decimal places?

I'm working with map data, and the Latitude/Longitude extends to 8 decimal places. For example: Latitude 40.71727401 Longitude -74.00898606 I saw in the Google document which uses: lat FLOAT( 10, 6 ) NOT NULL, lng FLOAT( 10, 6 ) NOT NULL however, their decimal places only go …

Read More
Storing statistical data, do I need DECIMAL, FLOAT or DOUBLE?

I am creating for fun, but I still want to approach it seriously, a site which hosts various tests. With these tests I hope to collect statistical data. Some of the data will include the percentage of the completeness of …

Read More
Datatype to Store Longitude/Latitude in Mysql

'43.005895','-71.013202' Trying to use: INSERT INTO table(fanDetLocZip, fanDetLocCity, fanDetLocState, fanDetLocLat, fanDetLocLong, fanDetLocTZ, fanDetLocDST) VALUES(00210, 'Portsmouth', 'NH', '43.005895', '-71.013202', -5, 1); I'm currently using the datatype SPATIAL, GEOMETRY. Its giving me errors like: Cannot get geometry …

Read More