MySQL data types

Picking the right data type matters. It affects storage size, query performance, and how MySQL validates data on insert. This reference covers the types you will use most, with practical advice on when to choose each one.

Numeric types

MySQL has several integer types, each with a different storage size and range.

| 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 / INTEGER | 4 bytes | -2,147,483,648 to 2,147,483,647 | 0 to 4,294,967,295 | | BIGINT | 8 bytes | -9.2 × 10¹⁸ to 9.2 × 10¹⁸ | 0 to 1.8 × 10¹⁹ |

INT is the right default for most primary keys and foreign keys. The 2-billion ceiling is plenty for the vast majority of applications. Use BIGINT when you genuinely expect billions of rows, such as event logs, analytics tables, or high-volume transactional systems.

TINYINT and SMALLINT make sense for columns with a known small range, like a rating from 1 to 5 or an age in years. The storage savings are minor, but they add a layer of implicit constraint.

Auto-increment IDs

CREATE TABLE orders (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id INT UNSIGNED NOT NULL,
  total_cents INT NOT NULL,
  PRIMARY KEY (id)
);

Using UNSIGNED on an auto-increment column doubles the available range without any cost. For INT UNSIGNED, that is 4.2 billion rows. If that is not enough, switch to BIGINT UNSIGNED.

Floating-point and decimal types

| Type | Storage | Use case | |---|---|---| | FLOAT | 4 bytes | Approximate values, scientific data | | DOUBLE | 8 bytes | Higher-precision approximate values | | DECIMAL(p, s) | Varies | Exact values: money, measurements |

FLOAT and DOUBLE are approximate. They use binary floating-point, which means 0.1 + 0.2 may not equal exactly 0.3. This is fine for sensor readings or scientific calculations where a small rounding error is acceptable.

DECIMAL stores exact values. Use it for currency and anything where rounding errors are unacceptable. DECIMAL(10, 2) stores up to 10 digits total, with 2 after the decimal point, which covers values up to 99,999,999.99.

CREATE TABLE products (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name        VARCHAR(255) NOT NULL,
  price_usd   DECIMAL(10, 2) NOT NULL,
  weight_kg   FLOAT,
  PRIMARY KEY (id)
);

String types

CHAR vs VARCHAR

CHAR(n) is fixed-length. MySQL always stores exactly n characters, padding shorter values with spaces. VARCHAR(n) is variable-length and stores only the characters you insert, plus 1 or 2 bytes for the length prefix.

| Type | Storage | Best for | |---|---|---| | CHAR(n) | Always n bytes | Fixed-length codes: country codes, UUIDs, hashes | | VARCHAR(n) | Actual length + 1–2 bytes | Most text: names, emails, titles |

CHAR can be slightly faster for columns where every row has the same length, because MySQL can calculate row offsets without reading length prefixes. For most use cases, the difference is not meaningful. Use VARCHAR(255) as your default for short strings.

CREATE TABLE users (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email        VARCHAR(255) NOT NULL,
  country_code CHAR(2) NOT NULL,
  password_hash CHAR(60) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY (email)
);

TEXT types

When a VARCHAR is not large enough, MySQL provides four TEXT types.

| Type | Max length | |---|---| | TINYTEXT | 255 bytes | | TEXT | 65,535 bytes (~64 KB) | | MEDIUMTEXT | 16,777,215 bytes (~16 MB) | | LONGTEXT | 4,294,967,295 bytes (~4 GB) |

Use TEXT for long-form content: blog post bodies, comments, descriptions. Use MEDIUMTEXT or LONGTEXT only when you expect content that exceeds 64 KB.

Note that MySQL cannot use a TEXT column as a primary key or in a UNIQUE constraint without specifying a prefix length. You also cannot give a TEXT column a default value.

BLOB types

BLOB types mirror the TEXT types but store binary data: TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB. In practice, storing files in a database is usually a mistake. Use object storage (S3, GCS, etc.) and store the URL in a VARCHAR column instead.

Date and time types

| Type | Storage | Format | Range | |---|---|---|---| | DATE | 3 bytes | YYYY-MM-DD | 1000-01-01 to 9999-12-31 | | TIME | 3 bytes | HH:MM:SS | -838:59:59 to 838:59:59 | | DATETIME | 8 bytes | YYYY-MM-DD HH:MM:SS | 1000-01-01 to 9999-12-31 | | TIMESTAMP | 4 bytes | YYYY-MM-DD HH:MM:SS | 1970-01-01 to 2038-01-19 | | YEAR | 1 byte | YYYY | 1901 to 2155 |

