MySQL 5.7 Virtual Columns: Syntax, Use Cases, and Indexing
‍

MySQL 5.7 introduces a new feature called virtual/generated column. It is called generated column because the data of this column is computed based on a predefined expression or from other columns.

‍

What is Virtual Column ?

• In general virtual columns appear to be normal table columns, but their values are derived rather than being stored on disk.
• Virtual columns are one of the top features in MySQL 5.7, they can store a value that is derived from one or several other fields in the same table in a new field.

Syntax :

Syntax for adding new virtual column,

==> Alter table table_name add column column_name generated always as column_name virtual;

Example :

Alter table contacts add column generated always as mydbops_test virtual / stored.

GENERATED ALWAYS – It indicates that the column is a generated column.

VIRTUAL – The column values are not stored, but these are evaluated when rows are read.

STORED – The column values are evaluated and stored when rows are inserted or updated.

‍

Use Cases:

‍

Case 1 ( using concat ):

For example we have a mydbops_lab_test table structure as below,

 
mysql> create table mydbops_lab_test
(id int(11) NOT NULL AUTO_INCREMENT primary key,
firstname varchar(20),
lastname varchar(20),
full_name char(41) GENERATED ALWAYS AS (concat(firstname,' ',lastname)),
email_id varchar(25));
 Query OK, 0 rows affected (0.40 sec)
	

 
mysql> desc mydbops_lab_test;
+-----------+-------------+------+-----+---------+-------------------+
| Field     | Type        | Null| Key  | Default | Extra             |
+-----------+-------------+------+-----+---------+-------------------+
| id        | int(11)     | NO  | PRI  | NULL    | auto_increment    |
| firstname | varchar(20) | YES |      | NULL    |                   |
| lastname  | varchar(20) | YES |      | NULL    |                   |
| full_name | char(41)    | YES |      | NULL    | VIRTUAL GENERATED |
| email_id  | varchar(25) | YES |      | NULL    |                   |
+-----------+-------------+------+-----+---------+-------------------+
5 rows in set (0.00 sec)
	

We need to test the full name column, so populate a few row into the contacts table.

 
mysql> select * from mydbops_lab_test;
+----+-----------+----------+--------------+-----------------+
| id | firstname | lastname | full_name    | email_id        |
+----+-----------+----------+--------------+-----------------+
| 1  | john      | rubin    | john rubin   | rubin@gmail.com |
| 2  | mark      | henry    | mark henry   | mark@gmail.com  |
| 3  | peter     | parker   | peter parker | peter@yahoo.com |
| 4  | jim       | rose     | jim rose     | jim@outlook.com |
+----+-----------+----------+--------------+-----------------+
4 rows in set (0.00 sec)
	

Adding a virtual column on existing table :

 
mysql>  ALTER TABLE v_column  ADD full_name char(41) GENERATED ALWAYS AS (concat(firstname,' ',lastname)) VIRTUAL NOT NULL;
 Query OK, 0 rows affected (0.35 sec)
 Records: 0  Duplicates: 0  Warnings: 0
	

‍

Case 2 ( using difference ):

• In this example i will calculate the balance amount to the test table.So i created a new table with virtual column.

 
mysql > create table mydbops_lab_test_1
(id int(11) NOT NULL AUTO_INCREMENT primary key,
Total_cost float(9,4),
Expensive float(9,4),
Balance_amount float(12,6) GENERATED ALWAYS AS (Total_cost - Expensive),
email_id varchar(25),
name varchar(30));
 Query OK, 0 rows affected (0.34 sec)
	

 
mysql> desc mydbops_lab_test_1;
 +----------------+-------------+------+-----+---------+-------------------+
 | Field           | Type       | Null | Key | Default | Extra             |
 +----------------+-------------+------+-----+---------+-------------------+
 | id              | int(11)    | NO   | PRI | NULL    | auto_increment    |
 | Total_cost      | float(9,4) | YES  |     | NULL    |                   |
 | Expensive       | float(9,4) | YES  |     | NULL    |                   |
 | Balance_amount  | float(12,6)| YES  |     | NULL    | VIRTUAL GENERATED |
 | email_id        | varchar(25)| YES  |     | NULL    |                   |
 | name            | varchar(30)| NO   |     | NULL    |                   |
 +----------------+-------------+------+-----+---------+-------------------+
 6 rows in set (0.00 sec)
	

