Learning Focus

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

MySQL DOUBLE Data Type: Complete Guide

---

What is DOUBLE?

DOUBLE is MySQL's double-precision floating-point data type for storing approximate numeric values with higher precision than FLOAT. It's designed for scientific or engineering applications requiring more significant digits.

Key Features

Feature	Details
Storage	8 bytes (64 bits)
Range	2.2250738585072014E-308 to 1.7976931348623157E+308
Precision	~15 significant digits
Synonyms	REAL (when not in precision mode)
Default Value	NULL

---

When to Use DOUBLE?

[OK] Best for:

• High-precision scientific data (e.g., physics simulations)
• Engineering calculations (e.g., structural stress analysis)
• Geospatial coordinates (latitude/longitude with decimals)
• Machine learning datasets (neural network weights)

[X] Avoid for:

• Financial calculations (use DECIMAL)
• Unique identifiers or counts (use integer types)
• Scenarios requiring exact decimal representation

---

Syntax & Options

Basic Declaration

column_name DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

• M: Total digits (1-255)

• D: Decimal places (0-30)

• Example:

  coordinates DOUBLE(10,8)  -- Latitude: 40.7127753
  

Important Notes

• Omitting (M,D) uses full double-precision (~15 digits)
• UNSIGNED restricts to positive values
• ZEROFILL is deprecated in MySQL 8.0+

---

Practical Examples

Example 1: Geospatial Data

CREATE TABLE locations (
    id INT PRIMARY KEY,
    latitude DOUBLE(10,8),  -- 40.7127753
    longitude DOUBLE(11,8)  -- -74.0059728
);

Example 2: Scientific Constants

INSERT INTO physics_constants (name, value)
VALUES ('Planck', 6.62607015e-34);

Example 3: Rounding Behavior

SELECT DOUBLE(8,3) = 12345.6789;  -- Returns 12345.679 -> TRUE

---

DOUBLE vs FLOAT vs DECIMAL

Feature	DOUBLE	FLOAT	DECIMAL
Precision	~15 digits	~7 digits	Exact
Storage	8 bytes	4 bytes	Variable
Speed	Fast	Faster	Slowest
Use Case	High-precision science	General science	Finance

---

Common Issues

1. Precision Loss

INSERT INTO stars (distance_km) VALUES (9460730472580.8);
-- Stored as 9460730472580.8 -> Exact? NO! (Try SELECT to see rounding)

2. Comparison Errors

SELECT 0.1 + 0.2 = 0.3;  -- Returns FALSE with DOUBLE

3. Overflow/Underflow

INSERT INTO quantum (value) VALUES (1.8e308);  -- Exceeds DOUBLE range -> Error

---

Best Practices

1. Avoid equality checks: Use tolerance ranges

   WHERE ABS(a - b) < 0.0000001
   

2. Use DECIMAL for money:

   price DECIMAL(10,2) -- NOT DOUBLE!
   

3. Specify precision when practical:

   DOUBLE(12,6) -- For GPS coordinates
   

---

Performance Considerations

• Faster than DECIMAL: Use for non-exact calculations
• Larger than FLOAT: 8 bytes vs 4 bytes
• Indexing challenges: Floating-point inaccuracies affect uniqueness

---

Summary

• DOUBLE stores 64-bit floating-point numbers with ~15-digit precision
• Range: 1.7E+308 (massive!)
• Best for: Scientific/engineering apps needing more precision than FLOAT
• Watch for: Precision limits, rounding errors

---

Next Topics

1. BIT (compact binary storage)
2. Numeric Functions (ROUND(), TRUNCATE())
3. Type Conversion (CAST/CONVERT)

Concept Map

flowchart LR
    A[Schema Context] --> B[DOUBLE]
    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_double (id INT PRIMARY KEY, value DOUBLE);
DESCRIBE sample_table;
SHOW CREATE TABLE sample_table;

What's Next

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