Best data type for storing currency values in a MySQL database?

For representation of money, we need to use Decimal (TotalDigitsinteger, DigitsAfterDecimalinteger) method.

Let’s say, we need to display the value 345.66. For that, count how many digits are available. In value 345.66, there are 5 digits in total and 2 digits after decimal point, which is 66.

We can represent the same with the help of Decimal() method from MySQL. Here is the exact representation.

DECIMAL(5,2)

Let us first create a table and consider the same above representation for our example −

mysql> create table MoneyRepresentation
   -> (
   -> Money Decimal(5,2)
   -> );
Query OK, 0 rows affected (0.65 sec)

Let us insert the same value i.e. 345.66

mysql> insert into MoneyRepresentation values(345.66);
Query OK, 1 row affected (0.13 sec)

Display all records with the help of SELECT statement. The query is as follows −

mysql> select *from MoneyRepresentation;

The following is the output.

+--------+
| Money  |
+--------+
| 345.66 |
+--------+
1 row in set (0.00 sec)

Look at the above output, we got 5 digits total and added 2 digits after decimal point because we have set the function as

Decimal(5,2)