Question

Set MySQL DECIMAL with accuracy of 10 digits after the comma?

Answer 1

Ankith Reddy

Ankith Reddy

Answered on 26th Feb, 2019

As you know the DECIMAL() method takes two parameter. The first parameter tells about the total number of digits and second parameter tells about number of digits after decimal point. Therefore, if you use DECIMAL(10,10) that means you can use only 10 fractional digit.

For Example: Store 0.9999999999 with DECIMAL(20,10).

To understand what we discussed above, let us create a table. The query to create a table is as follows:

mysql> create table Decimal_Demo

-> (

-> Id int NOT NULL AUTO_INCREMENT,

-> Price DECIMAL(20,10),

-> PRIMARY KEY(Id)

-> );

Query OK, 0 rows affected (1.06 sec)

Now you can insert some records in the table using insert command. The query to insert record is as follows:

mysql> insert into Decimal_Demo(Price) values(7475433123.1727374747);

Query OK, 1 row affected (0.15 sec)

mysql> insert into Decimal_Demo(Price) values(999.9999999999);

Query OK, 1 row affected (0.19 sec)

mysql> insert into Decimal_Demo(Price) values(1363645345.1726364664);

Query OK, 1 row affected (0.16 sec)

mysql> insert into Decimal_Demo(Price) values(1.172636466437475656565);

Query OK, 1 row affected, 1 warning (0.53 sec)

mysql> insert into Decimal_Demo(Price) values(0.9999999999);

Query OK, 1 row affected (0.20 sec)

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

mysql> select *from Decimal_Demo;

The following is the output:

+----+-----------------------+

| Id | Price |

+----+-----------------------+

| 1 | 7475433123.1727374747 |

| 2 | 999.9999999999 |

| 3 | 1363645345.1726364664 |

| 4 | 1.1726364664 |

| 5 | 0.9999999999 |

+----+-----------------------+

5 rows in set (0.00 sec)

Answer 2

Standard SQL requires that DECIMAL(5,2) be able to store any value with five digits and two decimals, so values that can be stored in the salary column range from -999.99 to 999.99 . In standard SQL, the syntax DECIMAL( M ) is equivalent to DECIMAL( M ,0) .