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.