MySQL 5.7 Virtual Columns: Complete Guide to Syntax, Use Cases, and Indexing

MySQL 5.7 introduced a powerful feature that revolutionizes how developers handle computed data: virtual columns (also known as generated columns). This comprehensive guide explores everything you need to know about implementing and optimizing virtual columns in your database applications.

What Are Virtual Columns in MySQL 5.7?

Virtual columns are table columns whose values are automatically computed based on expressions or other columns in the same table, rather than being explicitly stored on disk. These columns appear and behave like regular table columns but derive their values dynamically through predefined expressions.

Key Characteristics

Computed Values: Data is calculated from expressions or other column values
Storage Efficiency: Virtual columns don’t consume additional disk space
Real-time Updates: Values automatically update when referenced columns change
Indexable: Can be indexed for improved query performance

Virtual Column Syntax

Basic Syntax Structure

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    virtual_column datatype AS (expression) [VIRTUAL | STORED],
    ...
);

Creating Virtual Columns

-- Example: Customer table with computed full name
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    full_name VARCHAR(101) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,
    birth_date DATE,
    age INT AS (YEAR(CURDATE()) - YEAR(birth_date)) VIRTUAL
);

Adding Virtual Columns to Existing Tables

-- Add virtual column to existing table
ALTER TABLE products 
ADD COLUMN discounted_price DECIMAL(10,2) AS (price * 0.9) VIRTUAL;

-- Add stored generated column
ALTER TABLE orders 
ADD COLUMN total_with_tax DECIMAL(10,2) AS (subtotal * 1.08) STORED;

Virtual vs Stored Generated Columns

Virtual Columns (VIRTUAL)

Values computed on-the-fly during queries
No disk storage required
Faster INSERT/UPDATE operations
Slightly slower SELECT operations

Stored Generated Columns (STORED)

Values computed and physically stored
Requires disk space
Slower INSERT/UPDATE operations
Faster SELECT operations

CREATE TABLE sales_data (
    product_id INT,
    quantity INT,
    unit_price DECIMAL(10,2),
    -- Virtual: computed each time
    subtotal DECIMAL(10,2) AS (quantity * unit_price) VIRTUAL,
    -- Stored: computed once and saved
    total DECIMAL(10,2) AS (quantity * unit_price * 1.1) STORED
);

Practical Use Cases

1. Data Transformation and Formatting

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255),
    username VARCHAR(50) AS (SUBSTRING_INDEX(email, '@', 1)) VIRTUAL,
    domain VARCHAR(100) AS (SUBSTRING_INDEX(email, '@', -1)) VIRTUAL
);

2. Mathematical Calculations

CREATE TABLE geometry (
    id INT PRIMARY KEY,
    radius DECIMAL(10,2),
    area DECIMAL(15,4) AS (PI() * radius * radius) VIRTUAL,
    circumference DECIMAL(15,4) AS (2 * PI() * radius) VIRTUAL
);

3. Date and Time Computations

CREATE TABLE events (
    id INT PRIMARY KEY,
    event_date DATETIME,
    year_month VARCHAR(7) AS (DATE_FORMAT(event_date, '%Y-%m')) VIRTUAL,
    day_of_week VARCHAR(10) AS (DAYNAME(event_date)) VIRTUAL,
    is_weekend BOOLEAN AS (DAYOFWEEK(event_date) IN (1, 7)) VIRTUAL
);

4. JSON Data Extraction

CREATE TABLE user_profiles (
    id INT PRIMARY KEY,
    profile_data JSON,
    user_name VARCHAR(100) AS (JSON_UNQUOTE(JSON_EXTRACT(profile_data, '$.name'))) VIRTUAL,
    user_age INT AS (JSON_EXTRACT(profile_data, '$.age')) VIRTUAL
);

Indexing Virtual Columns

Creating Indexes on Virtual Columns

-- Create index on virtual column
CREATE INDEX idx_full_name ON customers (full_name);

-- Composite index with virtual column
CREATE INDEX idx_age_name ON customers (age, full_name);

