The Virtual columns are computed columns. They will be
computed based on information inserted/Updated.
In MariaDB two types of virtual columns are available.
- VIRTUAL virtual
columns
- 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
