MySQL Numeric Types

Also Known As

TL;DR

Explanation

Integer types: TINYINT (1B), SMALLINT (2B), MEDIUMINT (3B), INT (4B, ~2.1B signed), BIGINT (8B). UNSIGNED doubles the positive range. DECIMAL(p,s) stores exact decimal values using string-based arithmetic — use for money and prices. FLOAT (4B) and DOUBLE (8B) are IEEE 754 approximate values — never use for currency as 0.1 + 0.2 ≠ 0.3. Use DECIMAL(10,2) for prices, BIGINT for large counters, INT UNSIGNED for most IDs.

Common Misconception

Why It Matters

Common Mistakes

• Using FLOAT or DOUBLE for prices, totals, or tax calculations.
• Using signed INT when UNSIGNED would double the available range for no cost.
• Not switching to BIGINT before an INT column approaches its limit — the ALTER TABLE is expensive on large tables.

Avoid When

• Never use FLOAT or DOUBLE for financial values — use DECIMAL.
• Avoid signed integer types for IDs — UNSIGNED doubles the range at zero cost.

When To Use

• Use DECIMAL for any monetary or precise decimal value.
• Use BIGINT UNSIGNED for primary keys on tables expected to exceed hundreds of millions of rows.

Code Examples

-- FLOAT for money: rounding errors accumulate
price FLOAT NOT NULL,
-- INT for high-volume event log: overflows at ~2.1B rows
id INT AUTO_INCREMENT PRIMARY KEY

CREATE TABLE products (
    id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- up to 4.2B
    price     DECIMAL(10,2) NOT NULL,                  -- exact: 99999999.99
    quantity  MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,   -- up to 16.7M
    weight_kg DECIMAL(8,3)                             -- exact decimal
);

CREATE TABLE events (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY -- up to 18.4 quintillion
);

References

• ↗ https://dev.mysql.com/doc/refman/8.0/en/numeric-types.html

Tags

Related Terms