How do I stop a MySQL decimal field from being rounded?

You can stop rounding decimal field with the help of DECIMAL() function. Here is the demo of a rounded decimal field. For our example, let us first create a demo table

mysql> create table stopRoundingDemo    -> (    -> Amount DECIMAL(7)    -> ); Query OK, 0 rows affected (0.67 sec)

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

mysql> insert into stopRoundingDemo values(7836.783); Query OK, 1 row affected, 1 warning (0.43 sec) mysql> insert into stopRoundingDemo values(1737.67); Query OK, 1 row affected, 1 warning (0.23 sec) mysql> insert into stopRoundingDemo values(110.50); Query OK, 1 row affected, 1 warning (0.33 sec)

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

mysql> select *from stopRoundingDemo;

The following is the output

+--------+ | Amount | +--------+ | 7837   | | 1738   | | 111    | +--------+ 3 rows in set (0.08 sec)

In the above sample output, the decimal is rounded.

Now, we will see how to stop decimal being rounded. For that, let us first create a new table and set the DECIMAL type in a way that would give the result without round off

mysql> create table stopRoundingDemo2    -> (    -> Amount DECIMAL(10,4)    -> ); Query OK, 0 rows affected (0.81 sec)

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

mysql> insert into stopRoundingDemo2 values(7836.783); Query OK, 1 row affected (0.14 sec) mysql> insert into stopRoundingDemo2 values(1737.67); Query OK, 1 row affected (0.14 sec) mysql> insert into stopRoundingDemo2 values(110.50); Query OK, 1 row affected (0.16 sec)

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

mysql> select *from stopRoundingDemo2;

The output is as follows

+-----------+ | Amount    | +-----------+ | 7836.7830 | | 1737.6700 | | 110.5000  | +-----------+ 3 rows in set (0.00 sec)