Decimal vs Double vs Float in MySQL

When designing a MySQL database, selecting the right numeric data type is crucial for balancing precision, storage efficiency, and performance. Decimal, Double, and Float are three common numeric types, each serving distinct purposes.

While Decimal offers exact precision for financial data, Float and Double handle approximate values for scientific calculations. This article explores their differences, use cases, and practical examples to help you make informed decisions.

What is Decimal

Decimal (or DECIMAL(M,D)) is a fixed-point data type designed for precise arithmetic, such as financial calculations. It stores exact numeric values with a specified precision (M = total digits) and scale (D = decimal places). For example, DECIMAL(10,2) can store up to 10 digits, with 2 reserved for decimals (e.g., 12345678.99).

Following are the Key Features of a Decimal value -

• Exact precision ? No rounding errors.
• Flexible sizing ? Define precision and scale (e.g., DECIMAL(5,3) for 12.345).
• Larger storage ? Requires more space than Float/Double.

What is Double

Double (or DOUBLE) is a floating-point data type for approximate numeric values. It provides high precision (about 15 decimal places) and supports a wide range of magnitudes (e.g., 1.797E+308). Use it for scientific data where minor rounding errors are acceptable.

Following are the Key Features of a Double value -

• High range ? It handles very large/small numbers.
• 8 bytes storage ? More precise than Float.
• Approximate ? Minor rounding errors may occur.

What is Float

Float (or FLOAT) is similar to Double but with lower precision (~7 decimal places) and smaller storage (4 bytes). It's suitable for non-critical calculations like temperature readings. Following are the Key Features of a Floating Point value -

• Lower precision ? Faster but less accurate than Double.
• 4 bytes storage ? Efficient for large datasets.

Decimal vs Double vs Float: Comparison Table

Parameter	Decimal	Double	Float
Precision	Exact (user-defined)	~15 decimal places	~7 decimal places
Storage	Variable (depends on M and D)	8 bytes	4 bytes
Use Case	Financial data, exact calculations	Scientific data, high range	Non-critical approximate values
Syntax	DECIMAL(M,D)	DOUBLE or DOUBLE PRECISION	FLOAT or FLOAT(p)
Rounding Errors	None	Possible	Likely

When to Use Each Data Type

We will use the Decimal value in the following scenarios -

• Financial transactions (e.g., prices, account balances).
• Scenarios requiring exact precision (e.g., tax calculations).

We will use the Double value in the following scenarios -

• Scientific measurements (e.g., astronomical distances).
• High-precision engineering data.

We will use the Floating point value in the following scenarios -

• Non-critical metrics (e.g., sensor readings).
• Large datasets where storage efficiency matters.

Example 1: Storing Financial Data with Decimal

CREATE TABLE transactions (id INT PRIMARY KEY, amount DECIMAL(10,2));  INSERT INTO transactions VALUES (1, 99999.99); 
SELECT * FROM transactions;

Output

+----+-----------+  
| id | amount    |  
+----+-----------+  
| 1  | 99999.99  |  
+----+-----------+

Example 2: Scientific Data with Double

CREATE TABLE experiments (experiment_id INT, result DOUBLE  ); 
INSERT INTO experiments VALUES (1, 0.00000012345);
SELECT result FROM experiments;

Output

+-------------+  
| result      |  
+-------------+  
| 0.000000123 |  
+-------------+

Example 3: Precision Issues with Float

CREATE TABLE demo (decimal_val DECIMAL(5,2), float_val FLOAT  );
INSERT INTO demo VALUES (1.00, 1.00);
UPDATE demo SET float_val = float_val + 0.1;
SELECT * FROM demo;

Output

+-------------+-----------+  
| decimal_val | float_val |  
+-------------+-----------+  
| 1.00        | 1.10      |  
+-------------+-----------+

Repeating 0.1 additions may lead to rounding errors in Float but not in Decimal.

Conclusion

Choose Decimal for exact arithmetic (e.g., money), Double for high-precision scientific data, and Float for storage-efficient approximations. Balancing precision, performance, and storage ensures optimal database design. Always test with real-world data to avoid unexpected rounding issues.