Generated Columns and Invisible Columns

Generated columns make expressions indexable (JSON paths, normalized strings, derived dates) and guarantee consistent derived values.

Concept

Generated columns (5.7+) compute their value from an expression on other columns of the same row. VIRTUAL columns are computed on read (no storage); STORED columns are computed on write and stored. Both can be indexed (InnoDB supports secondary indexes on virtual columns).

Invisible columns (8.0.23+) are hidden from SELECT * and from INSERT without a column list, but can be referenced explicitly — useful for adding columns without breaking legacy code.

Why It Matters

Generated columns make expressions indexable (JSON paths, normalized strings, derived dates) and guarantee consistent derived values. Invisible columns allow safe schema evolution.

Syntax

col_name type GENERATED ALWAYS AS (expr) [VIRTUAL | STORED] [NOT NULL] [UNIQUE],
col_name type INVISIBLE,
ALTER TABLE t ALTER COLUMN c SET VISIBLE | SET INVISIBLE;

Example

Rules: expressions must be deterministic (no NOW(), RAND(), subqueries, or stored functions) and may reference only columns of the same row — any non-generated column, or a generated column defined earlier in the table.

VIRTUAL vs STORED:

On INSERT/UPDATE Yes (secondary) Yes (including PK) Instant (usually) Cheap expressions, index targets Expensive expressions read often Invisible column:

CREATE TABLE sensor_readings (
    reading_id   BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vin          CHAR(17)    NOT NULL,
    captured_at  DATETIME(3) NOT NULL,
    temp_c       DECIMAL(5,1) NOT NULL,
    temp_f       DECIMAL(5,1) GENERATED ALWAYS AS (temp_c * 9 / 5 + 32) VIRTUAL,
    capture_day  DATE         GENERATED ALWAYS AS (DATE(captured_at)) STORED,
    wmi          CHAR(3)      GENERATED ALWAYS AS (LEFT(vin, 3)) VIRTUAL,       -- World Manufacturer Identifier
    INDEX ix_readings_day (capture_day),
    INDEX ix_readings_wmi (wmi)
);

INSERT INTO sensor_readings (vin, captured_at, temp_c) VALUES ('EHBAUREV250000001', '2026-03-10 10:01:00.000', 92.0);
SELECT temp_c, temp_f, capture_day, wmi FROM sensor_readings;
-- 92.0 | 197.6 | 2026-03-10 | EHB

INSERT INTO sensor_readings (vin, captured_at, temp_c, temp_f) VALUES ('X', NOW(), 1, 2);
-- ERROR 3105: The value specified for generated column 'temp_f' in table 'sensor_readings' is not allowed.

ALTER TABLE customers ADD COLUMN loyalty_tier VARCHAR(10) INVISIBLE DEFAULT 'BASIC';
SELECT * FROM customers LIMIT 1;                       -- loyalty_tier not shown
SELECT customer_id, loyalty_tier FROM customers LIMIT 1;   -- shown when named

Expected Result

EXPLAIN SELECT COUNT(*) FROM sensor_readings WHERE wmi = 'EHB';
-- key: ix_readings_wmi  (the virtual column's index is used)

Common Mistakes

Non-deterministic expressions (rejected).

Inserting explicit values into generated columns.

Expecting SELECT * to show invisible columns.

Best Practices

Use VIRTUAL + index for JSON paths and derived lookup keys.

Keep expressions simple and documented.

Interview Relevance

Low-medium: "VIRTUAL vs STORED generated columns?", "How do you index a JSON field?".

Practice Questions

Add a generated column model_year_code = the 10th character of the VIN and index it.

Add a STORED column for order month and group by it.