Generated Columns

Generated Columns

Generated Columns and Functional Indexes

A generated column, col type AS (expr), is computed from its row: VIRTUAL (the default) on every read, STORED on every write. Both can be indexed, the expression must be deterministic, and you cannot write to them.

A virtual column over JSON, a stored line total, and a functional indexSQL

ALTER TABLE products
  ADD COLUMN format VARCHAR(20) AS (attributes->>'$.format') VIRTUAL AFTER price,
  ADD INDEX idx_format (format);
ALTER TABLE order_items
  ADD COLUMN line_total DECIMAL(10,2) AS (quantity * unit_price) STORED;
CREATE INDEX idx_pages ON products ((CAST(attributes->>'$.pages' AS UNSIGNED)));
SELECT sku, format, price FROM products WHERE format = 'hardcover';
UPDATE order_items SET line_total = 0 WHERE order_id = 1;

Output

+-----------+-----------+-------+
| sku       | format    | price |
+-----------+-----------+-------+
| BK-LAR-01 | hardcover | 49.00 |
+-----------+-----------+-------+
ERROR 3105 (HY000): The value specified for generated column 'line_total' in table
  'order_items' is not allowed.

The virtual column needed no rebuild, the stored one rewrote the 15 order lines, and EXPLAIN shows Index lookup on products using idx_format; Indexing JSON indexes JSON arrays the same way. A functional index (MySQL 8.0.13 524 ) indexes an expression directly and is really a hidden virtual column, which SHOW EXTENDED COLUMNS lists as !hidden!idx_pages!0!0. Queries must repeat the expression exactly: WHERE CAST(attributes->>'$.pages' AS UNSIGNED) > 400 got an Index range scan ... using idx_pages. An index on (RAND()) fails with error 3758.

MENU