Skip to main content

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples in this guide work on VillageSQL. Install Now →
A generated column is a column whose value is derived from an expression rather than stored by the application. MySQL computes it automatically — you define the formula once in the schema, and MySQL keeps it consistent.

​
Syntax

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
);

​
VIRTUAL vs STORED

VIRTUALSTORED
StorageNo disk storage — computed on readWritten to disk on INSERT/UPDATE
Computation timingEvery SELECTEvery INSERT/UPDATE
Disk spaceSaves spaceUses space like a regular column
Can be indexedYes (index stores the computed values)Yes
Can be a foreign key targetNoNo
DefaultYes — VIRTUAL is the default if omittedOpt-in
Use STORED when the computation is expensive and you read the column far more than you write it. Use VIRTUAL (the default) when storage is a concern or writes happen frequently.

​
Expressions

Generated column expressions can reference other columns in the same row. They cannot reference other generated columns or call non-deterministic functions. 
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
);
Functions allowed in generated columns must be deterministic — same inputs always produce the same output. Disallowed: NOW(), RAND(), UUID(), subqueries, stored functions. Extracting fields from JSON is a common use case: 
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
);

​
Indexing Generated Columns

You can add an index on a generated column. For VIRTUAL columns, MySQL materializes the computed value into the index. 
CREATE TABLE events (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    occurred_at DATETIME NOT NULL,
    year_month  VARCHAR(7) AS (DATE_FORMAT(occurred_at, '%Y-%m')) VIRTUAL
);

-- Index the computed year_month
ALTER TABLE events ADD INDEX idx_year_month (year_month);

-- This query now uses the index
SELECT * FROM events WHERE year_month = '2024-06';
This is also how to build a functional index — an index on a function of a column rather than the raw column value. MySQL 8.0.13+ supports functional indexes directly (INDEX (LOWER(email))), which internally creates a hidden generated column: 
-- 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)
);

​
Inserting and Updating

You cannot assign a value to a generated column in an INSERT or UPDATE. The column value is always derived from the expression. You can write DEFAULT explicitly, but that’s all: 
-- 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

Add a generated column to an existing table: 
ALTER TABLE orders
ADD COLUMN discount_pct DECIMAL(5,2)
    AS (CASE WHEN subtotal > 1000 THEN 0.10 ELSE 0.00 END) VIRTUAL;
Change a generated column’s expression: 
ALTER TABLE orders
MODIFY COLUMN tax_amount DECIMAL(10,2)
    AS (ROUND(subtotal * tax_rate, 2)) STORED;

​
Frequently Asked Questions

​
Can I use a generated column in a WHERE clause?

Yes. For VIRTUAL columns without an index, MySQL computes the expression during the scan. For STORED columns or indexed VIRTUAL columns, the value is already materialized. Use EXPLAIN to verify whether the index is being used.

​
Does a generated column update automatically when the source columns change?

Yes. MySQL recomputes VIRTUAL generated columns on every read. STORED generated columns are recomputed and written on every INSERT or UPDATE that affects the columns in the expression.

​
Can I use a generated column as a partition key?

Yes, for STORED generated columns. VIRTUAL columns cannot be used as partition keys.

​
Troubleshooting

ProblemSolution
ERROR 3105: The value specified for generated column is not allowedYou assigned an explicit value to a generated column in INSERT/UPDATE — omit it or use DEFAULT
ERROR 3106: 'Non-deterministic functions' is not allowedThe expression uses NOW(), RAND(), UUID(), or a subquery — replace with deterministic alternatives
Generated column not using indexCheck EXPLAIN — the WHERE clause expression must exactly match the generated column’s expression; WHERE LOWER(email) uses a LOWER(email) generated column index, but WHERE LOWER(TRIM(email)) does not
STORED column slowing down writesEvery INSERT/UPDATE recomputes and stores the expression — switch to VIRTUAL if read performance isn’t the priority
ALTER TABLE adding generated column is slowMySQL rebuilds the table for STORED generated columns; VIRTUAL column additions are much faster (metadata-only in many cases)