This is an introduction to two relatively new amazing features in MariaDB: Dynamic Columns and Virtual Columns. Dynamic columns allow storage of key-value pairs in blob columns, which is useful if you have a lot of attributes to store that may be different on a per-row basis. Virtual columns can be used like mini-views to add a column whose value is always calculated by some expression.

In this introduction I'll create a dynamic column and store some attributes, create a virtual column that exposes one of those attributes as a column, and index that for speed. I'm working with MariaDB 10.0.25 which is available in Debian 8 Jessie.

Simple example

Create a normal table with a blob field - I've called it attrs - and also create our virtual field that will access the data tied up in the blob.

CREATE TABLE people
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY_KEY,
  name VARCHAR(100),
  attrs blob NOT NULL,
  age int(3) AS (COLUMN_GET(attrs, 'age' AS INT)) PERSISTENT,
  KEY (age);

Add some data to the table.

INSERT INTO people (name, attrs) VALUES
  ("Rich", COLUMN_CREATE("age", 41, "fave_colour", "cyan")),
  ("Wilma", COLUMN_CREATE("age", 253, "fave_colour", "green"));

Take a look: we can select the age, and if we use age in a WHERE clause, it's going to use the index. Nice!

MariaDB> SELECT name, age FROM people;
+-------+------+
| name  | age  |
+-------+------+
| Rich  |   41 |
| Wilma |  253 |
+-------+------+


MariaDB> explain select name, age from people where age>100;
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
|    1 | SIMPLE      | t     | range | age           | age  | 5       | NULL |    1 | Using index condition |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

Check it works when we update the data.

MariaDB> UPDATE people SET attrs=COLUMN_ADD(attrs, "age", age+1);
MariaDB> SELECT name, age FROM people;
+-------+------+
| name  | age  |
+-------+------+
| Rich  |   42 |
| Wilma |  254 |
+-------+------+

Working with columns: COLUMN_CREATE or COLUMN_ADD?

If you're keen-eyed you'll have noticed I used COLUMN_CREATE in the INSERT and COLUMN_ADD in the UPDATE. They each take one or more key-value pairs in a list, but  COLUMN_ADD takes the blob column as its first parameter and alters this by adding or updating the given keys, whereas COLUMN_CREATE creates a new, empty dynamic column store with key-value pairs you specify.

The problem then arises for an arbitrary update: do we have to check the existence of a column and update/create it? Well it turns out that COLUMN_ADD will happily work with an empty string as its input (but not NULL), which is why we set the attrs column as NOT NULL.