Examples

-- 1) Schema with sensible defaults
CREATE TABLE customers (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email      VARCHAR(190) NOT NULL,
  name       VARCHAR(120) NOT NULL,
  created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE orders (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id  BIGINT UNSIGNED NOT NULL,
  status       ENUM('new','paid','shipped','cancelled') NOT NULL DEFAULT 'new',
  total_cents  BIGINT UNSIGNED NOT NULL,
  details      JSON,                                       -- arbitrary structured data
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_customer_status_created (customer_id, status, created_at DESC),
  CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

-- 2) Upsert (INSERT ... ON DUPLICATE KEY UPDATE)
INSERT INTO customers (email, name)
VALUES ('alice@example.com', 'Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- 3) JSON column queries (MySQL 8 has rich JSON support)
INSERT INTO orders (customer_id, total_cents, details) VALUES
  (1, 4995, JSON_OBJECT('source','web','utm','spring','items',
              JSON_ARRAY(JSON_OBJECT('sku','sku-1','qty',2)))),
  (1, 9990, JSON_OBJECT('source','app','utm','summer','items',
              JSON_ARRAY(JSON_OBJECT('sku','sku-2','qty',1))));

-- Select a JSON path
SELECT id, JSON_EXTRACT(details, '$.source') AS source FROM orders;
-- Shorthand operator (->>) returns text, -> returns JSON
SELECT id, details->>'$.utm' AS utm FROM orders;

-- Filter on a JSON field, indexed via a generated column (next example)
SELECT id FROM orders WHERE JSON_EXTRACT(details, '$.source') = 'web';

-- 4) Generated columns — index a JSON field
ALTER TABLE orders
  ADD COLUMN source VARCHAR(32)
    GENERATED ALWAYS AS (details->>'$.source') STORED,
  ADD KEY ix_source (source);

-- Now the planner can use the index
EXPLAIN SELECT id FROM orders WHERE source = 'web';

-- 5) Window function for rolling revenue
SELECT id, created_at, total_cents,
       SUM(total_cents) OVER (PARTITION BY customer_id ORDER BY created_at
                              ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d
FROM orders
ORDER BY customer_id, created_at;

-- 6) Bonus — CTEs + window function for top-N per group
WITH ranked AS (
  SELECT id, customer_id, total_cents,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_cents DESC) AS rn
  FROM orders
)
SELECT id, customer_id, total_cents FROM ranked WHERE rn <= 3;

-- 7) Replace OR UPDATE (REPLACE INTO is DELETE+INSERT and resets autoincrement; AVOID)
-- Prefer ON DUPLICATE KEY UPDATE OR an explicit UPDATE.

-- 8) Bulk insert chunking — split very large inserts
-- INSERT INTO ... VALUES (...),(...),... up to 1000-5000 rows per statement
-- Lower lock contention than 100k single-row inserts.

-- 9) Performance tips
-- - utf8mb4 (4-byte UTF-8) for full Unicode support; avoid the deprecated utf8 (3-byte).
-- - Always include a primary key (InnoDB rows are stored in PK order).
-- - InnoDB cluster index: queries that USE the PK are basically free seeks.
-- - Composite indexes follow filter -> sort -> range order, same as Postgres.

-- Common everyday MySQL recipes — see the lesson body.