Data Types

-- ===== Integers =====
TINYINT     -- 1 byte, -128..127
SMALLINT    -- 2 bytes
MEDIUMINT   -- 3 bytes
INT         -- 4 bytes
BIGINT      -- 8 bytes
-- UNSIGNED variants double the upper range

CREATE TABLE orders (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  total_cents  BIGINT NOT NULL,
  units        INT NOT NULL
) ENGINE=InnoDB;

-- ===== Money: DECIMAL only =====
DECIMAL(p, s)   -- exact; DECIMAL(12, 2) for AUD with 2 dp
-- NEVER use FLOAT/DOUBLE for money.

ALTER TABLE orders MODIFY total DECIMAL(12, 2) NOT NULL;

-- ===== Strings =====
CHAR(n)     -- fixed length, padded with spaces (rarely the right tool)
VARCHAR(n)  -- variable length up to n; n is in CHARACTERS not bytes
TEXT        -- up to 64KB
MEDIUMTEXT  -- up to 16MB
LONGTEXT    -- up to 4GB

-- VARCHAR vs TEXT: VARCHAR is stored inline (faster for small fields);
-- TEXT lives off-page. Use VARCHAR for short fields with a real upper bound,
-- TEXT for free-form long content.

CREATE TABLE comments (
  id     BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  body   TEXT NOT NULL,
  email  VARCHAR(254) NOT NULL    -- RFC max
);

-- Character set + collation matter:
ALTER TABLE comments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
-- utf8mb4 is required for emoji and full Unicode; default since MySQL 8.

-- ===== Dates =====
DATE         -- 3 bytes, '1000-01-01' to '9999-12-31'
DATETIME     -- 8 bytes, NO timezone storage; range as DATE
TIMESTAMP    -- 4 bytes (until 2038), automatic UTC conversion
TIME         -- time of day

-- Pick TIMESTAMP when you want UTC normalisation; DATETIME when you store local-time
-- intentionally. After 2038, only DATETIME survives without migration.
ALTER TABLE orders ADD COLUMN created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

-- ===== JSON (MySQL 5.7+) =====
CREATE TABLE profiles (
  user_id BIGINT UNSIGNED PRIMARY KEY,
  data    JSON NOT NULL
);

-- Read a path:
SELECT data->>'$.email' FROM profiles WHERE user_id = 1;

-- Index a path with a generated column + index:
ALTER TABLE profiles
  ADD country VARCHAR(2) GENERATED ALWAYS AS (data->>'$.country') STORED,
  ADD INDEX (country);

-- ===== Enum and Set =====
ENUM('new', 'paid', 'shipped')   -- one of a fixed set, stored as 1-byte index
SET('a','b','c')                 -- subset of values; bitmap storage

-- Enums are fine for truly fixed sets; lookup tables age better when the set changes.

-- ===== Booleans =====
BOOLEAN          -- alias for TINYINT(1); 0/1

-- ===== UUID storage =====
-- Native UUID type does not exist; common patterns:
--  1. CHAR(36) — readable, indexable, larger
--  2. BINARY(16) + UUID_TO_BIN(uuid, true)/BIN_TO_UUID()  — compact, slightly ordered

CREATE TABLE users (
  id     BINARY(16) PRIMARY KEY,
  email  VARCHAR(254) NOT NULL UNIQUE
);
INSERT INTO users (id, email) VALUES (UUID_TO_BIN(UUID(), TRUE), 'a@x.io');

-- ===== Generated columns =====
ALTER TABLE orders
  ADD COLUMN total_aud DECIMAL(12, 2) AS (total_cents / 100) VIRTUAL;

-- ===== Patterns to internalise =====
-- - utf8mb4 always; never plain utf8 (which is 3 bytes max, breaks emoji)
-- - DECIMAL for money; never FLOAT/DOUBLE
-- - VARCHAR(n) for short, TEXT for long; mind the index key length (utf8mb4 = 4*n)
-- - TIMESTAMP for UTC-normalised, DATETIME for local-time intentional storage
-- - BINARY(16) for UUIDs once you outgrow CHAR(36)
-- - JSON for sparse / variable shapes; generated columns + indexes for queryable paths

-- ===== Pitfalls =====
-- - VARCHAR(255) on a utf8mb4 column with InnoDB row format limits -> key prefix errors
-- - 0000-00-00 dates (legacy MySQL allows them) -> ZERO_DATE in strict mode breaks queries
-- - FLOAT total_cents -> rounding errors
-- - ENUM you outgrow -> ALTER TABLE rewrites the column for every change
-- - JSON without indexed generated columns -> full table scans on every search

-- INT, BIGINT, DECIMAL(10,2), FLOAT, DOUBLE
-- VARCHAR(n), TEXT, CHAR(n)
-- DATE, DATETIME, TIMESTAMP, TIME, YEAR
-- JSON, ENUM('a','b'), SET('x','y')