Using Generated Columns in MySQL

Generated columns compute their value automatically from other columns in the same row — useful for indexing computed expressions or maintaining derived data without triggers or application logic.

CREATE TABLE orders (
    id INT PRIMARY KEY,
    quantity INT,
    unit_price_cents INT,
    -- Automatically computed, always in sync with the source columns
    total_cents INT GENERATED ALWAYS AS (quantity * unit_price_cents) STORED,
    -- Virtual: computed on read, not stored on disk
    total_display VARCHAR(20) GENERATED ALWAYS AS (
        CONCAT('$', total_cents / 100)
    ) VIRTUAL
);

-- STORED generated columns can be indexed
ALTER TABLE orders ADD INDEX idx_total (total_cents);

STORED columns are computed once at write time and take disk space, but can be indexed; VIRTUAL columns (the default) are computed on every read and take no storage, but generally can’t be indexed directly in older MySQL versions. Choose based on whether you need to query/index the computed value or just display it.

Using Generated Columns in MySQL

Recommended

Categories