Virtual columns in MySQL and their use cases

Introduction:

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

What is a virtual column?

• In general, virtual columns look like regular table columns, but their values are derived rather than stored on disk.
• Virtual columns are one of the core features of MySQL 5.7 — they can store a value derived from one or more other fields of the same table in a new field.

Syntax:

The syntax for adding a new virtual column,

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

Example :

Alter the contacts table to add a column that is always generated as mydbops_test virtual / stored.

GENERATED ALWAYS - indicates that the column is a generated column.

VIRTUAL - column values are not stored, but they are evaluated when rows are read.

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

Use cases:

Case 1 (using concat):

For example, we have the mydbops_lab_test table structure as shown 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 let's populate a few rows in 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 to an 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 calculate the balance amount for a test table, so I created a new table with a virtual column.

mysql> create table mydbops_lab_test_1
(id int (11) NOT NULL AUTO_INCREMENT primary key,
Total_cost float (9,4),
Discount float (9,4),
Balance_amount float (12,6) GENERATED ALWAYS AS (Total_cost - Discount),
email_id varchar (25),
firstname 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 | |
 | Discount | 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 inserted a few records into this table (mydbops_lab_test_1). We need to calculate the sum of each person's balance.

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)

• Virtual columns are computed every time the data is read, whereas a stored column is computed and physically saved when the data is updated.

Indexing virtual columns:

• In MySQL, InnoDB supports secondary indexes on virtual columns. Other index types (Full Text / GIS) are not supported.
• A secondary index can 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 can be defined as unique.

Example:

Here I've added an index for the virtual generated column (Balance_amount).

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,
 `Discount` float (9,4) DEFAULT NULL,
 `Balance_amount` float (12,6) GENERATED ALWAYS AS ((`Total_cost` - `Discount`)) 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:

• Virtually generated columns can be used as a way to simplify and unify queries.
• A complex condition can be defined as a generated column and referenced from multiple queries on the table, to guarantee that they all use exactly the same condition.
• This can be useful for working with column types that can't be indexed directly, such as JSON columns.
• The [NOT NULL] column is not supported by MariaDB, and is only allowed starting with 5.7.

Disadvantages:

• The downside of virtual columns is that the values are stored twice: once as the value of the generated column, and once in the index.
• If the generated column is indexed, the optimizer recognizes query expressions that match the column's definition and uses the corresponding indexes from the column when executing the query.

Key points to remember:

Generated column expressions are subject to a few rules:

• Subqueries, parameters, variables, stored functions, and user-defined functions are not allowed.
• A generated column's definition can refer to other generated columns, but only to those that appear earlier in the table definition.
• An auto_increment column cannot be used as the base column in a generated column definition.
• Creating a table like a destination table preserves the generated-column information from the source table.
• Creating a table with a select into a destination table does not preserve information about whether the selected columns from the table are generated columns.

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