What is Virtual columns in MySQL and How to use it? Example Tutorial

 What is a Virtual Column and where is it used?

Virtual/generated columns are a new feature in MySQL 5.7. Because the data in this column is computed based on a specified expression or from other columns, it is called a generated column.

Virtual columns resemble regular table columns in appearance, but their values are generated rather than saved on a disc.

Virtual columns are one of MySQL 5.7's most useful features; they allow you to store a value in a new field that is derived from one or more other fields in the same table.

How to add a new Virtual Column?

The ALTER TABLE procedures ADD, MODIFY, and CHANGE are allowed for produced columns.

CREATE TABLE t1 (c1 INT);
ALTER TABLE t1 ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;

We can observe many new things in this query, such as 'generated always', 'stored' and there's one more to look out for, 'virtual'. Let's see what this all signifies.

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

VIRTUAL – The column contents are not saved, but when rows are read, they are assessed.

STORED – The column virtues are assessed and saved when rows are inserted or updated.

Uses of Virtual columns

1. Using concatenation

Now, let's see one of the most common tables and most common problems associated with it. Suppose you have a Customer table with first name, last name as columns. Now, you also want the full name of the customer to be stored as a separate column which would basically be first name + last name. 

The query to use a virtual column for a full name that would automatically generate the full name is as below : 

create table Customer
(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));

We'll need to add a few rows in the contacts database to test the complete name field.

INSERT INTO Customer(firstname, lastname, email_id) 
VALUES('virat', 'kohli', 'vk@rcb.com');

INSERT INTO Customer(firstname, lastname, email_id) 
VALUES('rohit', 'sharma', 'rs@mi.com');

INSERT INTO Customer(firstname, lastname, email_id) 
VALUES('Ravindra', 'jadeja', 'rj@csk.com');

INSERT INTO Customer(firstname, lastname, email_id) 
VALUES('aakash', 'chopra', 'ak@vella.com');

The table output would give us the test results. Do a select  * from the table and observer what we get.

Now, what if a table already exists. You would need to alter the structure and add a virtual column to it. The query for the same is as below.

ALTER TABLE Customer  ADD full_name char(41) GENERATED 

ALWAYS AS (concat(firstname,' ',lastname)) VIRTUAL NOT NULL;

2. Using a difference

I'll compute the balance amount to the test table in this case. So I made a new table and added a virtual column to it.

create table Cost
(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));

The table structure would look like below : 

On this (Customer) table, I've added a few records. We need to figure out how much each person's balance is. Let's enter some data into the table as follows : 

INSERT INTO Cost(Total_cost, Expensive, email_id, name) VALUES 
(234.7, 143.9, 'vk@rcb.com', 'Virat');

INSERT INTO Cost(Total_cost, Expensive, email_id, name) VALUES 
(6554.456, 123.9877, 'rs@mi.com', 'rohit');

INSERT INTO Cost(Total_cost, Expensive, email_id, name) VALUES 
(106.3, 234.5, 'yc@rcb.com', 'chahal');

Now, to check the test results, just select all the entries from the table.

When data is read, virtual columns are computed and saved physically, but when data is changed, stored columns are calculated and stored physically.

Key takeaway points: 

• There are no sub-queries, arguments, variables, stored functions, or user-defined functions allowed.
• Other produced columns can be referenced by a generated column definition, but only those that appear earlier in the table definition.
• In a generated column definition, an auto-increment column cannot be utilized as a basis column.
• The created column information from the source table is preserved in a create table, just as it is in a destination table.
• The information about whether columns in the selected-from table are created columns is not preserved when using a create table choose the destination table.

The Select component of the statement is unable to give values to the destination table's created fields.

Conclusion

As you all have seen, virtual columns are very important and perform a significant task in improving the business logic at the database level. Due to virtual columns, there would be less burden on the program layer and more smooth operations of business logic will happen as there would be fewer chances of error.