Virtual Columns in MySQL and Use cases.

Introduction:

  • 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s