MySQL Generated Columns

MySQL generated columns are table columns whose values are computed from an expression. Instead of calculating the same value in every query or application, you define the expression once in the table structure.

Generated columns are useful for derived values such as full names, totals, normalized search values, date parts, and values extracted from JSON. MySQL keeps the generated value consistent with the base columns.

Generated Column Syntax

Use GENERATED ALWAYS AS or the shorter AS syntax inside CREATE TABLE or ALTER TABLE.

Syntax:

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

Create a Virtual Generated Column

A virtual generated column does not store its value in the table data. MySQL calculates it when you read the row.

Example:

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(40),
  last_name VARCHAR(40),
  full_name VARCHAR(90)
    AS (CONCAT(first_name, ' ', last_name)) VIRTUAL
);

INSERT INTO employees (employee_id, first_name, last_name)
VALUES (1, 'Aarav', 'Sharma');

-- MySQL calculates full_name from first_name and last_name.
SELECT employee_id, full_name FROM employees;

Create a Stored Generated Column

A stored generated column saves the computed value. This can help when the expression is expensive or when you need reliable indexing behavior.

Example:

CREATE TABLE order_items (
  item_id INT PRIMARY KEY,
  price DECIMAL(10, 2),
  quantity INT,
  line_total DECIMAL(10, 2)
    AS (price * quantity) STORED
);

INSERT INTO order_items (item_id, price, quantity)
VALUES (1, 499.00, 3);

SELECT item_id, line_total FROM order_items;

Add a Generated Column to an Existing Table

Use ALTER TABLE when the table already exists. This is useful when you want to standardize a repeated expression without rewriting many queries.

Example:

ALTER TABLE employees
ADD COLUMN email_domain VARCHAR(80)
  AS (SUBSTRING_INDEX(email, '@', -1)) STORED;

Index a Generated Column

MySQL can use indexes on generated columns. Indexing a generated column is useful when you frequently filter or sort by the computed value.

Example:

CREATE INDEX idx_line_total
ON order_items (line_total);

-- The index can help this filter.
SELECT item_id, line_total
FROM order_items
WHERE line_total > 1000;

Use Generated Columns with JSON

Generated columns are often used to expose a JSON path as a normal SQL column. You can then filter or index that extracted value more easily.

Example:

CREATE TABLE product_events (
  event_id INT PRIMARY KEY,
  event_data JSON,
  product_sku VARCHAR(30)
    AS (JSON_UNQUOTE(JSON_EXTRACT(event_data, '$.sku'))) STORED,
  INDEX (product_sku)
);

INSERT INTO product_events VALUES
(1, '{"sku":"KB-100","action":"view"}');

-- Query the generated column instead of repeating the JSON path.
SELECT event_id
FROM product_events
WHERE product_sku = 'KB-100';

Update Rules

You do not insert or update a generated column directly. MySQL calculates its value from the expression. If you explicitly provide a value, only DEFAULT is allowed.

Example:

-- Correct: provide only base column values.
INSERT INTO order_items (item_id, price, quantity)
VALUES (2, 250.00, 4);

-- Correct: MySQL recomputes line_total after the update.
UPDATE order_items
SET quantity = 5
WHERE item_id = 2;

Expression Rules

Generated column expressions must be deterministic. That means the expression should produce the same result for the same row values.

• Use literals, operators, and deterministic built-in functions.
• Do not use stored functions, variables, or subqueries in the expression.
• Do not use nondeterministic functions such as NOW() in a generated column expression.
• Reference earlier generated columns only when MySQL allows the dependency order.

When to Use Generated Columns

• Use them to avoid repeating the same expression in many queries.
• Use stored columns when repeated reads are more important than storage.
• Use virtual columns when storage is more important than read-time calculation.
• Use them carefully for JSON paths that need filtering or indexing.
• Avoid them for values that should be entered and controlled by application logic.

Tip: Generated columns make derived data consistent, but they are still part of schema design. Choose names and expressions that will stay meaningful as the application grows.

Conclusion

MySQL generated columns let you define computed values directly in a table. Use virtual columns for lightweight derived values, stored columns for repeated reads or indexing, and JSON-based generated columns when structured SQL queries need values stored inside JSON documents.