Pick types for range and storage: INT/BIGINT, DECIMAL for money, DATETIME vs TIMESTAMP, JSON, TEXT.
Common types
- INT, BIGINT — integers; UNSIGNED if non-negative only
- DECIMAL(p,s) — exact money (avoid FLOAT for currency)
- VARCHAR(n), TEXT — strings; VARCHAR for indexed shorter fields
- DATETIME, TIMESTAMP — TIMESTAMP is UTC storage with session TZ display
- JSON — validated JSON documents (5.7+)
Examples
CREATE TABLE ledger (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
amount DECIMAL(12,2) NOT NULL,
posted_at DATETIME NOT NULL,
meta JSON
);Practice: Run on database practice in mysql client.
BOOLEAN
BOOLEAN is alias for TINYINT(1)—0/1 in practice.
Important interview questions and answers
- Q: FLOAT for money?
A: Rounding errors—use DECIMAL or integer cents. - Q: TIMESTAMP vs DATETIME?
A: TIMESTAMP converts to UTC; DATETIME stores literal value.
Self-check
- Which type for exact currency?
- What is BOOLEAN under the hood?
Tip: DECIMAL for money; avoid FLOAT currency.
Interview prep
- Money type?
DECIMAL(p,s)—not FLOAT for currency.
- BOOLEAN?
Alias for TINYINT(1).