MySQL - FLOAT

MySQL Data Types: FLOAT vs. DECIMAL

MySQL provides several numeric data types for storing numbers. Among them, FLOAT is used for approximate-value floating-point numbers. While it has its uses, it comes with a major caveat: imprecision. This tutorial explains the FLOAT data type, its limitations, and when you should use the precise DECIMAL data type instead.

The MySQL FLOAT Data Type

The FLOAT data type stores single-precision floating-point numbers. It is an ‘approximate’ numeric type, meaning it may not store the exact value you provide, especially for fractional numbers. It uses 4 bytes of storage.

Note: As of MySQL 8.0.17, the UNSIGNED attribute for FLOAT and other floating-point types is deprecated and will be removed in a future version. You should not use it in new designs.

Syntax and Example

CREATE TABLE scientific_measurements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(100),
    temperature FLOAT
);

We can verify the column type with DESC:

DESC scientific_measurements;

Field	Type	Null	Key	Default	Extra
id	int	NO	PRI	NULL	auto_increment
event_name	varchar(100)	YES		NULL
temperature	float	YES		NULL

The Problem with Approximation

The core issue with FLOAT (and DOUBLE) stems from how computers store floating-point numbers using binary (IEEE 754 standard). Many decimal fractions cannot be represented perfectly in binary, leading to small rounding errors. This is unacceptable for financial calculations.

Example of Imprecision

Consider a table for financial transactions:

CREATE TABLE transactions_float (
    amount FLOAT
);
INSERT INTO transactions_float VALUES (0.1), (0.2);

SELECT SUM(amount) FROM transactions_float;
-- Expected Result: 0.3
-- Actual Result: 0.30000001192092896

This tiny error can accumulate in large calculations, leading to significant discrepancies. Never use FLOAT or DOUBLE for storing monetary values.

The Solution: The DECIMAL Data Type

For scenarios requiring exact precision, MySQL provides the DECIMAL (or its synonym NUMERIC) data type. It stores numbers as strings, preserving their exact value.

Syntax: DECIMAL(P, S)

• P (Precision): The total number of digits (both left and right of the decimal point). Maximum is 65.
• S (Scale): The number of digits to the right of the decimal point. Must be less than or equal to P.

Correcting Our Example with DECIMAL

-- For currency, a common choice is DECIMAL(10, 2)
-- This allows for values up to 99,999,999.99
CREATE TABLE transactions_decimal (
    amount DECIMAL(10, 2)
);

INSERT INTO transactions_decimal VALUES (0.10), (0.20);

SELECT SUM(amount) FROM transactions_decimal;
-- Correct Result: 0.30

When to Use FLOAT vs. DECIMAL

Scenario	Recommended Type	Reason
Monetary values, financial data, accounting	DECIMAL	Requires absolute precision. Rounding errors are unacceptable.
Scientific measurements, engineering data	FLOAT or DOUBLE	Data often has inherent measurement error, so slight imprecision is acceptable. Faster calculations and less storage.
Calculations where performance is critical and slight imprecision is tolerable	FLOAT or DOUBLE	Floating-point arithmetic is handled by the CPU’s hardware, making it faster than decimal arithmetic.

Using Numeric Types in Client Programs

When fetching numeric data in your application, be aware of how your language handles it. Most languages will map SQL FLOAT to a native float type and DECIMAL to a specific high-precision decimal object or a string to avoid precision loss.

Python
Java



# The Python `mysql-connector-python` library can fetch DECIMAL types as Python's native `Decimal` object.
import mysql.connector
from decimal import Decimal

# When inserting, use strings or Decimal objects for precision.
insert_query = "INSERT INTO transactions_decimal (amount) VALUES (%s)"
cursor.execute(insert_query, ("29.99",))

# When fetching, ensure the connector is configured to not use native floats for decimals.
cursor = conn.cursor(raw=False, use_decimal=True)
cursor.execute("SELECT amount FROM transactions_decimal")
for row in cursor:
    # row['amount'] will be a Decimal object, not a float
    print(f"Amount: {row['amount']}, Type: {type(row['amount'])}")



// In Java, JDBC maps SQL DECIMAL/NUMERIC to java.math.BigDecimal, which is a high-precision type.
// SQL FLOAT/DOUBLE map to Java's float/double.

String sql = "SELECT amount FROM transactions_decimal WHERE id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, 123);

ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
    // Use getBigDecimal to retrieve DECIMAL values without precision loss.
    java.math.BigDecimal amount = rs.getBigDecimal("amount");
    System.out.println("Transaction Amount: " + amount.toString());
}