Learning Focus

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

MySQL FLOAT Data Type: Complete Guide

---

What is FLOAT?

FLOAT is MySQL's single-precision floating-point data type for storing approximate numeric values. It's ideal for scientific measurements or values where exact precision isn't critical.

Key Features

Feature	Details
Storage	4 bytes (32 bits)
Range	1.175494351E-38 to 3.402823466E+38
Precision	~7 significant digits
Synonyms	None
Default Value	NULL

---

When to Use FLOAT?

[OK] Best for:

• Scientific data (e.g., temperature readings, sensor data)
• Statistical calculations (e.g., averages, ratios)
• Game physics engines (velocity, coordinates)

[X] Avoid for:

• Financial calculations (use DECIMAL instead)
• Unique identifiers (e.g., primary keys)
• Data requiring exact precision

---

Syntax & Options

Basic Declaration

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

• M: Total digits (1-255)

• D: Decimal places (0-30)

• Example:

  temperature FLOAT(5,2)  -- Stores values like 123.45
  

Important Notes

• Omitting (M,D) uses full precision (~7 digits)
• UNSIGNED restricts to positive values
• ZEROFILL pads with zeros (deprecated in MySQL 8.0+)

---

Practical Examples

Example 1: Sensor Data

CREATE TABLE sensor_readings (
    reading_id INT AUTO_INCREMENT PRIMARY KEY,
    temperature FLOAT(5,2),  -- -99.99 to 999.99
    pressure FLOAT            -- Full precision
);

Example 2: Scientific Calculations

INSERT INTO measurements (value) VALUES (6.02214076e23);  -- Avogadro's number

Example 3: Rounding Behavior

SELECT FLOAT(5,2) = 123.4567;  -- Returns 123.46 -> TRUE

---

FLOAT vs DECIMAL vs DOUBLE

Feature	FLOAT	DECIMAL	DOUBLE
Precision	Approximate (~7 digits)	Exact	Approximate (~15 digits)
Storage	4 bytes	Variable	8 bytes
Use Case	Scientific data	Financial data	High-precision science
Rounding	Yes	No	Yes

---

Common Issues

1. Precision Loss

INSERT INTO accounts (balance) VALUES (123456.78);
-- Stored as 123457 (precision loss with FLOAT(9,0))

2. Comparison Errors

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

3. Overflow/Underflow

INSERT INTO physics (value) VALUES (3.5E38);  -- Exceeds FLOAT range -> Error

---

Best Practices

1. Avoid equality checks: Use ranges instead

   WHERE ABS(value - target) < 0.001
   

2. Specify precision when known: FLOAT(7,3) > FLOAT

3. Use UNSIGNED for positive-only values

4. Convert to DECIMAL for exact storage:

   CAST(float_column AS DECIMAL(10,2))
   

---

Performance Considerations

• Faster than DECIMAL for calculations
• Smaller storage than DOUBLE
• Avoid indexing: Precision issues cause unreliable comparisons

---

Summary

• FLOAT stores approximate 32-bit floating-point numbers
• Range: 3.4E+38 with ~7 significant digits
• Best for: Non-critical measurements, scientific data
• Watch for: Precision loss, rounding errors

---

Next Topics

1. DOUBLE (higher-precision floating-point)
2. BIT (binary data storage)
3. Numeric functions (ROUND(), ABS())

Concept Map

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

What's Next

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