Road to MariaDB 5.2: Virtual Columns

MariaDB 5.2 is almost here. The gamma release (think “RC”) was released on 28 Sep and the stable release will follow just as soon as the developers are happy with it.

One of the best new features of MariaDB 5.2 is Virtual Columns.

What are virtual columns you say?

Well, I suppose I should start by backing up a bit and saying that there are two types of virtual columns: VIRTUAL virtual columns and PERSISTENT virtual columns. (I know, I know… saying virtual twice is a little strange).

Ignoring the semantics, I think of 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.

More information on the syntax to use when creating a table with virtual columns, and some things to be aware of, see the Virtual Columns entry in the Askmonty.org Knowledgebase.

Enough talk, let’s see virtual columns in action.

Warning: The examples below are simplistic. In my defense, my job is to document how a feature works, not to wow anyone with all of the cool ways a feature can or could be used (and abused). Let me just say the examples work for my purposes here, but are not very useful, imaginative, or practical. 🙂
MariaDB [test]> create table table1 (
-> a int not null,
-> b int as (a + 10) persistent);
Query OK, 0 rows affected (0.28 sec)

MariaDB [test]> insert into table1 (a) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.12 sec)
Records: 4 Duplicates: 0 Warnings: 0

MariaDB [test]> select * from table1;
+---+------+
| a | b    |
+---+------+
| 1 | 11   |
| 2 | 12   |
| 3 | 13   |
| 4 | 14   |
+---+------+
4 rows in set (0.00 sec)

Tip: Don’t try to INSERT into a virtual column. MariaDB will just ignore you (and complain).

Now what happens if I do an alter table?

MariaDB [test]> alter table table1
-> change b
-> b int as (a + 100) persistent;
Query OK, 4 rows affected (0.41 sec)
Records: 4 Duplicates: 0 Warnings: 0

MariaDB [test]> select * from table1;
+---+------+
| a | b    |
+---+------+
| 1 |  101 |
| 2 |  102 |
| 3 |  103 |
| 4 |  104 |
+---+------+
4 rows in set (0.00 sec)

As you can see, the values in the virtual column were automatically updated based on the new virtual column definition. Pretty cool, no?

The alternative to using virtual columns would be to use triggers, but if you change your trigger, the old generated values will not be updated automatically. Granted this may be the behavior you need, but then again, it might not be. If you would like the generated data to be automatically updated, use virtual columns; if not, use triggers. In my opinion, virtual columns are much easier to update and maintain compared to triggers, but maybe that’s just me.

For example, consider a table with (among other things) a price_usd column representing a price in USD (United States Dollars) and a price_eur column representing a price in EUR (Euros). For the purposes of this example, let’s pretend the price_eur column is either a persistent virtual column or is filled by a trigger automatically. If you are using the table to record sales, you probably don’t want old entries to be updated if they represent the exchange rate between EUR and USD on the day the entry was entered, the the trigger-filled version is probably what you want since it will record the price in USD and EUR when the sale was made. On the other hand, if the table is used to record current prices, you will want all of the generated columns to be updated if the exchange rate changes, so the virtual columns version is the one you want to use.

Anyway, virtual columns is one of the nifty new features in MariaDB 5.2. Check it out!