> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Choosing MySQL Data Types

> How to choose the right MySQL data type for your columns: integers, strings, dates, and JSON — with storage costs and performance trade-offs.

> [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md)
>
> All examples on this page work on VillageSQL. Install Now →

The data type you assign to a column affects storage size, index efficiency, and query behavior. Choosing a type that's larger than necessary wastes space in every row and in every index that includes that column. Choosing a type that's too small causes truncation errors or silent data loss.

## Integer Types

MySQL has five integer types. Pick the smallest one that fits your expected range.

| Type | Storage | Range (signed) | Use when |
| :- | :- | :- | :- |
| `TINYINT` | 1 byte | -128 to 127 | Flags, status codes, small enumerations |
| `SMALLINT` | 2 bytes | -32,768 to 32,767 | Counts or IDs that won't exceed \~32K |
| `MEDIUMINT` | 3 bytes | -8.3M to 8.3M | Mid-range IDs or counters |
| `INT` | 4 bytes | -2.1B to 2.1B | Most IDs and counters |
| `BIGINT` | 8 bytes | ±9.2 quintillion | Large IDs, Unix timestamps in milliseconds |

`INT UNSIGNED` doubles the positive range to \~4.3B. Use `UNSIGNED` on primary keys and foreign keys that are always positive — it's free extra range.

`BIGINT` is twice the storage of `INT`. Don't default to it for all IDs — `INT` handles over 2 billion rows, which is more than most tables ever reach.

## String Types

### VARCHAR vs CHAR

`VARCHAR(n)` stores only as many bytes as the string contains (plus 1-2 bytes for length). `CHAR(n)` always stores exactly `n` bytes, padding shorter strings with spaces.

| Type | Storage | Use when |
| :- | :- | :- |
| `CHAR(n)` | Always n bytes | Fixed-length strings (country codes, status flags like 'Y'/'N', MD5 hashes) |
| `VARCHAR(n)` | Actual length + 1-2 bytes | Variable-length strings (names, emails, URLs) |

`CHAR` can be slightly faster for fixed-length values because no length prefix needs to be parsed. For anything variable, use `VARCHAR`.

### VARCHAR vs TEXT

```sql theme={null}
-- VARCHAR stores inline in the row (up to row size limit)
name VARCHAR(255)

-- TEXT stores off-page once it exceeds a threshold; inline storage is limited
description TEXT
```

`VARCHAR` values up to the row size limit are stored inline in the clustered index page. `TEXT` (and `BLOB`) values are stored in overflow pages once they're large enough. This means:

* `VARCHAR` columns are included in indexes directly
* `TEXT` columns can't be fully indexed — you need a prefix length: `INDEX (description(100))`

Use `VARCHAR` for strings you know are under a few hundred characters. Use `TEXT` for long content (articles, comments, HTML) where inline storage doesn't make sense.

## Date and Time Types

| Type | Storage | Range | Use when |
| :- | :- | :- | :- |
| `DATE` | 3 bytes | 1000-01-01 to 9999-12-31 | Date only, no time |
| `TIME` | 3 bytes | -838:59:59 to 838:59:59 | Duration or time of day |
| `DATETIME` | 8 bytes | 1000-01-01 to 9999-12-31 | Timestamps with no timezone conversion |
| `TIMESTAMP` | 4 bytes | 1970-01-01 to 2038-01-19 | Auto-updating timestamps; stored as UTC, displayed in session timezone |
| `YEAR` | 1 byte | 1901 to 2155 | Year only |

The key distinction between `DATETIME` and `TIMESTAMP`:

* `TIMESTAMP` stores values as UTC and converts to the session's `time_zone` on retrieval. If your application operates in multiple timezones, `TIMESTAMP` handles the conversion automatically.
* `DATETIME` stores the literal value with no timezone conversion — what you put in is what you get out.
* `TIMESTAMP` has a 2038 limit (Unix timestamp overflow). For any date beyond 2038, use `DATETIME`.

```sql theme={null}
-- TIMESTAMP auto-updates on insert and update
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
```

