Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 75 ยท Data Types

Generated Columns

Learn MySQL generated columns: VIRTUAL vs STORED, expression rules, indexing a generated column, extracting JSON fields and functional indexes with live SQLite examples.

A generated column is a column whose value MySQL computes from other columns of the same row. You declare the expression once, and the database keeps it correct: no triggers, no application code that must remember to update a derived value. Typical uses are a full name built from first and last name, a lowercased email for case-insensitive lookups, a price with tax, or a value pulled out of a JSON document so that it can be indexed.

This lesson covers the syntax, the difference between VIRTUAL and STORED columns, the rules for expressions, how to index a generated column (including JSON fields) and the shortcut called a functional index. SQLite supports almost the same syntax, so the examples run live. The index and ALTER TABLE behavior of MySQL itself is shown read-only.

A column that computes itself

Expression Defined once in the table.
Always current Changes when its inputs change.
Read-only You cannot write to it directly.
1 / 2

Syntax

SQL
CREATE TABLE people (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name  VARCHAR(50) NOT NULL,
  full_name  VARCHAR(101) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) VIRTUAL
);

The pieces are: the column name and type, then GENERATED ALWAYS AS (expression), then VIRTUAL or STORED. GENERATED ALWAYS is optional (AS (expression) alone works), and VIRTUAL is the default when you leave both out. You can add NOT NULL, COMMENT and other column options after it.

You cannot insert or update a generated column with a real value. The only values the server accepts are DEFAULT and, in an INSERT, leaving it out. Trying to write another value is an error. Name the column list explicitly in your inserts, so the generated column is never part of the list.

SQLite uses the concatenation operator instead of CONCAT(), but the declaration is otherwise the same, so you can try it:

SQL A virtual generated column

Press Run (or Ctrl + Enter) to see the result here.

The full_name of the second row is now Grace Murray Hopper, because it was computed when the query read it. You never wrote it.

VIRTUAL versus STORED

VIRTUAL STORED
When it is computed Each time the row is read When the row is inserted or updated
Space in the table row None Yes, like a normal column
Read cost The expression runs on each read Plain read
Write cost None The expression runs on each write
Adding it with ALTER TABLE Usually instant (metadata only) Rebuilds the table
Indexable in InnoDB Yes Yes

The usual guidance is to prefer VIRTUAL: it costs nothing to add, takes no space, and is cheap for simple expressions. Choose STORED when the expression is expensive and you read the value very often, or when the engine or tool you use needs a real column (some replication and partitioning setups). If an index covers a virtual column, the computed value is stored inside the index anyway, so lookups through it do not run the expression on every row.

A stored column is a good place to keep a normalized value you search often. SQLite supports STORED as well:

SQL A stored generated column

Press Run (or Ctrl + Enter) to see the result here.

The totals are 1497, 1999 and 600, with an order total of 4096 repeated on each row. The generated column also makes aggregate queries simpler, because SUM(line_total) reads like the business rule.

Rules for the expression

MySQL checks the expression when you create the column. It may use literals, built-in functions and operators, and other columns of the same table (except another generated column that comes later in the definition, and AUTO_INCREMENT columns). It must be deterministic: the same inputs always give the same output. That rules out:

  • Non-deterministic functions such as RAND(), UUID(), NOW() and CURRENT_TIMESTAMP.
  • Subqueries, stored functions you wrote, user variables and parameters.
  • References to other tables.

Because of the last rule, generated columns suit per-row calculations only. A "customer's order count" has to be a query, a summary table or a trigger, not a generated column. Also keep an eye on types: the declared type must hold the result. A CONCAT result longer than the VARCHAR is cut or rejected depending on SQL mode.

Indexing a generated column

The best reason to use generated columns in MySQL is to index an expression. A WHERE LOWER(email) = '[email protected]' cannot use a plain index on email, since the function hides the column (Query Optimization Techniques explains why). With a generated column, you index the computed value and query it:

SQL
ALTER TABLE users
  ADD COLUMN email_lower VARCHAR(190)
    GENERATED ALWAYS AS (LOWER(email)) VIRTUAL,
  ADD UNIQUE KEY uq_email_lower (email_lower);

SELECT id FROM users WHERE email_lower = '[email protected]';
-- type: const, key: uq_email_lower

A unique key on such a column enforces rules the data alone cannot, such as one account per case-insensitive email, even with a case-sensitive collation on the original column. In InnoDB, secondary indexes on virtual columns are supported, with a few limits: the primary key cannot be a virtual column, and creating or dropping a secondary index on a virtual column can require table work, so plan it for quiet hours on very large tables.

