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