Virtual Columns in MySQL and Use cases.

Jul 19, 2017

Mins to Read

All

Illustration representing Virtual Columns in MYSQL and use cases

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

MySQL

Features

No items found.

Virtual Columns in MySQL and Use cases.

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

What is Virtual Column ?

Syntax :

Use Cases:

Case 1 ( using concat ):

Case 2 ( using difference ):

Indexing on Virtual Columns :

Advantages :

Disadvantages:

Key points to remember :

About the Author

Topic covered

Continue Reading

Scaling TiDB for Large-Scale Applications: Webinar Highlights

Why Startups and Enterprises are Adopting PostgreSQL

Optimistic and Pessimistic Locking in TiDB

Best Practices for Managing MongoDB Log Files and System Resources

Subscribe Now!

Virtual Columns in MySQL and Use cases.

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

What is Virtual Column ?

Syntax :

Use Cases:

Case 1 ( using concat ):

Case 2 ( using difference ):

Indexing on Virtual Columns :

Advantages :

Disadvantages:

Key points to remember :

About the Author

Topic covered

Continue Reading

Scaling TiDB for Large-Scale Applications: Webinar Highlights

Why Startups and Enterprises are Adopting PostgreSQL

Optimistic and Pessimistic Locking in TiDB

Best Practices for Managing MongoDB Log Files and System Resources

Subscribe Now!

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