MySQL Data Types

Master MySQL data types — the three families (numeric, date/time, string) plus JSON and spatial. Covers integer types and UNSIGNED, the DECIMAL vs FLOAT money rule, DATETIME vs TIMESTAMP and the Y2038 problem, VARCHAR/CHAR/TEXT character vs byte limits, ENUM and SET, JSON support, and a comprehensive choose-the-right-type cheat sheet. Flags deprecated syntax (display widths, FLOAT(M,D), YEAR(2)).

A data type tells MySQL what kind of value can live in a column — an integer, a string of up to 100 characters, a date, a fraction with two decimal places, a JSON document. Picking the right type matters because it determines:

• What values are allowed — the database rejects anything that doesn't fit.
• How much storage the column uses — and therefore how big your tables grow.
• How fast queries are — narrower types fit more rows per page; arithmetic on integers is faster than on strings.
• What operations work — you can SUM() a number, but not a VARCHAR; you can DATE_ADD() a date, but not an integer.

MySQL data types divide into three broad families, plus a couple of specialized types:

Integer Types

Five integer sizes, each available as SIGNED (the default — allows negatives) or UNSIGNED (positive only, doubles the maximum):

Fixed-Point and Floating-Point Types

Boolean

MySQL doesn't have a real boolean type. BOOL and BOOLEAN are synonyms for TINYINT(1) — they store 0 (false) or any non-zero (true). You'll see this surprise newcomers: SELECT TRUE = 1; returns 1.

Fractional Seconds

Since MySQL 5.6.4, DATETIME, TIMESTAMP, and TIME can store fractional seconds with up to microsecond precision. Specify the precision in parentheses:

CREATE TABLE event_log (
    id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    happened_at DATETIME(6)    NOT NULL,    -- microseconds
    response_ms TIME(3)                     -- milliseconds
);

DATETIME vs TIMESTAMP — The Big Choice

Variable vs Fixed Length

Big Text — TEXT Family

Binary Strings — BLOB Family

Enumerated and Set Types

MySQL has two type-safe ways to constrain a column to a fixed set of string values:

CREATE TABLE accounts (
    id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    status      ENUM('pending','active','suspended','closed') NOT NULL DEFAULT 'pending',
    roles       SET('user','editor','admin') NOT NULL DEFAULT 'user'
);

INSERT INTO accounts (status, roles)
VALUES ('active', 'user,editor');

SELECT * FROM accounts;

Since MySQL 5.7, JSON is a first-class type. It validates that values are well-formed JSON, stores them in an efficient binary format, and exposes a family of JSON_*() functions for querying and modifying nested data.

CREATE TABLE products (
    id     BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name   VARCHAR(100) NOT NULL,
    specs  JSON
);

INSERT INTO products (name, specs)
VALUES ('Wireless mouse', '{"color":"black","weight_g":85,"buttons":3}');

-- Query into the JSON
SELECT name, JSON_EXTRACT(specs, '$.color') AS color
FROM   products
WHERE  JSON_EXTRACT(specs, '$.weight_g') < 100;

For geographic data, MySQL has the OGC-standard spatial types — GEOMETRY (the catch-all), POINT, LINESTRING, POLYGON, and the MULTI* versions. With spatial indexes, you can run "find restaurants within 5 km" queries efficiently. Niche but powerful when you need it.

CREATE TABLE customers (
    id              BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,    -- big integer, no negatives
    email           VARCHAR(255)    NOT NULL UNIQUE,               -- variable string
    full_name       VARCHAR(100)    NOT NULL,
    country_code    CHAR(2)         NOT NULL,                       -- fixed: ISO codes are always 2 chars
    age             TINYINT UNSIGNED,                              -- 0-255 covers any human age
    balance         DECIMAL(12, 2)  NOT NULL DEFAULT 0,            -- money — always DECIMAL
    status          ENUM('pending','active','suspended','closed')
                    NOT NULL DEFAULT 'pending',
    preferences     JSON,                                          -- flexible per-user settings
    created_at      DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    last_login_at   DATETIME(6),
    bio             TEXT,                                          -- long, variable
    avatar          MEDIUMBLOB                                     -- binary, ~16 MB max
);

Notice the deliberate type choices: BIGINT UNSIGNED for an ID that needs to count high, CHAR(2) for the always-2-character country code, TINYINT UNSIGNED for age (saves bytes vs INT), DECIMAL for money, JSON for the open-ended preferences blob.

• MySQL data types fall into three big families — numeric, date/time, and string — plus JSON and spatial for special cases.
• Pick the smallest integer that fits; use UNSIGNED when values can never be negative — it doubles your range for the same storage.
• Money is DECIMAL, never FLOAT — floating point can't represent simple decimals exactly.
• Default to DATETIME for timestamps; reach for TIMESTAMP only when UTC conversion is desired and dates won't pass 2038.
• Default to VARCHAR; use CHAR only for fixed-length codes; use TEXT for long text where row-size limits would otherwise be exceeded.
• Watch the deprecation surface: integer (N) display widths, FLOAT(M,D) / DOUBLE(M,D), and YEAR(2) are all deprecated or removed in modern MySQL — drop them.
• ENUM for a small fixed set of values; a separate lookup table when the set changes often; JSON for genuinely variable structure.