MySQL: InnoDB, JSON, CTEs and Window Functions Reference Guide

# macOS — Homebrew
brew install mysql
brew services start mysql

# Linux — Debian/Ubuntu
sudo apt install mysql-server
sudo systemctl enable --now mysql

# Docker — disposable dev instance
docker run --name mysql -e MYSQL_ROOT_PASSWORD=dev -p 3306:3306 -d mysql:8

# First-run hardening
mysql_secure_installation

# Connect (CLI)
mysql -h 127.0.0.1 -u root -p shop          # password prompt
mysql -h 127.0.0.1 -u app -p --ssl-mode=REQUIRED
mysql --defaults-file=~/.my.cnf shop        # store creds in client section

# Create user + db
CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
CREATE USER 'app'@'%' IDENTIFIED BY 'secret';
GRANT ALL PRIVILEGES ON shop.* TO 'app'@'%';
FLUSH PRIVILEGES;

-- Top 3 orders per customer (MySQL 8+)
SELECT customer_id, order_id, amount
FROM (
  SELECT
    customer_id,
    order_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
  FROM orders
) t
WHERE rn <= 3;

-- Running total by day
SELECT
  day,
  revenue,
  SUM(revenue) OVER (ORDER BY day
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM daily_revenue;

-- Recursive CTE — generate dates
WITH RECURSIVE days (d) AS (
  SELECT DATE '2026-01-01'
  UNION ALL
  SELECT d + INTERVAL 1 DAY FROM days WHERE d < '2026-01-31'
)
SELECT d FROM days;

-- Table with JSON column + generated/indexed columns
CREATE TABLE events (
  id    BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  ts    DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  data  JSON         NOT NULL,
  -- Functional index (MySQL 8.0.13+) — index on a JSON expression
  KEY idx_user_plan ((CAST(data->>'$.user.plan' AS CHAR(16))))
);

INSERT INTO events (data) VALUES
  ('{"type":"login","user":{"id":42,"plan":"pro"}}'),
  ('{"type":"click","user":{"id":7,"plan":"free"},"path":"/pricing"}');

-- Extract — -> keeps JSON, ->> unquotes to text
SELECT
  data->>'$.user.id'   AS user_id,
  data->'$.user.plan'  AS plan_json
FROM events
WHERE data->>'$.user.plan' = 'pro';

-- Mutate — JSON_SET, JSON_REMOVE, JSON_MERGE_PATCH
UPDATE events
SET data = JSON_SET(data, '$.user.tier', 'gold')
WHERE id = 1;

-- JSON_TABLE — pivot JSON into rows (8.0+)
SELECT t.user_id, t.plan
FROM events e,
     JSON_TABLE(e.data, '$' COLUMNS (
       user_id INT  PATH '$.user.id',
       plan    TEXT PATH '$.user.plan'
     )) AS t;

-- Upsert — MySQL syntax (no ON CONFLICT)
INSERT INTO users (email, name, login_count)
VALUES ('a@x.com', 'Ana', 1) AS new       -- MySQL 8.0.19+ row alias
ON DUPLICATE KEY UPDATE
  name        = new.name,
  login_count = users.login_count + 1;

-- Older syntax — VALUES() function (deprecated in 8.0.20+)
INSERT INTO users (email, name)
VALUES ('a@x.com', 'Ana')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- REPLACE — delete then insert. Watch out: re-fires triggers, drops FKs
REPLACE INTO users (email, name) VALUES ('a@x.com', 'Ana');

-- INSERT IGNORE — silently skip duplicate-key + many other errors
INSERT IGNORE INTO inventory (sku, qty) VALUES ('a', 5), ('b', 2);

-- Bulk upsert from a VALUES table
INSERT INTO inventory (sku, qty)
VALUES ('a', 5), ('b', 2), ('c', 9) AS new
ON DUPLICATE KEY UPDATE qty = new.qty;

-- Plan only — fast
EXPLAIN
SELECT * FROM orders WHERE customer_id = 42;

-- Plan + real execution stats (MySQL 8.0.18+)
EXPLAIN ANALYZE
SELECT o.id, c.email
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= NOW() - INTERVAL 7 DAY
ORDER BY o.created_at DESC
LIMIT 50;

-- JSON output — paste into a plan visualizer
EXPLAIN FORMAT=JSON SELECT ...;

-- Read the EXPLAIN columns:
--   type:       const > eq_ref > ref > range > index > ALL (worst)
--   key:        which index actually got picked
--   rows:       estimated rows scanned
--   filtered:   % of those rows surviving WHERE
--   Extra:      "Using index" (covering), "Using filesort" (sort spill),
--               "Using temporary" (temp table — usually bad on big sets)

-- Refresh stats
ANALYZE TABLE orders;

-- Tiny shop schema with the choices that age well in MySQL 8 / InnoDB
CREATE TABLE customer (
  id         BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  email      VARCHAR(255) NOT NULL,
  created_at DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  UNIQUE KEY uq_customer_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE product (
  id    BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  sku   VARCHAR(64)    NOT NULL,
  price DECIMAL(10,2)  NOT NULL,
  UNIQUE KEY uq_product_sku (sku),
  CHECK (price >= 0)
) ENGINE=InnoDB;

CREATE TABLE `order` (
  id           BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  customer_id  BIGINT UNSIGNED NOT NULL,
  total        DECIMAL(12,2)   NOT NULL,
  placed_at    DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  KEY idx_order_customer_placed (customer_id, placed_at DESC),
  CONSTRAINT fk_order_customer
    FOREIGN KEY (customer_id) REFERENCES customer(id)
) ENGINE=InnoDB;

-- Top 5 spenders in the last 30 days
SELECT c.email, SUM(o.total) AS spend
FROM `order` o
JOIN customer c ON c.id = o.customer_id
WHERE o.placed_at >= NOW() - INTERVAL 30 DAY
GROUP BY c.email
ORDER BY spend DESC
LIMIT 5;