Intermediate LevelSQL & MySQL
What are Generated (Computed) Columns in MySQL (VIRTUAL vs STORED)?
❓ Interview Prompt: Explain Generated Columns, `VIRTUAL` (computed on read) vs `STORED` (persisted on write), and indexing computed expressions.
Generated (Computed) Columns automatically calculate their values from expressions referencing other columns in the same table:
2 Types:
1
'VIRTUAL' (Default): Values are computed on-the-fly whenever rows are read. Conserves disk space.
2
'STORED': Values are evaluated and written to disk whenever rows are inserted or updated.
•
Indexing: Both 'VIRTUAL' and 'STORED' columns can be indexed to accelerate queries on computed expressions!
Code Implementation Example
sql
CREATE TABLE orders (
id INT PRIMARY KEY,
subtotal DECIMAL(10, 2),
tax DECIMAL(10, 2),
-- Generated Column computing total order price
total_price DECIMAL(10, 2) GENERATED ALWAYS AS (subtotal + tax) STORED,
INDEX idx_total_price (total_price)
);💡Defining a STORED Generated Column with a secondary B-Tree index.
🎯 Interview Key Takeaway
Use Generated Columns to encapsulate computed expressions and build B-Tree indexes on derived fields.
#MySQL#Generated Columns#Computed Columns#Intermediate
Back to all SQL & MySQL Questions→