Virtual Columns (A feature of MariaDB)

The Virtual columns are computed columns. They will be computed based on information inserted/Updated.

In MariaDB two types of virtual columns are available.

1. VIRTUAL virtual columns
2.  PERSISTENT virtual columns

Virtual virtual columns as truly virtual; their value is not stored and they have no actual existence apart from the table definition. They act like regular columns in queries, but their content is always calculated on the fly and never written to disk. You might be thinking that this could lead to a performance penalty; so to help with performance, if an SQL query doesn’t reference a virtual virtual column, the value is not calculated.

Persistent virtual columns are half-way between being truly virtual and being regular columns. The main difference compared to virtual virtual columns is that the calculated data is actually stored in the database.

The imaginary use case is that there is a sales team in a company and you would like to evaluate if a particular salesperson is eligible for a bonus. In order to get a bonus, you need to sell above the average for the day and be in the top 5 amongst the salespeople.

So here is what I did:

Create table to store data

MariaDB [virtest]>  create table salespeople (id int unsigned not null auto_increment primary key, salesperson_id int unsigned not null , `date` datetime not null default 0, sold decimal(15,2) not null default 0, day_avg decimal(15,2) not null default 0,  above_avg char(1) as (if(sold>day_avg,'Y','N')) virtual);

Query OK, 0 rows affected (0.01 sec)

Now inserted a data row..

MariaDB [virtest]> insert into salespeople(salesperson_id, `date`, sold, day_avg) values (1,now(),300,150);

Query OK, 1 row affected (0.01 sec)

Selected data from table…

MariaDB [virtest]> select * from salespeople;

+----+----------------+---------------------+--------+---------+-----------+

| id | salesperson_id | date                | sold   | day_avg | above_avg |

+----+----------------+---------------------+--------+---------+-----------+

|  1 |              1 | 2012-06-27 20:07:22 | 300.00 |  150.00 | Y         |

+----+----------------+---------------------+--------+---------+-----------+

Now updating row with new data..

MariaDB [virtest]> update salespeople set sold = 149 where id = 1;

Query OK, 1 row affected (0.09 sec)

Rows matched: 1  Changed: 1  Warnings: 0

See the result after update

MariaDB [virtest]> select * from salespeople;

+----+----------------+---------------------+--------+---------+-----------+

| id | salesperson_id | date                | sold   | day_avg | above_avg |

+----+----------------+---------------------+--------+---------+-----------+

|  1 |              1 | 2012-06-27 20:07:22 | 149.00 |  150.00 | N         |

+----+----------------+---------------------+--------+---------+-----------+

Now you can calculate column value at runtime. 

Enjoy J