• I have inserted a few records on this ( mydbops_lab_test_1 ) table.We need to calculate the balance amount of the each person.

 
mysql> select id,name,Total_cost,Balance_amount from mydbops_lab_test_1 where Balance_amount < 2000 order by Balance_amount desc;
	

 
 +----+--------+------------+----------------+
 |id | name    | Total_cost | Balance_amount |
 +----+--------+------------+----------------+
 | 1 | rose    | 2000.0000  | 1949.109985    |
 | 5 | raj     | 810.1100   | 798.209961     |
 | 4 | kevin   | 900.0000   | 579.000000     |
 | 9 | suresh  | 677.1000   | 457.099976     |
 | 3 | jim     | 100.0000   | 71.000000      |
 | 8 | bell    | 41.9700    | 19.970001      |
 +----+--------+------------+----------------+
 6 rows in set (0.00 sec)
	

• The virtual columns are calculated each time data is read where as the stored column are calculated and stored physically when the data is updated.

‍

Indexing on Virtual Columns :

• In MySQL InnoDB supports the secondary indexes on virtual columns. Other type indexes are not supported ( Full Text / GIS ).
• A secondary index may be created on one or more virtual columns or on a combination of virtual columns and regular columns or stored generated columns. Secondary indexes that include virtual columns may be defined as unique.

Example :Here I have added the index on (Balance_amount) virtual generated column.Syntax:

 
mysql> alter table mydbops_lab_test_1 add index idx_Balance_amount(Balance_amount);
 Query OK, 0 rows affected (0.33 sec)
 Records: 0 Duplicates: 0 Warnings: 0
	

 
mysql> show create table mydbops_lab_test_1\G
 *************************** 1. row ***************************
 Table: mydbops_lab_test_1
 Create Table: CREATE TABLE `mydbops_lab_test_1` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `Total_cost` float(9,4) DEFAULT NULL,
 `Expensive` float(9,4) DEFAULT NULL,
 `Balance_amount` float(12,6) GENERATED ALWAYS AS ((`Total_cost` - `Expensive`)) VIRTUAL,
 `email_id` varchar(25) DEFAULT NULL,
 `name` varchar(30) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_Balance_amount` (`Balance_amount`)
 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
 1 row in set (0.00 sec)
	

Advantages :

• Virtual generated columns can be used as a way to simplify and unify queries.
• A complicated condition can be defined as a generated column and referred to from multiple queries on the table to ensure that all of them use exactly the same condition.
• It can be useful for working with columns of types that cannot be indexed directly, such as JSON columns
• The column [ NOT NULL ] is not supported with MariaDB while it’s allowed in 5.7 only.

Disadvantages:

• The disadvantage of virtual columns is that values are stored twice, once as the value of the generated column and once in the index.
• If a generated column is indexed, the optimizer recognizes query expressions that match the column definition and uses indexes from the column as appropriate during query execution.

‍

Key points to remember :

Generated column expressions follows some rules:

• Sub-queries, parameters, variables, stored functions and user-defined functions are not permitted.
• A generated column definition can refer to other generated columns, but only those occurring earlier in the table definition.
• An auto_increment column cannot be used as a base column in a generated column definition.
• A create table like the destination table preserves generated column information from the original table.
• A create table select the destination table does not preserve information about whether columns in the selected-from table are generated columns.

The Select part of the statement cannot assign values to generated columns in the destination table.

Read more expert insights on MyDBOPS Blog: Visit MyDBOPS Blog

‍

FAQs

1. What is a MySQL virtual column?‍

A MySQL virtual column is a special type of column that doesn't store data physically in the database. Instead, its value is calculated on the fly using an expression based on other columns in the same table. This makes it a flexible and storage-efficient option for deriving values without duplicating data.

2. What is the difference between stored and virtual columns in MySQL?‍

The key difference lies in how they handle data:

• Virtual columns are computed at query time and do not occupy storage unless indexed.
• Stored columns are calculated once when data is inserted or updated, and the result is saved physically, consuming storage space.
  Use virtual columns when performance isn't critical; use stored columns when frequent queries require faster access.

3. What is a virtual column?‍

A virtual column is a computed column whose value is dynamically derived from other columns using a predefined expression. It's part of the table schema but doesn't hold data itself unless explicitly indexed. Virtual columns are useful for simplifying complex queries, enforcing consistency, and improving maintainability.

4. What is the difference between a virtual column and a view?‍

A virtual column exists within a table and is tied directly to its structure, offering column-level computation.
A view, on the other hand, is a saved SQL query that can combine data from multiple tables and present it as a virtual table.
In short:

• Use virtual columns for lightweight, computed fields within a single table.
• Use views for complex data aggregation or combining data across tables.

‍