March 2026 — 7 min read

MySQL Data Types Explained — Which to Use and When

Choosing the right data type for each column is one of the most important decisions in database design. The wrong choice costs you storage, hurts query performance, and can introduce subtle data integrity bugs. This guide covers the most important MySQL data types and when to reach for each one.

Numeric Types

Type	Storage	Range (signed)	Use for
`TINYINT`	1 byte	-128 to 127	Booleans, small status flags
`SMALLINT`	2 bytes	-32,768 to 32,767	Small counters, age, year
`INT`	4 bytes	-2.1B to 2.1B	General-purpose IDs and counts
`BIGINT`	8 bytes	±9.2 quintillion	High-volume tables, snowflake IDs
`DECIMAL(p,s)`	Variable	Exact	Money, measurements requiring precision
`FLOAT` / `DOUBLE`	4 / 8 bytes	Approximate	Scientific values where small errors are acceptable

Key rules:

Use TINYINT(1) for booleans — MySQL's conventional boolean representation.
Use INT UNSIGNED for auto-increment primary keys (doubles the positive range to ~4.3B).
Use BIGINT UNSIGNED for tables that may grow very large.
Never use FLOAT or DOUBLE for monetary values — floating-point imprecision will cause rounding errors in financial calculations. Use DECIMAL(10, 2) instead.

String Types

Type	Max size	Use for
`CHAR(n)`	255 chars	Fixed-length strings: country codes, hashes, status enums
`VARCHAR(n)`	65,535 bytes	Variable-length strings: names, emails, URLs, titles
`TEXT`	65,535 bytes	Long content: descriptions, comments, HTML
`MEDIUMTEXT`	16 MB	Large documents, article bodies
`LONGTEXT`	4 GB	Very large content (logs, serialised data)

Key rules:

Use VARCHAR(n) for most string columns. Set n to a realistic maximum — VARCHAR(255) is common for names and emails, VARCHAR(2048) for URLs.
Use CHAR(n) only when the value is always the same length (e.g., ISO country codes CHAR(2), MD5 hashes CHAR(32)). It's marginally faster to index than VARCHAR.
Don't put TEXT columns in WHERE clauses without a full-text index — it forces a table scan.
Avoid ENUM — it's inflexible to alter and opaque to external tools. Use a VARCHAR with a CHECK constraint, or a separate lookup table.

Date and Time Types

Type	Range	Use for
`DATE`	1000-01-01 to 9999-12-31	Dates without time: birthdays, deadlines
`DATETIME`	1000-01-01 to 9999-12-31	Timestamps stored in application timezone
`TIMESTAMP`	1970-01-01 to 2038-01-19	Audit timestamps stored in UTC, auto-converted on retrieval
`TIME`	-838:59:59 to 838:59:59	Durations, time-of-day without date
`YEAR`	1901 to 2155	Year-only values

Key rules:

Use TIMESTAMP for created_at and updated_at audit columns — it auto-converts to UTC on storage and back to the session timezone on retrieval.
Use DATETIME when you need to store a specific wall-clock time without timezone conversion (e.g., a scheduled event that should fire at 9am regardless of timezone).
TIMESTAMP has a 2038 limit — for future dates beyond that, use DATETIME.

JSON Type

MySQL 5.7+ supports a native JSON column type that validates JSON on insert and enables path-based queries with JSON_EXTRACT() and the -> operator.

SELECT config->'$.theme' FROM user_settings WHERE user_id = 1;

Use JSON for truly variable or schema-less data — feature flags, user preferences, integration payloads. Don't use it as a way to avoid modelling your data properly: if the same key appears in every row, it should be a column.

A Quick Reference: Common Use Cases

Primary key: INT UNSIGNED NOT NULL AUTO_INCREMENT
Email address: VARCHAR(255) NOT NULL UNIQUE
Password hash: VARCHAR(255) NOT NULL
Price / monetary value: DECIMAL(10, 2) NOT NULL
Boolean flag: TINYINT(1) NOT NULL DEFAULT 0
Created/updated timestamps: TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
Long-form text: TEXT
Foreign key: Same type as the referenced primary key, e.g. INT UNSIGNED NOT NULL

Apply these data types in your schema

SQL Designer lets you pick MySQL data types from a dropdown as you design your tables visually. Free, no installation required.

Create a Free Account