The MySQL `DECIMAL`

data type is one of the numbers data types in MySQL that can store numbers with decimal points.

The `DECIMAL`

type requires more storage space than `FLOAT`

or `DOUBLE`

because `DECIMAL`

stores the exact representation of your number value.

To see how the `DECIMAL`

type differs from `DOUBLE`

, let’s create a table with numbers data as an example.

Suppose you have a `numbers`

table in your database with the following data:

```
+------------+-------------+
| num_double | num_decimal |
+------------+-------------+
| 2.5 | 2.50000 |
| 0.01 | 0.01000 |
| -1.5 | -1.50000 |
+------------+-------------+
```

Now let’s try to increment each value in the columns above by `0.2`

as follows:

```
SELECT (num_double + 0.2), (num_decimal + 0.2) FROM numbers;
```

The returned result set is as shown below:

```
+---------------------+---------------------+
| (num_double + 0.2) | (num_decimal + 0.2) |
+---------------------+---------------------+
| 2.7 | 2.70000 |
| 0.21000000000000002 | 0.21000 |
| -1.3 | -1.30000 |
+---------------------+---------------------+
```

As you can see, there’s a problem with the `DOUBLE`

type when you calculate some numbers with decimal points like `0.01 + 0.1`

.

This is because `DOUBLE`

type numbers are stored as approximate representations. `DOUBLE`

type can store a larger range of possible numbers than `DECIMAL`

, but there will be precision bugs when you need to do calculations.

`DECIMAL`

types are commonly used for number data with many decimal points that are going to be calculated further such as financial or monetary data.

`FLOAT`

and `DOUBLE`

can be used for decimal numbers that doesn’t require further calculation and are mostly final, such as everyday weather report.

When you create a `DECIMAL`

column, you need to specify the **precision digits** available for the column (`M`

) and the amount of **scale** reserved for the decimal points (`D`

)

```
DECIMAL(M,D)
```

The `M`

number range is `1 - 65`

while the `D`

number ranges from `0 - 30`

and must not be larger than `M`

.

For example, the following `num_decimal`

column can contain `10`

digits with `5`

decimal points:

```
CREATE TABLE `numbers` (
`num_decimal` DECIMAL(10,5) DEFAULT NULL
)
```

The maximum range of the `num_decimal`

column above is `99999.99999`

and `-99999.99999`

.

When you don’t specify the range, then MySQL may default to `(10,0)`

as the `length`

parameters of the column.

And that’s how the MySQL `DECIMAL`

type works. The data type is best for when you have numbers with decimal points that require exact representation.

**Related articles:**