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
.
COLUMN_ADD
but ensure your blob column is defined with NOT NULL
or COLUMN_ADD
will silently fail to record anything in a new record. Alternatively use attrs=COLUMN_ADD(COALESCE(attrs, ""), ...)
Add new comment