What is the best datatype for currencies in MySQL?

The best data type for currencies in MySQL is a DECIMAL. The syntax of DECIMAL data type is as follows −

DECIMAL(TotalDigit,NumberOfDigitAfterDecimalPoint);

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table CurrenciesDemo
   -> (
   -> TotalPrice DECIMAL(10,2)
   -> );
Query OK, 0 rows affected (1.82 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into CurrenciesDemo values(1647575.67);
Query OK, 1 row affected (0.19 sec)

mysql> insert into CurrenciesDemo values(1647575);
Query OK, 1 row affected (0.21 sec)

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

mysql> insert into CurrenciesDemo values(1986.50);
Query OK, 1 row affected (0.23 sec)

mysql> insert into CurrenciesDemo values(99999999.90);
Query OK, 1 row affected (0.17 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from CurrenciesDemo;

The following is the output −

+-------------+
| TotalPrice  |
+-------------+
| 1647575.67  |
| 1647575.00  |
| 99599596.00 |
| 1986.50     |
| 99999999.90 |
+-------------+
5 rows in set (0.00 sec)

The following is the query to show all the above prices with prefix symbol like dollar −

mysql> select concat('$',TotalPrice) as DollerSign from CurrenciesDemo;

The following is the output −

+--------------+
| DollerSign   |
+--------------+
| $1647575.67  |
| $1647575.00 |
| $99599596.00 |
| $1986.50 |
| $99999999.90 |
+--------------+
5 rows in set (0.00 sec)

Arjun Thakur

Published on 01-Feb-2019 14:55:58