Which MySQL type is most suitable for “price” column?

The best type for price column should be DECIMAL. The type DECIMAL stores the value precisely.

For Example - DECIMAL(10,2) can be used to store price value. It means the total digit will be 10 and two digits will be after decimal point.

To understand the type DECIMAL, let us create a table.

mysql> create table PriceDemo
   −> (
   −> ProductPrice DECIMAL(10,2)
   −> );
Query OK, 0 rows affected (0.60 sec)

Now insert some records in the table in the form of price. The query to insert records is as follows −

mysql> insert into PriceDemo values(12345.67);
Query OK, 1 row affected (0.12 sec)

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

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

mysql> insert into PriceDemo values(4444444.50);
Query OK, 1 row affected (0.18 sec)

Display all records which we have inserted above. The query to display all records −

mysql> select *from PriceDemo;

The following is the output −

+--------------+
| ProductPrice |
+--------------+
|     12345.67 |
|  99999999.67 |
|    123456.67 |
|   4444444.50 |
+--------------+
4 rows in set (0.00 sec)

Rishi Rathor

Published on 11-Jan-2019 13:00:23