What is the best data type to store money values in MySQL?

We can store the money values in MySQL in decimal(value1,value2). Here, value1 is the total range including value2. The value2 specifies the number of digits after the decimal point. To understand this concept, the steps are given below.

First a table is created using the create command.

mysql> CREATE table MoneyDemo
-> (
-> Id int,
-> Money decimal(10,2)
-> );
Query OK, 0 rows affected (0.46 sec)

As can be seen from the above command, the decimal value has 10 digits only and also 2 digits only after the decimal point.

After creating the table, some records are inserted with the help of the insert command. This is given as follows −

mysql> INSERT into MoneyDemo values(1,98777445.50);
Query OK, 1 row affected (0.13 sec)

mysql> INSERT into MoneyDemo values(2,12345678.00);
Query OK, 1 row affected (0.18 sec)

mysql> INSERT into MoneyDemo values(3,88888888.50);
Query OK, 1 row affected (0.20 sec)

If more than 10 digits are inserted in the above commands, an error message will be generated. This can be seen below −

mysql> INSERT into MoneyDemo values(3,8888888888.50);
ERROR 1264 (22003): Out of range value for column 'Money' at row 1

All the records in the table can be displayed with the help of the select statement. The query for this is given below −

mysql> SELECT * from MoneyDemo;

The output obtained is as follows

+------+-------------+
| Id   | Money       |
+------+-------------+
| 1    | 98777445.50 |
| 2    | 12345678.00 |
| 3    | 88888888.50 |
+------+-------------+
3 rows in set (0.00 sec)