Learning Focus

Use this lesson to understand DECIMAL with practical syntax and examples.

MySQL DECIMAL Data Type: Complete Guide

What is DECIMAL?

The DECIMAL data type is MySQL's solution for storing exact numeric values with fixed precision, making it ideal for financial calculations, monetary values, and other scenarios where precision is critical.

Key Features

Feature	Description
Alternative Names	NUMERIC, FIXED (exact synonyms in MySQL)
Storage	Variable (4 bytes per 9 digits + overhead)
Precision	Up to 65 total digits
Scale	Up to 30 decimal places
Accuracy	Exact (no floating-point rounding errors)
Default Format	DECIMAL(10,0)

DECIMAL Syntax

DECIMAL(M,D) [UNSIGNED] [ZEROFILL]

• M = Total number of digits (precision, 1-65)
• D = Number of digits after decimal point (scale, 0-30)

Examples

price DECIMAL(10,2)        -- $999,999.99
tax_rate DECIMAL(4,3)      -- 0.125
population DECIMAL(12,0)   -- 7,889,123,456

When to Use DECIMAL?

[OK] Best for:

• Financial amounts (prices, salaries, transactions)
• Scientific measurements requiring exact precision
• Any calculation where rounding errors are unacceptable

[X] Avoid for:

• Approximate values (use FLOAT/DOUBLE)
• Very large numbers where exact precision isn't needed
• Scientific calculations needing wide range over precision

Storage Requirements

Precision (Digits)	Storage (Bytes)
1-9	4
10-18	8
19-27	12
28-36	16
37-45	20
46-54	24
55-65	28

Practical Examples

Financial Application

CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    amount DECIMAL(13,4) UNSIGNED,  -- $999,999,999.9999
    fee DECIMAL(10,2) UNSIGNED,
    net_amount DECIMAL(13,4) GENERATED ALWAYS AS (amount - fee) STORED
);

Scientific Measurement

CREATE TABLE lab_results (
    sample_id VARCHAR(20) PRIMARY KEY,
    weight DECIMAL(8,5),      -- 123.45678 grams
    concentration DECIMAL(7,6) -- 0.999999 purity
);

DECIMAL vs FLOAT/DOUBLE

Feature	DECIMAL	FLOAT/DOUBLE
Accuracy	Exact	Approximate
Storage	More efficient for precision	More efficient for range
Use Case	Financial data	Scientific computations
Rounding	None	Possible rounding errors

Important Behaviors

Rounding Rules

DECIMAL truncates by default when inserting values with excess decimals:

INSERT INTO products (price) VALUES (19.998); -- Stored as 19.99 for DECIMAL(10,2)

Arithmetic Operations

SELECT (0.1 + 0.2) = 0.3;  -- Returns 1 (TRUE) with DECIMAL, 0 with FLOAT

Performance Considerations

• DECIMAL math is slower than FLOAT/DOUBLE
• Indexes on DECIMAL columns are larger than integer indexes
• Consider BIGINT as alternative (store cents as integers)

Best Practices

1. Choose appropriate precision:
   • Prices: DECIMAL(10,2)
   • Percentages: DECIMAL(5,2)
   • Large quantities: DECIMAL(15,0)
2. Use UNSIGNED when negative values aren't possible
3. Avoid ZEROFILL (deprecated in MySQL 8.0+)
4. Consider storage impact - DECIMAL(65,30) requires 28 bytes per value

Common Mistakes

-- Problem: Insufficient precision
DECIMAL(5,2) for $1,000,000.00  -- Too small!

-- Problem: Excessive precision
DECIMAL(65,30) for product prices  -- Wasteful!

-- Problem: Using DECIMAL when FLOAT would suffice
DECIMAL(10,10) for scientific probabilities

Migration Considerations

When converting from FLOAT/DOUBLE to DECIMAL:

1. Expect data changes due to rounding differences
2. May need to modify application code
3. Queries might return different results

Summary

• DECIMAL provides exact numeric storage
• Essential for financial applications
• Requires careful precision/scale planning
• More storage-intensive than floating-point types

1. FLOAT/DOUBLE data types next?
2. Numeric functions for DECIMAL?
3. Or proceed to another data type category?

Concept Map

flowchart LR
    A[Schema Context] --> B[DECIMAL]
    B --> C[Query Pattern]
    C --> D[Validation]
    D --> E[Production Use]

Common Pitfalls

Pitfall	Consequence	Prevention
Executing queries without validating sample rows	Logic errors reach production data or reports	Start with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behavior	Aggregations and filters return misleading results	Test with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schema	Queries fail due to missing columns/indexes	Verify structure with DESCRIBE table_name; and adapt query design

Quick Reference

CREATE TABLE sample_decimal (id INT PRIMARY KEY, value DECIMAL);
DESCRIBE sample_table;
SHOW CREATE TABLE sample_table;

What's Next

• Previous: BIGINT - Review the previous lesson to reinforce context.
• Next: FLOAT - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.