How do you choose data types in MySQL, and what are common pitfalls?

Question

IT Interview Practice · Accepted Answer

Pick the **smallest type that correctly holds every value** you will ever store. The right type saves disk, shrinks indexes, and prevents silent data corruption.

## Numbers

- `INT` (4 bytes) for most ids; `BIGINT` (8 bytes) only when you truly outgrow ~2.1 billion.
- `DECIMAL(10,2)` for money — **never** `FLOAT`/`DOUBLE`, which round in binary and lose cents.

## Strings

- `VARCHAR(n)` for bounded text (names, emails); the `n` is a cap, storage is only what you use.
- `TEXT` for large blobs (articles) — it is stored off-page and **cannot have a default**; don't use it for short values you filter on.

## Dates

- `DATETIME` (no timezone, 8 bytes) vs `TIMESTAMP` (UTC-converted, 4 bytes, ~2038 limit).

```sql
CREATE TABLE payments (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  amount      DECIMAL(10,2) NOT NULL,   -- exact money, not FLOAT
  currency    CHAR(3)       NOT NULL,   -- always 3 chars -> fixed CHAR
  note        VARCHAR(255),             -- bounded, indexable
  created_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Pitfall: the default SQL mode rejects bad values (good) --
INSERT INTO payments (amount) VALUES ('abc');  -- ERROR 1366, not silently 0
```

## Common pitfalls

- Using `FLOAT` for money.
- `VARCHAR(255)` everywhere out of habit — oversized keys bloat indexes.
- Storing dates as strings, which kills range queries and sorting.
- Forgetting `UNSIGNED` on ids/counts that are never negative (doubles the positive range).

## Why it matters

Data types are the cheapest correctness and performance decision you make, and the hardest to change later — narrow, exact types keep indexes small and stop bad data at the door before it becomes a production bug.

Numbers

Strings

Dates

Common pitfalls

Why it matters

Related questions