MySQL Data Types Explained — Which Type to Use and When

Numeric Types

MySQL's numeric types split into two groups: exact types (INT, BIGINT, DECIMAL) and approximate types (FLOAT, DOUBLE). The distinction matters most for financial data. The MySQL 8.0 Reference Manual documents that FLOAT and DOUBLE use IEEE 754 binary representation, which can't precisely store most decimal fractions — making them unsafe for any monetary column.

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.3 billion).
• Use BIGINT UNSIGNED for tables that may grow very large.
• Never use FLOAT or DOUBLE for monetary values. Floating-point imprecision causes rounding errors in financial calculations. Use DECIMAL(10, 2) instead.

String Types

VARCHAR covers most string storage needs. It's variable-width: MySQL allocates only what the content requires, plus 1-2 bytes of length overhead. CHAR, by contrast, always occupies exactly n bytes — a 10-character value in a CHAR(50) column still consumes all 50 bytes. That fixed width makes CHAR marginally faster to index, which is why the MySQL 8.0 Reference Manual recommends it for fixed-length data like ISO country codes (CHAR(2)) or MD5 hashes (CHAR(32)).

Key rules:

• Use VARCHAR(n) for most string columns. Set n to a realistic maximum: VARCHAR(255) for names and emails, VARCHAR(2048) for URLs.
• Use CHAR(n) only when the value is always the same length. It's marginally faster to index than VARCHAR.
• Don't put TEXT columns in WHERE clauses without a full-text index. It forces a full table scan regardless of how specific your filter is.
• Avoid ENUM. It's inflexible to alter and opaque to external tools. Use a VARCHAR with a CHECK constraint, or a separate lookup table instead.

Date and Time Types

The TIMESTAMP versus DATETIME decision has real production consequences. TIMESTAMP stores every value in UTC and reconverts to the session timezone on retrieval, making it automatic for audit columns like created_at. Its hard upper limit is 2038-01-19 — the point where a 32-bit Unix timestamp overflows. Any application storing future dates past that boundary must use DATETIME, which supports dates through year 9999 (MySQL 8.0 Reference Manual).

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 ceiling. For future dates beyond that, use DATETIME.

JSON Type

MySQL 5.7 introduced native JSON storage; MySQL 8.0 expanded the operator set considerably. A JSON column validates syntax on insert and rejects malformed data outright, preventing the silent corruption that storing JSON in a TEXT column allows. Path expressions use dot notation: data->'$.key' is shorthand for JSON_EXTRACT(data, '$.key'), and the ->> operator returns an unquoted scalar value directly.

-- Read a single JSON path value
SELECT config->'$.theme' FROM user_settings WHERE user_id = 1;

-- Unquoted scalar (MySQL 5.7.13+)
SELECT config->>'$.theme' FROM user_settings WHERE user_id = 1;

-- Filter rows by JSON value
SELECT * FROM users WHERE preferences->>'$.notifications' = 'true';

Use JSON for truly variable or schema-less data: feature flags, user preferences, integration payloads. Don't use it to avoid modelling your data properly. If the same key appears in every row, it should be a regular column. JSON columns can't be indexed directly — only generated columns derived from JSON paths can be indexed — so querying inside JSON at scale requires careful planning. See our database schema examples for how to mix JSON columns with conventional structure.

What Are the Most Common MySQL Data Type Mistakes?

Three type selection errors show up repeatedly in production schemas. They're easy to avoid at design time and expensive to fix once a table has millions of rows.

1. FLOAT or DOUBLE for monetary values. This is the most damaging one. A column defined as FLOAT can't store 0.1 exactly — it stores the nearest IEEE 754 approximation. Sum enough rows and the total drifts from the correct value. Use DECIMAL(10, 2) for any price, fee, or financial amount. No exceptions. The database normalization guide covers how this fits into a well-structured financial schema.

2. TEXT columns in WHERE clauses without a FULLTEXT index. A query like WHERE body LIKE '%keyword%' against a TEXT column does a full table scan every time. On a table with 50,000 rows that's slow; on a table with 5 million rows it's a timeout. Add a FULLTEXT index, store a searchable excerpt in a VARCHAR column, or move search to a dedicated tool.

3. ENUM instead of a lookup table. ENUM enforces allowed values at the database level, which sounds useful. The problem is ALTER TABLE. Adding a new ENUM value in MySQL 5.x rewrites the entire table — a blocking operation on a live database. In MySQL 8.0 this is instant for appended values, but the opaque storage still confuses ORMs and external tooling. A VARCHAR(50) with a CHECK constraint, or a foreign key to a status_types lookup table, is far more maintainable. Your future self will thank you when the business adds a new status at 11pm on a Friday.

Quick Reference: Common Column Patterns

• Primary key: INT UNSIGNED NOT NULL AUTO_INCREMENT
• Large-table primary key: BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
• UUID / GUID: CHAR(36) NOT NULL (or BINARY(16) with UUID_TO_BIN() in MySQL 8.0+ for storage efficiency)
• Email address: VARCHAR(255) NOT NULL UNIQUE
• Password hash: VARCHAR(255) NOT NULL
• URL slug: 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

Video Resource