MySQL の生成列

column_name data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED]

CREATE TABLE orders (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    subtotal    DECIMAL(10,2) NOT NULL,
    tax_rate    DECIMAL(5,4) NOT NULL,
    tax_amount  DECIMAL(10,2) AS (subtotal * tax_rate) VIRTUAL,
    total       DECIMAL(10,2) AS (subtotal + subtotal * tax_rate) STORED
);

CREATE TABLE users (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name  VARCHAR(50),
    full_name  VARCHAR(101) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
    email      VARCHAR(100),
    email_lower VARCHAR(100) AS (LOWER(email)) STORED
);

CREATE TABLE events (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    payload JSON,
    user_id INT AS (payload->>'$.user_id') STORED,
    action  VARCHAR(50) AS (payload->>'$.action') VIRTUAL
);

CREATE TABLE events (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    occurred_at DATETIME NOT NULL,
    ym          VARCHAR(7) AS (DATE_FORMAT(occurred_at, '%Y-%m')) VIRTUAL
);

-- Index the computed ym. (Note: YEAR_MONTH is a reserved word — the
-- INTERVAL unit in INTERVAL '1-2' YEAR_MONTH — so an unquoted column
-- can't be named year_month.)
ALTER TABLE events ADD INDEX idx_ym (ym);

-- This query now uses the index
SELECT * FROM events WHERE ym = '2024-06';

-- Functional index syntax (MySQL 8.0.13+)
CREATE TABLE users (
    id    INT PRIMARY KEY,
    email VARCHAR(100),
    INDEX idx_email_lower ((LOWER(email)))  -- note the double parentheses
);

-- Equivalent to:
CREATE TABLE users (
    id          INT PRIMARY KEY,
    email       VARCHAR(100),
    email_lower VARCHAR(100) AS (LOWER(email)) VIRTUAL,
    INDEX idx_email_lower (email_lower)
);

-- Correct: omit the generated column entirely
INSERT INTO orders (subtotal, tax_rate) VALUES (100.00, 0.08);

-- Correct: DEFAULT is allowed
INSERT INTO orders (subtotal, tax_rate, total) VALUES (100.00, 0.08, DEFAULT);

-- Error: cannot assign a value to a generated column
INSERT INTO orders (subtotal, tax_rate, total) VALUES (100.00, 0.08, 108.00);

ALTER TABLE orders
ADD COLUMN discount_pct DECIMAL(5,2)
    AS (CASE WHEN subtotal > 1000 THEN 0.10 ELSE 0.00 END) VIRTUAL;

ALTER TABLE orders
MODIFY COLUMN tax_amount DECIMAL(10,2)
    AS (ROUND(subtotal * tax_rate, 2)) VIRTUAL;