-- Unique index on virtual column
CREATE UNIQUE INDEX idx_username ON users (username);

Performance Benefits

-- Efficient queries using indexed virtual columns
SELECT * FROM customers WHERE full_name LIKE 'John%';
SELECT * FROM events WHERE year_month = '2025-07';
SELECT * FROM users WHERE username = 'john.doe';

Advanced Examples

Complex Business Logic

CREATE TABLE orders (
    id INT PRIMARY KEY,
    subtotal DECIMAL(10,2),
    tax_rate DECIMAL(4,4),
    discount_percent DECIMAL(5,2),
    shipping_cost DECIMAL(8,2),

    -- Multi-step calculation
    discount_amount DECIMAL(10,2) AS (subtotal * discount_percent / 100) VIRTUAL,
    taxable_amount DECIMAL(10,2) AS (subtotal - (subtotal * discount_percent / 100)) VIRTUAL,
    tax_amount DECIMAL(10,2) AS ((subtotal - (subtotal * discount_percent / 100)) * tax_rate) VIRTUAL,
    total DECIMAL(10,2) AS (
        subtotal - (subtotal * discount_percent / 100) + 
        ((subtotal - (subtotal * discount_percent / 100)) * tax_rate) + 
        shipping_cost
    ) VIRTUAL
);

Conditional Logic

CREATE TABLE employees (
    id INT PRIMARY KEY,
    salary DECIMAL(10,2),
    years_experience INT,
    department VARCHAR(50),

    salary_grade VARCHAR(10) AS (
        CASE 
            WHEN salary >= 100000 THEN 'Senior'
            WHEN salary >= 70000 THEN 'Mid-level'
            ELSE 'Junior'
        END
    ) VIRTUAL,

    bonus_eligible BOOLEAN AS (
        years_experience >= 2 AND department IN ('Sales', 'Engineering')
    ) VIRTUAL
);

Best Practices and Limitations

Best Practices

Use Virtual for Frequently Computed Values: Ideal for calculations performed repeatedly
Index Strategic Virtual Columns: Create indexes on virtual columns used in WHERE clauses
Keep Expressions Simple: Complex expressions can impact performance
Consider Storage vs Computation Trade-offs: Use STORED for expensive calculations accessed frequently

Limitations

-- These are NOT allowed in virtual column expressions:
-- - Subqueries
-- - Non-deterministic functions (RAND(), NOW())
-- - User-defined functions
-- - References to other tables

-- Valid deterministic functions
CREATE TABLE valid_examples (
    id INT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    year_created INT AS (YEAR(created_at)) VIRTUAL,  -- Valid
    upper_name VARCHAR(100) AS (UPPER(name)) VIRTUAL -- Valid
);

Performance Optimization Tips

Query Optimization

-- Leverage indexes on virtual columns
EXPLAIN SELECT * FROM customers WHERE full_name = 'John Smith';

-- Use virtual columns in ORDER BY
SELECT * FROM events ORDER BY year_month DESC;

-- Combine with regular columns in composite indexes
CREATE INDEX idx_dept_grade ON employees (department, salary_grade);

Monitoring Performance

-- Check index usage
SHOW INDEX FROM customers;

-- Analyze query execution
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE total > 1000;

Conclusion

MySQL 5.7 virtual columns provide a powerful mechanism for creating computed fields that enhance data accessibility and query performance. By understanding the syntax, use cases, and indexing strategies outlined in this guide, you can effectively leverage virtual columns to build more efficient and maintainable database applications.

Whether you’re implementing complex business logic, transforming data formats, or optimizing query performance, virtual columns offer a robust solution that combines the flexibility of computed values with the performance benefits of proper indexing.

The WebScale Database Infrastructure Architecture, Engineering and Operations Company

Full-Stack Database Engineering & Cloud DBaaS Solutions for PostgreSQL, MySQL, MongoDB & More | Performance, Scalability, High Availability, Security & Analytics Experts

MySQL 5.7 Virtual Columns