For a full discussion of timezone handling, see [Timestamps and Time Zones in MySQL](https://villagesql.com/docs/en/guides/timestamps-timezones.md).

## Decimal and Floating Point

| Type | Storage | Precision | Use when |
| :- | :- | :- | :- |
| `DECIMAL(p,s)` | Variable | Exact | Money, financial calculations — never use FLOAT for currency |
| `FLOAT` | 4 bytes | \~7 significant digits | Scientific data where approximate values are acceptable |
| `DOUBLE` | 8 bytes | \~15 significant digits | Higher-precision approximate values |

`FLOAT` and `DOUBLE` use binary floating-point, which can't represent decimal fractions exactly. `0.1 + 0.2` in floating-point is `0.30000000000000004`. For money, always use `DECIMAL`.

```sql theme={null}
-- Correct: exact decimal arithmetic
price DECIMAL(10, 2)   -- up to 99,999,999.99

-- Wrong: floating-point rounding errors will appear in financial calculations
price FLOAT
```

## JSON

`JSON` columns store validated JSON and provide path-based query operators.

```sql theme={null}
-- Store structured data without a fixed schema
CREATE TABLE events (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    payload JSON NOT NULL
);

-- Extract a field
SELECT payload->>'$.user_id' FROM events;

-- Filter on a nested field
SELECT * FROM events WHERE payload->>'$.event_type' = 'purchase';
```

`JSON` columns can't be indexed directly. To index a frequently-queried JSON field, use a generated column:

```sql theme={null}
ALTER TABLE events
    ADD COLUMN event_type VARCHAR(50) AS (payload->>'$.event_type') STORED,
    ADD INDEX idx_event_type (event_type);
```

## Frequently Asked Questions

#### Should I use INT or BIGINT for primary keys?

`INT` handles 2.1 billion rows as a signed integer, 4.3 billion unsigned. For most tables, `INT UNSIGNED` is enough. Use `BIGINT` if you genuinely expect to exceed that, or if you're using UUID-style IDs that don't fit in 4 bytes.

#### Can I use VARCHAR(255) everywhere to avoid thinking about it?

You can, but longer `VARCHAR` declarations produce wider index keys, which increases index memory usage and reduces how many index entries fit per page. Declare the realistic maximum — `VARCHAR(100)` for a name field, not `VARCHAR(255)`. The actual storage cost matches the data length, but the index key length is fixed at the declared size.

#### What's the practical difference between NULL and a default value?

`NULL` means "unknown" — it propagates through expressions and is excluded from aggregate functions. A default value like `0` or `''` means "explicitly set to empty." Pick `NULL` for genuinely absent data; pick a default for "not yet set" scenarios where you want the column to have a sensible value immediately. Mixing them creates inconsistency in queries.

## Troubleshooting

| Problem | Solution |
| :- | :- |
| `Data too long for column` | Column type is too narrow — increase `VARCHAR` length or switch to `TEXT` |
| Rounding errors in financial calculations | Replace `FLOAT`/`DOUBLE` with `DECIMAL(p,s)` |
| `TIMESTAMP` shows wrong time | Session `time_zone` differs from expected — check `SELECT @@session.time_zone` |
| Can't create index on TEXT column | Use a prefix index: `INDEX (col(100))`, or add a generated column |
| INT primary key approaching limit | Alter to `BIGINT` or `INT UNSIGNED` before hitting the ceiling |

## See also

* [Normalization in MySQL](https://villagesql.com/docs/en/guides/normalization.md) — schema design context for type decisions
* [JSON in MySQL](https://villagesql.com/docs/en/guides/json-in-mysql.md) — the JSON column type as a flexible alternative to rigid schemas
* [How MySQL Indexes Work](https://villagesql.com/docs/en/guides/mysql-indexes.md) — data type choice affects index size and efficiency
* [Multi-dimensional range queries](https://villagesql.com/docs/en/guides/cube-queries.md) — the cube type for storing and querying n-dimensional geometric data
* [MySQL vs PostgreSQL: Which Should You Use?](https://villagesql.com/docs/en/guides/mysql-vs-postgresql.md) — how the two databases differ on types and more
* [utf8mb4 in MySQL: Storing Emoji and Full Unicode](https://villagesql.com/docs/en/guides/utf8mb4.md) — column charsets and the four-byte reality
* [Real Booleans in MySQL](https://villagesql.com/docs/en/guides/boolean-columns.md) — a column type that refuses everything except true and false
* [MySQL SELECT](https://villagesql.com/docs/en/tutorial/select.md) — the tutorial lesson that reads these columns back


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.