MySQL Numeric Data Types

Numeric data types are used to store numbers in MySQL — whole numbers like a user ID or product count, and decimal numbers like a price or salary. MySQL offers several numeric types, each designed for a specific range and precision. Choosing the right one means your data takes up less space and your queries run faster.

---

Integer Types

Integer types store whole numbers — no decimal point. MySQL provides five integer sizes, each holding a different range of values. The larger the range, the more storage space the column needs.

Type	Storage	Signed Range	Unsigned Range
TINYINT	1 byte	-128 to 127	0 to 255
SMALLINT	2 bytes	-32,768 to 32,767	0 to 65,535
MEDIUMINT	3 bytes	-8,388,608 to 8,388,607	0 to 16,777,215
INT	4 bytes	-2,147,483,648 to 2,147,483,647	0 to 4,294,967,295
BIGINT	8 bytes	-9.2 quintillion to 9.2 quintillion	0 to 18.4 quintillion

In practice, INT is the most commonly used integer type. It handles over two billion positive values, which is more than enough for most applications. Use BIGINT only when you genuinely need numbers larger than two billion — for example, high-volume transaction IDs or social media like counters.

---

The UNSIGNED Modifier

By default, integer columns are signed — they can store both negative and positive values. Adding the UNSIGNED keyword removes the ability to store negative numbers, but doubles the positive range.

CREATE TABLE users (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    age TINYINT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
);

Use UNSIGNED whenever a column should never hold a negative value — IDs, counts, ages, quantities. It is a simple change that doubles your usable range at no extra cost.

---

DECIMAL — Exact Decimal Numbers

DECIMAL(p, s) stores exact decimal values. The first parameter p is the total number of digits (precision), and the second parameter s is how many of those digits appear after the decimal point (scale).

price DECIMAL(10, 2)

This means up to 10 digits total, with exactly 2 after the decimal point. So it can store values like 12345678.99 — a maximum of 8 digits before the point and 2 after. This is the standard choice for storing prices, salaries, tax amounts, and any financial value.

---

FLOAT and DOUBLE — Approximate Decimal Numbers

FLOAT and DOUBLE store decimal numbers too, but they use floating-point representation — which means they are approximate, not exact. This makes them suitable for scientific or engineering data where tiny rounding differences do not matter, but unsuitable for money.

Type	Storage	Precision	Use For
FLOAT	4 bytes	~7 significant digits	Scientific measurements, GPS lat/lng
DOUBLE	8 bytes	~15 significant digits	High-precision scientific data
DECIMAL	Variable	Exact (up to 65 digits)	Financial values, prices, salaries

---

BOOLEAN — True or False

MySQL does not have a dedicated boolean type. Instead, BOOLEAN and BOOL are aliases for TINYINT(1). MySQL stores TRUE as 1 and FALSE as 0.

CREATE TABLE posts (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    is_published BOOLEAN NOT NULL DEFAULT FALSE,
    PRIMARY KEY (id)
);

You can insert and query using TRUE / FALSE keywords, but MySQL internally treats them as 1 and 0.

---

Practical Example

Here is a realistic orders table that puts several numeric types to use:

CREATE TABLE orders (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id INT UNSIGNED NOT NULL,
    quantity SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    unit_price DECIMAL(10, 2) NOT NULL,
    discount DECIMAL(5, 2) DEFAULT 0.00,
    is_paid BOOLEAN NOT NULL DEFAULT FALSE,
    PRIMARY KEY (id)
);

• id and customer_id — unsigned integers, never negative.
• quantity — a small positive number, so SMALLINT UNSIGNED is sufficient.
• unit_price and discount — exact decimals for financial accuracy.
• is_paid — a boolean flag stored as TINYINT(1).

---

Key Points to Remember

• Use INT for general-purpose whole numbers; use BIGINT only when values exceed ~2 billion.
• Add UNSIGNED to any integer column that should never be negative — it doubles the positive range.
• Use DECIMAL(p, s) for all financial values — it is exact.
• FLOAT and DOUBLE are approximate — suitable for scientific data, not money.
• BOOLEAN is just an alias for TINYINT(1) in MySQL.
• Always choose the smallest type that fits your data to save storage and improve speed.