DATETIME vs TIMESTAMP

This is the most important distinction in the date/time group.

TIMESTAMP stores values in UTC and converts them to the current session time zone on retrieval. It also has a smaller range (up to 2038) and costs half the storage of DATETIME.

DATETIME stores values exactly as you give them, with no time zone conversion. What you put in is what you get out.

For most applications, DATETIME is the better default. You avoid silent conversions when the session time zone changes, and you are not limited to dates before 2038. Handle time zone logic in your application layer where the rules are explicit.

Use TIMESTAMP when you want automatic tracking of row creation or update times:

CREATE TABLE articles (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  title        VARCHAR(255) NOT NULL,
  body         MEDIUMTEXT NOT NULL,
  published_at DATETIME,
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

Here published_at is a DATETIME because it is a user-controlled value that should not shift with time zones. The created_at and updated_at columns use TIMESTAMP for automatic tracking.

Boolean

MySQL has no native boolean type. It maps BOOLEAN and BOOL to TINYINT(1). The values TRUE and FALSE are aliases for 1 and 0.

CREATE TABLE feature_flags (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  flag_name  VARCHAR(100) NOT NULL,
  is_enabled TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

-- Both of these work
INSERT INTO feature_flags (flag_name, is_enabled) VALUES ('dark_mode', TRUE);
INSERT INTO feature_flags (flag_name, is_enabled) VALUES ('beta_ui', 1);

BIT(1) is an alternative that stores a single bit and only accepts 0 or 1. It has stricter validation but less readable query output and inconsistent handling across client libraries. TINYINT(1) is more portable.

JSON type

MySQL 5.7 added a native JSON type. Unlike storing JSON in a TEXT column, the JSON type validates the document on insert and stores it in an optimized binary format that allows efficient reads of individual keys.

CREATE TABLE events (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  event_type VARCHAR(100) NOT NULL,
  payload    JSON NOT NULL,
  occurred_at DATETIME NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO events (event_type, payload, occurred_at)
VALUES ('user.signup', '{"user_id": 42, "plan": "pro", "referral": null}', NOW());

-- Extract a value
SELECT payload->>'$.plan' AS plan FROM events WHERE id = 1;

You can index specific paths within a JSON column using generated columns:

ALTER TABLE events
  ADD COLUMN user_id BIGINT GENERATED ALWAYS AS (payload->>'$.user_id') STORED,
  ADD INDEX idx_user_id (user_id);

Use JSON when your data is genuinely schemaless or when different rows need different attributes. Avoid it as a substitute for proper schema design. If every row has the same five keys, those should be real columns.

ENUM and SET

ENUM restricts a column to a specific list of string values. MySQL stores the value as a small integer internally, so storage is efficient.

CREATE TABLE tickets (
  id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  status   ENUM('open', 'in_progress', 'resolved', 'closed') NOT NULL DEFAULT 'open',
  priority ENUM('low', 'medium', 'high') NOT NULL DEFAULT 'medium',
  PRIMARY KEY (id)
);

SET is similar but allows a column to hold multiple values from the list at once, stored as a bitmask.

CREATE TABLE notifications (
  id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  channels SET('email', 'sms', 'push') NOT NULL DEFAULT 'email',
  PRIMARY KEY (id)
);

The trap with both types: adding a new value requires an ALTER TABLE, which can be slow and lock the table on older MySQL versions (before 8.0 online DDL improvements). If the list of values changes often, a separate lookup table with a foreign key is more flexible.

Choosing the right type

| Situation | Recommended type | |---|---| | Primary key, expected < 2 billion rows | INT UNSIGNED AUTO_INCREMENT | | Primary key, expected billions of rows | BIGINT UNSIGNED AUTO_INCREMENT | | Money / exact decimals | DECIMAL(p, s) | | Short text (names, emails, titles) | VARCHAR(255) | | Long text (articles, comments) | TEXT or MEDIUMTEXT | | Fixed-length codes (country codes, hashes) | CHAR(n) | | Dates without times | DATE | | Timestamps for audit fields | TIMESTAMP DEFAULT CURRENT_TIMESTAMP | | All other date/time values | DATETIME | | Boolean flags | TINYINT(1) | | Schemaless or variable attributes | JSON | | Column with a small fixed set of values | ENUM |

The guiding principle: use the smallest type that covers your data without artificial constraints. This keeps storage efficient, keeps indexes small, and lets MySQL validate your data at the schema level rather than in application code.