Decimal and floating-point types in MySQL - Deep Dive

Overview - Decimal and floating-point types

What is it?

Decimal and floating-point types are ways to store numbers with fractions in a database. Decimal types store exact numbers with fixed decimal places, while floating-point types store approximate numbers that can handle very large or very small values. These types help databases manage numbers like prices, measurements, or scientific data.

Why it matters

Without decimal and floating-point types, databases would struggle to store numbers with fractions accurately or efficiently. For example, prices in money calculations need exact values to avoid errors, while scientific data may require very large or very small numbers. These types solve the problem of balancing precision and range in storing fractional numbers.

Where it fits

Before learning decimal and floating-point types, you should understand basic data types like integers and strings. After this, you can learn about numeric functions, rounding, and how calculations work in SQL queries.

Mental Model

Core Idea

Decimal types store exact fractional numbers with fixed precision, while floating-point types store approximate numbers with variable precision to cover a wide range.

Think of it like...

Think of decimal types like a ruler with fixed marks for measuring exactly in centimeters, and floating-point types like a zoom lens that can focus on very small or very large objects but sometimes blurs the details.

Number Storage Types
┌───────────────┬───────────────────────────────┐
│ Decimal       │ Fixed decimal places, exact   │
│               │ values (e.g., money)          │
├───────────────┼───────────────────────────────┤
│ Floating-Point│ Approximate, wide range, uses  │
│               │ scientific notation internally │
└───────────────┴───────────────────────────────┘

Build-Up - 6 Steps

1

Foundation

Understanding Numeric Data Types

Concept: Introduce basic numeric types and why fractional numbers need special types.

Databases store numbers in different ways. Integers store whole numbers without fractions. But many real-world numbers have fractions, like 3.14 or 99.99. To store these, databases use special types: decimal and floating-point. They handle fractions differently to balance accuracy and size.

Result

Learners understand why integers are not enough for fractional numbers and the need for decimal and floating-point types.

Knowing the limitation of integers sets the stage for why decimal and floating-point types exist.

2

Foundation

What is the Decimal Type?

Concept: Decimal type stores numbers exactly with fixed decimal places.

The decimal type stores numbers as exact values with a fixed number of digits before and after the decimal point. For example, DECIMAL(5,2) means 5 digits total, with 2 after the decimal point, like 123.45. This is perfect for money where exact values matter.

Result

Learners see how decimal stores exact fractional numbers and how to define precision and scale.

Understanding fixed precision explains why decimal is reliable for exact calculations.

3

Intermediate

What is Floating-Point Type?

🤔Before reading on: do you think floating-point stores numbers exactly or approximately? Commit to your answer.

Concept: Floating-point stores numbers approximately using scientific notation internally.

Floating-point types (FLOAT, DOUBLE) store numbers using a format like scientific notation. This allows very large or very small numbers but can introduce small rounding errors. For example, 0.1 might be stored as 0.100000001. This is useful for scientific data but not for money.

Result

Learners understand floating-point's approximate nature and its tradeoff between range and precision.

Knowing floating-point's approximation helps avoid surprises in calculations and data storage.

4

Intermediate

Choosing Between Decimal and Floating-Point

🤔Before reading on: which type would you choose for storing currency values? Decimal or floating-point? Commit to your answer.

Concept: Different use cases require choosing the right type based on precision and range needs.

Use DECIMAL when you need exact precision, like money or counts. Use FLOAT or DOUBLE when you need to store very large or very small numbers and can tolerate small rounding errors, like scientific measurements. Choosing the wrong type can cause errors or inefficiency.

Result

Learners can decide which type fits their data needs and avoid common mistakes.

Understanding use cases prevents costly errors in financial or scientific applications.

5

Advanced

How MySQL Stores Decimal and Floating-Point

🤔Before reading on: do you think MySQL stores DECIMAL as binary floating-point internally? Commit to your answer.

Concept: MySQL stores DECIMAL as a string of digits for exactness, and FLOAT/DOUBLE as binary floating-point.

MySQL stores DECIMAL values as strings of digits, not binary floats, to keep exact precision. FLOAT and DOUBLE use binary floating-point format, which can cause rounding errors. This internal difference explains why DECIMAL is exact but uses more space.

Result

Learners understand the internal storage differences that affect precision and performance.

Knowing internal storage clarifies why decimal is exact but slower, and floating-point is fast but approximate.

6

Expert

Surprising Floating-Point Behavior in Queries

🤔Before reading on: do you think comparing floating-point numbers for equality in SQL always works as expected? Commit to your answer.

Concept: Floating-point comparisons can fail due to tiny rounding differences, causing unexpected query results.

Because floating-point numbers are approximate, comparing them with '=' can fail even if they look equal. For example, WHERE value = 0.1 might not match rows because 0.1 is stored as 0.100000001 internally. Instead, use range checks or ROUND functions to compare safely.

Result

Learners avoid bugs caused by floating-point equality comparisons in SQL queries.

Understanding floating-point quirks prevents subtle bugs in database filtering and calculations.

Under the Hood

