Generated Columns

A generated column computes its value from other columns in the same row — VIRTUAL by default, and indexable either way.

Syntaxcol_name data_type [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED] [NOT NULL | NULL] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'string']

A generated column's value is computed by an expression fixed in the table definition. You never write to it; MySQL derives it from other columns of the same row. It gives a repeated expression a name, keeps the derivation in the schema rather than in five services, and makes a computed value indexable.

Shorthand and longhand

total_cents BIGINT GENERATED ALWAYS AS (qty * unit_cents) VIRTUAL,
total_cents BIGINT AS (qty * unit_cents),
-- identical: GENERATED ALWAYS is optional and VIRTUAL is the default

VIRTUAL versus STORED

VIRTUAL uses no row storage and is evaluated when the row is read. STORED is evaluated on every INSERT and UPDATE and written to disk, costing space and write time. Choose STORED when the column must be part of a primary key, or when the expression is expensive and reads dominate. InnoDB supports secondary indexes on virtual columns, so VIRTUAL plus an index covers most cases: the values are materialised in the index instead of the row.

What the expression may contain

Literals, operators and deterministic built-in functions only. No subqueries, no stored or loadable functions, no system or user variables, and nothing non-deterministic such as NOW(), CURRENT_USER() or CONNECTION_ID(). It cannot be AUTO_INCREMENT or read an AUTO_INCREMENT column, and it may reference another generated column only if that one is defined earlier in the table.

Changing them later

Adding, modifying or dropping a VIRTUAL column on a non-partitioned table is in place. Anything involving STORED copies the whole table, and VIRTUAL cannot be converted to STORED in place — you drop and re-add.

Example

Example · sql
CREATE TABLE order_items (
    id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_id   BIGINT NOT NULL,
    product_id INT    NOT NULL,
    qty        INT    NOT NULL,
    unit_cents INT    NOT NULL,

    -- VIRTUAL (the default): nothing on disk, computed on read.
    line_cents BIGINT AS (qty * unit_cents),

    -- STORED: written on every INSERT/UPDATE, so it can carry a UNIQUE key.
    item_key   VARCHAR(40) GENERATED ALWAYS AS
                   (CONCAT(order_id, ':', product_id)) STORED,

    UNIQUE KEY uk_item (item_key),
    KEY idx_line_cents (line_cents)   -- InnoDB indexes virtual columns
) ENGINE=InnoDB;

-- Generated columns are never listed in the INSERT.
INSERT INTO order_items (order_id, product_id, qty, unit_cents)
VALUES (10, 3, 2, 499);

SELECT qty, unit_cents, line_cents, item_key
FROM order_items
WHERE id = LAST_INSERT_ID();   -- 2, 499, 998, '10:3'

-- Hits idx_line_cents: the index stores the computed value.
SELECT id FROM order_items WHERE line_cents > 10000;

When to use it

  • Store a line total or tax amount that can never disagree with the quantity and price it came from.
  • Index a scalar pulled out of a JSON payload so lookups stop scanning the table.
  • Enforce case-insensitive uniqueness on an email or slug at the schema level.
  • Expose a formatted or derived value (full name, month bucket) to reporting queries without a view.

More examples

A generated column as a case-insensitive unique key

This moves a uniqueness rule that application code kept forgetting into the schema, where every writer obeys it. STORED here because the column backs a UNIQUE key that is read constantly. Note the cost: this ALTER copies the table, so plan it like any other rebuild on a large table.

Example · sql
ALTER TABLE users
    ADD COLUMN email_norm VARCHAR(255)
        GENERATED ALWAYS AS (LOWER(TRIM(email))) STORED,
    ADD UNIQUE KEY uk_email_norm (email_norm);

-- Second insert now fails with a duplicate key error rather than creating
-- a second account that differs only in case.
INSERT INTO users (email) VALUES ('[email protected]');
INSERT INTO users (email) VALUES ('  [email protected]  ');

-- Lookups use the index without any function around the column.
SELECT id FROM users WHERE email_norm = LOWER(TRIM(?));

Indexing a value buried in JSON

A JSON column cannot be indexed directly, and this is the documented way around it. VIRTUAL costs no row storage because the value lives in the index instead, and adding a virtual column to a non-partitioned table is an in-place ALTER rather than a rebuild.

Example · sql
ALTER TABLE events
    ADD COLUMN user_id BIGINT UNSIGNED AS
        (CAST(payload->>'$.user.id' AS UNSIGNED)) VIRTUAL,
    ADD KEY idx_user_created (user_id, created_at);

-- Scans the table: no index can serve the raw expression.
SELECT id FROM events
WHERE CAST(payload->>'$.user.id' AS UNSIGNED) = 42;

-- Uses idx_user_created.
SELECT id FROM events
WHERE user_id = 42 AND created_at >= '2026-01-01';

Functional index: the same mechanism without a named column

Reach for the functional index when nothing needs to read the value by name and you only want the optimizer to match the expression; reach for the named column when queries, views or the application refer to it. The double parentheses are required — a functional key part must be an expression, and ((col1)) alone is rejected.

Example · sql
-- A functional key part is implemented as a hidden virtual generated column,
-- so these two do the same work.
ALTER TABLE orders ADD INDEX idx_month ((MONTH(placed_at)));

-- Longhand, when you also want to select the value by name:
ALTER TABLE orders
    ADD COLUMN placed_month TINYINT AS (MONTH(placed_at)) VIRTUAL,
    ADD INDEX idx_placed_month (placed_month);

-- Both are subject to the generated-column expression rules and both
-- count against the table's column limit.
SELECT COUNT(*) FROM orders WHERE MONTH(placed_at) = 3;

Related

sql jsonsql create indexsql sargablesql alter tablesql triggers

Discussion

  • Be the first to comment on this lesson.