Data Types
Picking between INT sizes, DECIMAL and FLOAT, VARCHAR and TEXT, ENUM, JSON, and the TINYINT(1) that pretends to be a boolean.
col_name {TINYINT|INT|BIGINT|DECIMAL(M,D)|FLOAT|DOUBLE|CHAR(M)|VARCHAR(M)|TEXT|ENUM(...)|DATE|DATETIME|JSON} [UNSIGNED]A column's type decides what it can hold, how much space a row costs, and how comparisons behave. MySQL 8.4 groups types into numeric, string, date and time, JSON, and spatial.
Integers
| Type | Bytes | Signed range |
|---|---|---|
| TINYINT | 1 | -128 to 127 |
| SMALLINT | 2 | -32768 to 32767 |
| MEDIUMINT | 3 | -8388608 to 8388607 |
| INT | 4 | -2147483648 to 2147483647 |
| BIGINT | 8 | about -9.2e18 to 9.2e18 |
Adding UNSIGNED shifts the range upward: INT UNSIGNED reaches 4294967295.
Exact versus approximate
DECIMAL(M,D) stores exact values — up to 65 total digits, of which at most 30 are after the point. FLOAT and DOUBLE are approximate binary types, so 0.1 is not stored as 0.1. Money goes in DECIMAL, or in an integer count of minor units. Never in FLOAT.
Shorthand and longhand
INT = INTEGER. DEC, NUMERIC and FIXED = DECIMAL. BOOL and BOOLEAN = TINYINT(1), and TRUE/FALSE are literally 1/0.
Strings and structure
VARCHAR(M) is stored inline and counts against the 65,535-byte row limit; TEXT (65,535 characters), MEDIUMTEXT and LONGTEXT are stored away from the row. ENUM allows up to 65,535 members and is stored internally as an integer. JSON validates and stores documents in a binary form.
Example
CREATE TABLE products (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
sku VARCHAR(40) NOT NULL, -- short, indexed, inline
title VARCHAR(255) NOT NULL,
description TEXT NULL, -- long prose, off-row
price DECIMAL(10,2) NOT NULL, -- exact: 99999999.99 max
weight_kg DOUBLE NULL, -- measurement, approximation is fine
stock INT UNSIGNED NOT NULL DEFAULT 0,
visibility ENUM('draft','listed','hidden') NOT NULL DEFAULT 'draft',
is_featured TINYINT(1) NOT NULL DEFAULT 0, -- BOOL and BOOLEAN are the same type
attributes JSON NULL, -- validated on write
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uniq_products_sku (sku)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;When to use it
- Choose a money column that will not drift by a cent after a year of aggregation.
- Decide whether a status column should be ENUM or a foreign key to a lookup table.
- Size an id column before a table grows past 4.2 billion rows.
- Store a third-party API payload without inventing a column per field.
More examples
Two safe ways to store money, and one unsafe one
DECIMAL is stored as an exact value and added exactly; DOUBLE is binary floating point, so decimal fractions are approximations and the error compounds across a SUM. Use this when you have to justify to a colleague why the ledger total is off by a fraction of a cent.
CREATE TABLE payments (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
amount_exact DECIMAL(12,2) NOT NULL, -- exact decimal arithmetic
amount_cents BIGINT NOT NULL, -- integer minor units
amount_bad DOUBLE NOT NULL -- do not do this
);
INSERT INTO payments (amount_exact, amount_cents, amount_bad)
VALUES (0.10, 10, 0.10), (0.20, 20, 0.20), (0.30, 30, 0.30);
SELECT SUM(amount_exact), -- 0.60, always
SUM(amount_cents), -- 60, always
SUM(amount_bad) -- 0.6000000000000001 or similar
FROM payments;ENUM is an integer wearing a string costume
ENUM gives you a small storage footprint and ordering that follows business meaning rather than the alphabet. The cost is that every change to the member list is a schema change — if the list is edited by users rather than developers, use a lookup table and a foreign key instead.
CREATE TABLE tickets (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal'
);
-- Members are stored as index numbers 1..N, so ORDER BY sorts by
-- declaration order, not alphabetically: low, normal, high, urgent.
SELECT id, priority FROM tickets ORDER BY priority;
-- Adding a member at the end is cheap; inserting one in the middle
-- renumbers the members after it.
ALTER TABLE tickets
MODIFY priority ENUM('low','normal','high','urgent','critical')
NOT NULL DEFAULT 'normal';Indexing inside a JSON column
JSON is the right type for genuinely variable payloads, but a JSON column is not indexable on its own. Extract the fields you filter on into generated columns and index those; the query planner can then use the index while the document stays authoritative.
CREATE TABLE events (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
payload JSON NOT NULL,
-- JSON columns cannot be indexed directly; index a generated column
user_id BIGINT UNSIGNED
GENERATED ALWAYS AS (payload->>'$.user_id') STORED,
KEY idx_events_user (user_id)
);
-- payload->>'$.user_id' is shorthand for
-- JSON_UNQUOTE(JSON_EXTRACT(payload, '$.user_id'))
SELECT id FROM events WHERE user_id = 42;
Discussion