SQLite supports indexes on generated columns too, and its EXPLAIN QUERY PLAN shows when an index is used. This live example mirrors the idea:

SQL Indexing a generated column

Press Run (or Ctrl + Enter) to see the result here.

The plan reports a search using the unique index uq_email_lower, the equivalent of a MySQL EXPLAIN showing key: uq_email_lower. Inserting '[email protected]' as a second row would fail with a unique-constraint error, because its lowercase form is the same. Add that insert at the end to see it.

Extracting values from JSON

JSON columns cannot be indexed directly. A generated column that extracts one field turns the document into something a normal index can use. In MySQL the extraction uses the ->> operator (unquoted text) or JSON_VALUE:

SQL
CREATE TABLE events (
  id      BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payload JSON NOT NULL,
  user_id BIGINT UNSIGNED
    GENERATED ALWAYS AS (payload ->> '$.user.id') VIRTUAL,
  kind    VARCHAR(40)
    GENERATED ALWAYS AS (payload ->> '$.type') VIRTUAL,
  KEY idx_events_user (user_id),
  KEY idx_events_kind (kind)
);

SELECT id FROM events WHERE user_id = 42;   -- uses idx_events_user

The $.user.id text is a path expression: $ is the document root and each .name goes one level down. The value is cast into the declared column type, so you query a normal integer. We look at the JSON functions in depth in The JSON Data Type.

SQLite has the same arrows in version 3.38 and later, and json_extract before that. The next example builds the same idea live:

SQL JSON fields as indexed generated columns

Press Run (or Ctrl + Enter) to see the result here.

Events 1 and 3 come back with their kinds login and logout. The query asked about a plain integer column. It never touched JSON syntax, and it can use the index.

Functional indexes

Since MySQL 8.0.13 you can skip naming a generated column: a functional key part is written as an expression in extra parentheses.

SQL
CREATE INDEX idx_users_email_lower ON users ((LOWER(email)));

SELECT id FROM users WHERE LOWER(email) = '[email protected]';

Behind the scenes MySQL creates a hidden virtual generated column and indexes that, so the two approaches share the same mechanics. For a query to use a functional index, the WHERE expression must match the indexed one, including the same function and type conversions. The double parentheses are required, and a few limits apply (no AUTO_INCREMENT, no non-deterministic functions, no spatial or full-text indexes on them). SQLite has the same idea without the extra parentheses:

SQL A functional (expression) index

Press Run (or Ctrl + Enter) to see the result here.

The plan mentions idx_users_email_lower. If the query used upper(email) instead, the index would not apply.

Choosing between the options

  • Use a plain generated column without an index to give a computed value a name that every query can use.
  • Use a generated column with an index when you filter or sort by an expression, or need a UNIQUE rule on it, and you want to see and query the computed value.
  • Use a functional index when you only need the index and the query already contains the expression.
  • Prefer a generated column over a trigger that copies a derived value, because it cannot get out of sync.

What ran live here

The generated column syntax, STORED, indexes on generated columns, JSON extraction and expression indexes ran live on SQLite, which accepts almost identical definitions. The MySQL-specific parts are read-only: CONCAT() and the ->> path syntax inside ALTER TABLE, the EXPLAIN output and the functional-key-part syntax with double parentheses.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the main difference between VIRTUAL and STORED generated columns?

    A virtual column is evaluated when it is read and uses no space in the row. A stored column is calculated when the row is written and occupies space like a normal column.
  2. 2. Which expression is not allowed in a generated column?

    Generated column expressions must be deterministic. NOW(), RAND() and UUID() return different results over time, so they are rejected.
  3. 3. Why create a generated column from a JSON field?

    A generated column extracts and casts one field into a normal type. An index on it speeds up filters that would otherwise scan every JSON document.
  4. 4. How do you write a functional index in MySQL 8.0.13 or later?

    An expression used as an index key part is wrapped in its own parentheses, which gives the double parentheses.
  5. 5. When does a MySQL query use a functional index?

    The optimizer matches the expression in the query to the hidden generated column behind the index, so the query must use the same expression.

Summary

  • A generated column computes its value from other columns of the same row with GENERATED ALWAYS AS (expr), and you never write it directly.
  • VIRTUAL columns are computed on read and cost no row space, STORED columns are computed on write and occupy space. InnoDB can index both.
  • Expressions must be deterministic and use only the same row: no NOW(), RAND(), subqueries or other tables.
  • Index a generated column, for example LOWER(email) or a JSON field, to make an expression searchable and to enforce rules with UNIQUE.
  • A functional index, CREATE INDEX i ON t ((expr)), creates a hidden virtual column for you, and queries must repeat the same expression.

Next: CREATE TABLE in Depth.