DECIMAL stores numbers as strings of digits internally, preserving exact decimal values by recording each digit. FLOAT and DOUBLE store numbers in binary floating-point format, representing numbers as a sign, exponent, and mantissa. This binary format can only approximate many decimal fractions, causing rounding errors.

Why designed this way?

DECIMAL was designed to provide exact precision for financial and fixed-point calculations, avoiding rounding errors. FLOAT and DOUBLE were designed to efficiently store a wide range of values for scientific and engineering use, accepting small precision loss for performance and range.

Storage Mechanism
┌───────────────┬───────────────────────────────┐
│ DECIMAL       │ Stores digits as strings       │
│               │ Exact decimal representation   │
├───────────────┼───────────────────────────────┤
│ FLOAT/DOUBLE  │ Stores binary floating-point   │
│               │ Sign | Exponent | Mantissa    │
│               │ Approximate decimal values     │
└───────────────┴───────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think DECIMAL and FLOAT store numbers the same way internally? Commit to yes or no.

Common Belief: DECIMAL and FLOAT both store numbers as binary floating-point internally.

Reality: DECIMAL stores numbers as exact strings of digits, while FLOAT uses binary floating-point format.

Why it matters: Believing they store numbers the same leads to unexpected rounding errors when using FLOAT, or unnecessary performance costs when using DECIMAL.

Quick: Is it safe to compare floating-point numbers with '=' in SQL? Commit to yes or no.

Common Belief: Floating-point numbers can be safely compared for equality using '=' in SQL queries.

Reality: Floating-point numbers often cannot be reliably compared for equality due to tiny rounding differences.

Why it matters: Using '=' for floating-point comparison can cause queries to miss matching rows, leading to bugs and incorrect results.

Quick: Do you think DECIMAL always uses less storage than FLOAT? Commit to yes or no.

Common Belief: DECIMAL always uses less storage space than FLOAT or DOUBLE.

Reality: DECIMAL often uses more storage because it stores exact digits, while FLOAT uses compact binary format.

Why it matters: Choosing DECIMAL without considering storage can lead to inefficient database size and slower performance.

Quick: Can floating-point types represent all decimal fractions exactly? Commit to yes or no.

Common Belief: Floating-point types can represent all decimal fractions exactly.

Reality: Many decimal fractions cannot be exactly represented in binary floating-point, causing small errors.

Why it matters: Assuming exact representation causes subtle bugs in calculations and data comparisons.

Expert Zone

1

DECIMAL precision and scale affect storage size and performance; choosing them wisely balances accuracy and efficiency.

2

Floating-point rounding errors accumulate in calculations, so repeated operations can increase inaccuracies.

3

MySQL's handling of floating-point literals in queries can differ from stored values, causing unexpected behavior.

When NOT to use

Avoid FLOAT and DOUBLE for financial or exact calculations; use DECIMAL instead. Avoid DECIMAL for very large scientific data needing wide range; use FLOAT/DOUBLE. For approximate analytics, consider using FLOAT for performance.

Production Patterns

In production, DECIMAL is standard for money and fixed-point data. FLOAT/DOUBLE are used for scientific measurements, sensor data, or analytics where range matters more than exact precision. Developers often combine DECIMAL with rounding functions to ensure accuracy.

Connections

Binary Number System

Floating-point types use binary representation internally.

Understanding binary numbers helps explain why floating-point cannot exactly represent many decimal fractions.

Financial Accounting

Decimal types are essential for exact money calculations in accounting.

Knowing accounting needs clarifies why exact decimal storage is critical to avoid rounding errors in money.

Scientific Measurement

Floating-point types support wide range needed in scientific data.

Recognizing scientific data requirements explains why approximate floating-point storage is acceptable despite small errors.

Common Pitfalls

#1 Comparing floating-point numbers directly for equality in SQL.

Wrong approach:

SELECT * FROM measurements WHERE value = 0.1;

Correct approach:

SELECT * FROM measurements WHERE ABS(value - 0.1) < 0.00001;

Root cause:

Misunderstanding that floating-point numbers are approximate and may not match exactly.

#2 Using FLOAT type for storing currency values.

Wrong approach:

CREATE TABLE prices (amount FLOAT);

Correct approach:

CREATE TABLE prices (amount DECIMAL(10,2));

Root cause:

Not realizing FLOAT can introduce rounding errors unsuitable for money.

#3 Assuming DECIMAL always uses less storage than FLOAT.

Wrong approach:

Choosing DECIMAL(30,10) for large data without considering size.

Correct approach:

Choosing appropriate DECIMAL precision and scale to balance size and accuracy.

Root cause:

Ignoring how DECIMAL stores digits as strings, which can increase storage.

Key Takeaways

Decimal types store exact fractional numbers with fixed precision, ideal for money and exact calculations.

Floating-point types store approximate numbers using binary format, suitable for scientific data with wide range.

Floating-point numbers cannot be reliably compared for equality due to rounding errors; use range checks instead.

Choosing the right type depends on the need for precision versus range and storage efficiency.

Understanding internal storage helps prevent bugs and optimize database design.