Best data type to store money values in MySQL

I want to store many records in a MySQL database. All of them contains money values. But I don't know how many digits will be inserted for each one.
Which data type do I have to use for this purpose?
VARCHAR or INT (or other numeric data types)?


Solution 1:

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

decimal(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.

Solution 2:

You can use DECIMAL or NUMERIC both are same

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. : MySQL

i.e. DECIMAL(10,2)

Example settings

Good read

Solution 3:

I prefer to use BIGINT, and store the values in by multiply with 100, so that it will become integer.

For e.g., to represent a currency value of 93.49, the value shall be stored as 9349, while displaying the value we can divide by 100 and display. This will occupy less storage space.

Caution:
Mostly we don't perform currency * currency multiplication, in case if we are doing it then divide the result with 100 and store, so that it returns to proper precision.