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.
%20vs%20systemctl%20stop%20(2).png)
.svg)
