MySQL Generated Columns: VIRTUAL and STORED Explained

A generated column is a column whose value MySQL calculates from other columns. You never insert into it. It is kept correct automatically.

query.sql

SQL

CREATE TABLE order_lines (
    id INT AUTO_INCREMENT PRIMARY KEY,
    unit_price DECIMAL(8,2) NOT NULL,
    quantity INT NOT NULL,
    line_total DECIMAL(10,2) GENERATED ALWAYS AS (unit_price * quantity) STORED
);

INSERT INTO order_lines (unit_price, quantity) VALUES (80.00, 3), (15.50, 10);
SELECT * FROM order_lines;

Output

OUTPUT

+----+------------+----------+------------+
| id | unit_price | quantity | line_total |
+----+------------+----------+------------+
|  1 |      80.00 |        3 |     240.00 |
|  2 |      15.50 |       10 |     155.00 |
+----+------------+----------+------------+
2 rows in set

line_total was computed by MySQL. If you update quantity, line_total changes too.

query.sql

SQL

1
2

UPDATE order_lines SET quantity = 4 WHERE id = 1;
SELECT id, quantity, line_total FROM order_lines WHERE id = 1;

Output

OUTPUT

+----+----------+------------+
| id | quantity | line_total |
+----+----------+------------+
|  1 |        4 |     320.00 |
+----+----------+------------+
1 row in set

Syntax

Text

PLAIN

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

GENERATED ALWAYS is optional. This is MySQL syntax, though similar features exist elsewhere.

VIRTUAL vs STORED

	`VIRTUAL` (default)	`STORED`
Takes disk space	No	Yes
Calculated when	Each time you read it	When the row is written
Can be indexed	Yes (secondary index)	Yes
Best for	Cheap expressions	Costly expressions, or a value read far more than written

You cannot write to it

query.sql

SQL

INSERT INTO order_lines (unit_price, quantity, line_total) VALUES (10, 2, 999);

Output

OUTPUT

ERROR 3105 (HY000): The value specified for generated column 'line_total' in table 'order_lines' is not allowed.

Use case 1: a computed value that is always right

query.sql

SQL

CREATE TABLE people (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(30) NOT NULL,
    last_name VARCHAR(30) NOT NULL,
    full_name VARCHAR(61) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL
);
INSERT INTO people (first_name, last_name) VALUES ('Asha', 'Rao'), ('Ben', 'Carter');
SELECT full_name FROM people ORDER BY full_name;

Output

OUTPUT

+------------+
| full_name  |
+------------+
| Asha Rao   |
| Ben Carter |
+------------+
2 rows in set

Use case 2: indexing an expression

An index normally works on a column, not on a calculation. A generated column fixes that. It is the way to make a search on LOWER(email) or on a JSON field fast:

query.sql

SQL

CREATE TABLE accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    email_domain VARCHAR(100) AS (SUBSTRING_INDEX(email, '@', -1)) VIRTUAL,
    KEY idx_domain (email_domain)
);
INSERT INTO accounts (email) VALUES ('a@example.com'), ('b@example.org'), ('c@example.com');
SELECT email FROM accounts WHERE email_domain = 'example.com' ORDER BY id;

Output

OUTPUT

+---------------+
| email         |
+---------------+
| a@example.com |
| c@example.com |
+---------------+
2 rows in set

MySQL 8.0.13 and later can also index an expression directly (a “functional index”), which is often shorter (Functional Indexes). Generated columns still win when you also want to select or filter on the computed value.

Rules and limits

The expression may use other columns of the same table, literals and deterministic functions. It may not use NOW(), RAND(), subqueries, stored functions or variables.
A generated column cannot be a PRIMARY KEY if it is VIRTUAL.
You can add one later with ALTER TABLE ... ADD COLUMN ... AS (...). A VIRTUAL column is added instantly, and a STORED one rebuilds the table.

lightbulb

Do not store what you can calculate

A column such as age that must be updated every year is a bug waiting to happen. Store the birth date and calculate the age when you read it, with a generated column or a query.