Numeric Types in MySQL: INT, BIGINT, DECIMAL, FLOAT, and DOUBLE

Choosing the correct numeric type affects:

• Storage size: Using a larger type than necessary wastes disk space.
• Precision: Financial calculations need exact values, while scientific data may allow approximations.
• Performance: Smaller types often process faster.

Understanding the distinctions between these types helps developers make efficient and accurate database designs.

1. INT (Integer)

INT is the most commonly used integer type in MySQL. It stores whole numbers within a defined range.

• Storage: 4 bytes
• Range:
• Signed: -2,147,483,648 to 2,147,483,647
• Unsigned: 0 to 4,294,967,295

Example:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    age INT,
    salary INT
);

INSERT INTO employees (emp_id, age, salary) VALUES (1, 30, 50000);

Step-by-step analysis:

1. emp_id is defined as INT to uniquely identify each employee.
2. age and salary are integers because they don’t require fractions.
3. Using INT here ensures fast storage and retrieval.

2. BIGINT

BIGINT is used for very large integers, beyond the range of INT.

• Storage: 8 bytes
• Range:
• Signed: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
• Unsigned: 0 to 18,446,744,073,709,551,615

Example:

CREATE TABLE transactions (
    transaction_id BIGINT PRIMARY KEY,
    amount BIGINT
);

INSERT INTO transactions (transaction_id, amount) VALUES (123456789012345, 1000000000);

Step-by-step analysis:

• transaction_id uses BIGINT because transaction identifiers can exceed INT’s range.
• amount can also be large, and using BIGINT prevents overflow.

When to use BIGINT: IDs for large datasets, counters, or very high-value amounts.

3. DECIMAL (Exact Fixed-Point)

DECIMAL is ideal for storing exact numeric values, especially for financial calculations where precision is critical.

• Storage: Varies based on precision
• Syntax: DECIMAL(M, D)
• M = total digits
• D = digits after the decimal

Example:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    price DECIMAL(10,2)
);

INSERT INTO products (product_id, price) VALUES (1, 199.99);

Step-by-step analysis:

1. DECIMAL(10,2) allows up to 10 digits, with 2 digits after the decimal point.
2. price stores exact monetary values. Unlike FLOAT, DECIMAL avoids rounding errors.

When to use DECIMAL: Financial transactions, accounting, and precise measurements.

4. FLOAT (Approximate Floating-Point)

FLOAT stores approximate numeric values using 4 bytes. It’s suitable for scientific calculations where small rounding errors are acceptable.

• Storage: 4 bytes
• Precision: Approx. 7 decimal digits

Example:

CREATE TABLE measurements (
    measurement_id INT PRIMARY KEY,
    temperature FLOAT
);

INSERT INTO measurements (measurement_id, temperature) VALUES (1, 36.6);

Step-by-step analysis:

1. temperature can tolerate small rounding errors.
2. FLOAT uses less storage than DOUBLE, making it efficient for large datasets.

When to use FLOAT: Sensor data, approximate scientific values, or non-financial decimals.

5. DOUBLE (Approximate Floating-Point with Higher Precision)

DOUBLE is similar to FLOAT but provides double precision, storing numbers with higher accuracy.

• Storage: 8 bytes
• Precision: Approx. 15 decimal digits

Example:

CREATE TABLE physics_data (
    experiment_id INT PRIMARY KEY,
    velocity DOUBLE
);

INSERT INTO physics_data (experiment_id, velocity) VALUES (1, 299792458.123456);

Step-by-step analysis:

1. velocity requires high precision for scientific calculations.
2. DOUBLE minimizes rounding errors over large ranges, unlike FLOAT.

When to use DOUBLE: Physics simulations, engineering calculations, or high-precision measurements.

Comparison Table