Storing money amounts in MySQL?

To store money amounts in MySQL, the best choice is to use DECIMAL data type or NUMERIC type. Float data type is not a good choice for money amounts. It gives some rounding errors. Therefore, avoid float for money amounts.

Let us first create a table with data type DECIMAL. The following is the query to create a table −

mysql> create table MoneyStorageDemo
   -> (
   -> Amount DECIMAL(4,2)
   -> );
Query OK, 0 rows affected (0.44 sec)

Inserting some values into the table with the help of insert command. The query is as follows −

mysql> insert into MoneyStorageDemo values(50.2);
Query OK, 1 row affected (0.10 sec)
mysql> insert into MoneyStorageDemo values(5.50);
Query OK, 1 row affected (0.32 sec)
mysql> insert into MoneyStorageDemo values(10.4);
Query OK, 1 row affected (0.26 sec)

Now you can display all the values from the table with the help of select statement. The query is as follows −

mysql> select *from MoneyStorageDemo;

Here is the output −

+--------+
| Amount |
+--------+
| 50.20  |
| 5.50   |
| 10.40  |
+--------+
3 rows in set (0.00 sec)

George John

Updated on: 30-Jul-2019

6K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started