So you want to know what the best data type is for storing currency/money values in MySQL?
When handling money in MySQL, use DECIMAL(13,2) if you know the precision of your money values or use DOUBLE if you just want a quick good-enough approximate value.
The Details
On some database management systems (DBMS) there is a data type specially set aside for handling money. However, MySQL doesn't have one so the choice of best representation is left to the programmer.
Since about 1999, using MySQL 3, I have generally used the DOUBLE data type to represent money values as it stores decimals at reasonably high precision. The values stored are approximations, but the number of decimals treated accurately is enough for normal money values. The ROUND function is helpful when doing aggregation queries, such as:
SELECT
ROUND(SUM(amount), 2) AS total_amount
FROM orders
WHERE created_at > '2012-01-01'
However, since MySQL 5 there are some better data types for storing exact money values. From the MySQL manual:
"The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data. In MySQL, NUMERIC is implemented as DECIMAL, so the following remarks about DECIMAL apply equally to NUMERIC."The DECIMAL data type requires you to specify the number of digits before the decimal point and the number of digits after the decimal point.
So if your application needs to handle money values up to a trillion dollars (or euros or pounds), then this should work:
DECIMAL(13, 2)However, considering the rate that the central banks are printing money these days, we might all soon need to be processing larger values for currency.
I hope this helps.
References
- MySQL: 10.2.2. Fixed-Point Types (Exact Value)
- MySQL: 10.2.3. Floating-Point Types (Approximate Value)
One Last Thing
One of the services my company offers is database conversion solutions. Are you looking to convert spreadsheet data to a MySQL database? Check out SQL Converter's new Expert Conversion Service. The classic Excel macro is still available as well.
