MySQL data types

Type choices in MySQL affect storage, index limits and time zone behaviour in ways that differ from other databases.

The types you'll use most, with MySQL-specific notes:

Integers: TINYINT (1 byte) to BIGINT (8 bytes), optionally UNSIGNED to double the positive range. Use BIGINT UNSIGNED or BIGINT for ids of big tables. The display width in INT(11) is meaningless and deprecated.
Booleans: BOOL is an alias for TINYINT(1); TRUE is 1.
Exact numbers: DECIMAL(12,2) for money. FLOAT/DOUBLE are approximate.
Strings: VARCHAR(n) where n counts characters, stored inline with a length prefix; good for most text with a known bound. TEXT types (TEXT, MEDIUMTEXT, LONGTEXT) for long content; they can't have a default in older versions and can only be indexed with a prefix length. With utf8mb4, a character can take 4 bytes, which matters for index key limits (3072 bytes in InnoDB).
Dates: DATE, TIME, and two timestamp types:
DATETIME stores the literal date and time, no time zone conversion, years 1000–9999.
TIMESTAMP converts from the session time_zone to UTC on write and back on read, but its range ends in January 2038.
JSON for documents (later lesson), ENUM for small fixed sets, BINARY(16) for compact UUIDs.

Pick DATETIME for application timestamps and store them in UTC by convention, or use TIMESTAMP knowing the 2038 limit.

Example

CREATE TABLE payment (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    amount      DECIMAL(12,2) NOT NULL,
    currency    CHAR(3) NOT NULL,
    note        VARCHAR(255),
    refunded    BOOL NOT NULL DEFAULT FALSE,
    created_at  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP
);
INSERT INTO payment (amount, currency) VALUES (0.10, 'USD'), (0.20, 'USD');
SELECT SUM(amount) FROM payment;              -- 0.30 (exact DECIMAL)
SELECT refunded = FALSE FROM payment LIMIT 1;  -- 1

SET time_zone = '+00:00';
SELECT updated_at FROM payment LIMIT 1;       -- shown in UTC
SET time_zone = '+04:00';
SELECT updated_at FROM payment LIMIT 1;       -- same instant, shown 4 hours later

Remember

DECIMAL for money; BIGINT for large ids
DATETIME stores literal values; TIMESTAMP converts via time_zone and ends in 2038
VARCHAR(n) counts characters; utf8mb4 characters can take 4 bytes

Watch out for

Using TIMESTAMP for dates far in the future, like contract end dates after 2038.

Check yourself

A column must store dates up to the year 2100. Which type works?

Official docs: dev.mysql.com/doc/refman/8.4/en/data-types.html

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks