Programster: MariaDB - Virtual Columns

Virtual columns are defined by a relationship between other non-virtualized columns in your database. Thus the data is always derived and never inserted.

This feature is not available in MySQL databases. However, it is easy to upgrade your MySQL database to a MariaDB which is a "drop in replacement".

Why Are Virtual Columns Useful?

They help keep your data normalized, whilst allowing you to access it in the form you want. For example, imagine that as a policy, all of your systems must use meters for the unit of measurement. However, it would be silly to display such large numbers to users on your website, so you display in units of Kilometers instead. This would mean that you could either:

Duplicate your data by dividing by 1000 in your database (non-normalized form) and ensure to manually keep this relationship whenever you perform an update or insert.
Fetch the data in meters and keep dividing by 1000 in your web application.

Virtual columns allow you to fetch the data in kilometers, whilst also not having to duplicate your data or performing manual calculations throughout your codebase. This can be achieved with the example table definition below:

CREATE TABLE sections (
     section_id INT NOT NULL AUTO_INCREMENT,
     distance_meters INT (11),
     distance_kilometers FLOAT (11,5) AS (distance_meters / 1000) VIRTUAL,
     PRIMARY KEY ( section_id )
) ENGINE=InnoDB;

To demonstrate this in action, simply run the following two queries after creating the table.

INSERT INTO sections SET distance_meters=2543;
SELECT * FROM sections;

This should produce the following output:

As you can see, I only inserted the distance in meters, but I fetched data in kilometers as wel!

Virtual Or Persistent?

There are actually two different types of virtual columns. These are "persistent" and "virtual".

I have no idea why they didn't use the word "temporary" for the alternative to "persistent". This means we have "virtual virtual columns".

Virtual virtual columns are dynamically calculated at the point of the column being queried. This means they are not stored in the database and hence take up no storage. The drawback is that this can makes your read queries take longer and is very inefficient if you run these often.

Persistent virtual columns are physically stored in the database and the data only ever has to be calculated once at the point of any relevant writes. This may slow down your write queries and is wasteful if you hardly ever read that data. It also uses up your storage capacity.

If you don't specify persistent or virtual, the default is virtual

To make a persistent table, simply specify "PERSISTENT" rather than "VIRTUAL" as shown in the previous example that I will now modify.

CREATE TABLE sections (
     section_id INT NOT NULL AUTO_INCREMENT,
     distance_meters INT (11),
     distance_kilometers FLOAT (11,5) AS (distance_meters / 1000) PERSISTENT,
     PRIMARY KEY ( section_id )
) ENGINE=InnoDB;

More Examples

Greatest Value

CREATE TABLE table1 (
     id INT NOT NULL AUTO_INCREMENT,
     value1 INT(11),
     value2 INT(11),
     value3 INT(11) AS (GREATEST(value1, value2)),
     PRIMARY KEY ( id )
) ENGINE=InnoDB;

INSERT INTO table1 
SET 
    value1='1',
    value2='2';

INSERT INTO table1 
SET 
    value1='4',
    value2='3';

select * from table1;
+----+--------+--------+---------------+
| id | value1 | value2 | maximum_value |
+----+--------+--------+---------------+
|  1 |      1 |      2 |             2 |
|  2 |      4 |      3 |             4 |
+----+--------+--------+---------------+

Adding Up The Costs

CREATE TABLE sums_table (
     id INT NOT NULL AUTO_INCREMENT,
     cost1 INT(11),
     cost2 INT(11),
     total_cost INT(11) AS (cost1 + cost2),
     PRIMARY KEY ( id )
) ENGINE=InnoDB;

INSERT INTO sums_table 
SET 
    cost1='10',
    cost2='25';


select * from sums_table;
+----+-------+-------+------------+
| id | cost1 | cost2 | total_cost |
+----+-------+-------+------------+
|  1 |    10 |    25 |         35 |
+----+-------+-------+------------+

Get the Total Price Including VAT

CREATE TABLE products (
     id INT NOT NULL AUTO_INCREMENT,
     price FLOAT(11,2),
     inc_vat FLOAT(11,2) AS (price * 1.2),
     PRIMARY KEY ( id )
) ENGINE=InnoDB;

INSERT INTO products SET price='9.99';

select * from products;
+----+-------+---------+
| id | price | inc_vat |
+----+-------+---------+
|  1 |  9.99 |   11.99 |
+----+-------+---------+

Hashing Columns

Hashes allow you to quickly check if something has changed without having to check each individual value. This is why they are used in version control tools, to quickly see if a file or "state" has changed (ever noticed that Git uses hashes instead of version numbers like SVN?). In this case, we want to be able to easily check if employees names or salaries have changed without having to loop through every name and salary.

CREATE TABLE employees (
    id INT NOT NULL,
    name VARCHAR(200),
    salary INT(12),
    age int(21),
    hash VARCHAR(200) AS (MD5(concat(name, salary))) PERSISTENT,
    PRIMARY KEY ( id )
) ENGINE=InnoDB;

INSERT INTO employees SET name='steve', age=3, salary='12000';
+----+-------+--------+------+----------------------------------+
| id | name  | salary | age  | hash                             |
+----+-------+--------+------+----------------------------------+
|  0 | steve |  12000 |   21 | 9065f85a9f60f1be5c03678174efbdef |
+----+-------+--------+------+----------------------------------+

Now I can come back much later with my hash list

MariaDB - Virtual Columns