Generated Columns

-- ===== Syntax =====
CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  subtotal_cents BIGINT NOT NULL,
  tax_cents      BIGINT NOT NULL,
  total_cents    BIGINT GENERATED ALWAYS AS (subtotal_cents + tax_cents) VIRTUAL,
  total_aud      DECIMAL(12,2) GENERATED ALWAYS AS ((subtotal_cents + tax_cents) / 100) STORED
);

-- VIRTUAL: computed on read; no storage cost (default)
-- STORED:  computed on write; persisted on disk

INSERT INTO orders (subtotal_cents, tax_cents) VALUES (4500, 495);
SELECT total_cents, total_aud FROM orders;   -- both available

-- ===== Use cases =====
-- 1. Compute a value once, query it many times
-- 2. Derive an index-friendly representation of JSON
-- 3. Enforce a derived constraint via CHECK on the generated column

-- ===== Index a JSON path =====
CREATE TABLE profiles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  data JSON NOT NULL,
  country VARCHAR(2) GENERATED ALWAYS AS (data->>'$.country') STORED,
  INDEX (country)
);

SELECT * FROM profiles WHERE country = 'AU';
-- Uses the index. Without the generated column, you would seq-scan.

-- ===== VIRTUAL + index =====
CREATE TABLE events (
  ts DATETIME NOT NULL,
  day DATE GENERATED ALWAYS AS (DATE(ts)) VIRTUAL,
  INDEX (day)
);

-- VIRTUAL columns CAN be indexed; index stores the computed value.

-- ===== Constraints =====
ALTER TABLE orders
  ADD CONSTRAINT chk_positive_total CHECK (total_cents >= 0);

-- ===== Limitations =====
-- - Generated columns CANNOT reference other generated columns (in most versions)
-- - VIRTUAL generated columns require deterministic expressions
-- - Cannot use non-deterministic functions (NOW(), RAND(), UUID())
-- - Subqueries are not allowed in the expression

-- ===== When to choose VIRTUAL vs STORED =====
-- VIRTUAL:
--   - Lightly-read columns; cheap to compute
--   - Avoid storage cost
--   - You can still index them (the index IS stored)
-- STORED:
--   - Expensive expression
--   - Used in many queries
--   - Foreign keys reference them (FK requires STORED)

-- ===== Updating =====
-- You CANNOT directly assign to a generated column:
UPDATE orders SET total_cents = 999 WHERE id = 1;
-- ERROR

-- Update the source columns; the generated value follows.

-- ===== Use for migrations =====
-- During schema change, generate the new shape from the old:
ALTER TABLE users ADD email_lower VARCHAR(254)
  GENERATED ALWAYS AS (LOWER(email)) VIRTUAL,
  ADD UNIQUE INDEX (email_lower);
-- Existing rows immediately get the indexed lowercase email.

-- ===== Patterns to internalise =====
-- - Generated columns + index for JSON path queries
-- - VIRTUAL by default; STORED when read-heavy or FK target
-- - Pair with CHECK constraints for derived invariants
-- - Avoid non-deterministic expressions

-- ===== Pitfalls =====
-- - STORED columns add storage cost; measure on large tables
-- - Changing the expression requires ALTER TABLE rewrite (slow on big tables)
-- - VIRTUAL column expressions run on every read — keep them cheap
-- - Forgetting that NOW() / RAND() are forbidden -> error at CREATE

ALTER TABLE products
    ADD price_with_tax DECIMAL(10,2) AS (price * 1.10) VIRTUAL;