How to store decimal in MySQL?

To store decimal in MySQL, you need to understand the two parameters. The syntax is as follows −

DECIMAL(yourTotalDigit,yourDigitsAfterDecimalPoint);

For Example −

DECIMAL(4,2), it means you can take 4 digits total and 2 digit after decimal point.

The first parameter is up to 2 before the decimal point

The second parameter is up to 2 digit after decimal point.

• Case 1 − 12.34 is valid.
• Case 2 − 123.4 is not valid.
• Case 3 − 1.234 is valid because the value 4 will be ignored and it will be considered 1.23

Now you can check using table −

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

The invalid values for our example Decimal(4,2) are as follows −

mysql> insert into DecimalDemo values(123.4);
ERROR 1264 (22003): Out of range value for column 'Amount' at row 1

mysql> insert into DecimalDemo values(1234);
ERROR 1264 (22003): Out of range value for column 'Amount' at row 1

mysql> insert into DecimalDemo values(1234.56);
ERROR 1264 (22003): Out of range value for column 'Amount' at row 1

The valid values are as follows −

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

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

mysql> insert into DecimalDemo values(.2345);
Query OK, 1 row affected, 1 warning (0.18 sec)

mysql> insert into DecimalDemo values(1.234);
Query OK, 1 row affected, 1 warning (0.16 sec)

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

mysql> select *from DecimalDemo;

Output

+--------+
| Amount |
+--------+
| 12.34  |
| 12.40  |
| 0.23   |
| 1.23   |
+--------+
4 rows in set (0.00 sec)