tmp

Dynamic columns feature allows to store different sets of columns for each row in the table. It works by storing a set of columns in a blob and having a small set of functions to manipulate it.

Dynamic columns should be used when it is not possible to use regular columns. A typical use case is when one needs to store items that may have many different attributes (like size, color, weight, etc), and the set of attributes varies between the items.

The basics

The table should have a blob column which will be used as a storage for dynamic columns:

create table assets (
  item_name     varchar(32) primary key, -- A common attribute for all items
  dynamic_cols  blob  -- Dynamic columns will be stored here
);

Then, one can access the columns like so:

MariaDB [test]> insert into assets values 
    ->   ('MariaDB T-shirt', COLUMN_CREATE('color', 'blue', 'size', 'XL')),
    ->   ('Thinkpad Laptop', COLUMN_CREATE('color', 'black', 'price', '500'));
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> select item_name, COLUMN_GET(dynamic_cols, 'color' as char) as color from assets;
+-----------------+-------+
| item_name       | color |
+-----------------+-------+
| MariaDB T-shirt | blue  |
| Thinkpad Laptop | black |
+-----------------+-------+
2 rows in set (0.00 sec)

(note: the example uses MariaDB 10.0.1. In MariaDB 5.3, columns can only be identified by numbers. See [#mariadb-5.3] below)

It is possible to add or remove dynamic columns:

MariaDB [test]> update assets set dynamic_cols=COLUMN_DELETE(dynamic_cols, "price") where COLUMN_GET(dynamic_cols, 'color' as char)='black'; 
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> update assets set dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '3 years') where item_name='Thinkpad Laptop';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> select item_name, column_json(dynamic_cols) from assets;
+-----------------+----------------------------------------+
| item_name       | column_json(dynamic_cols)              |
+-----------------+----------------------------------------+
| MariaDB T-shirt | {"size":"XL","color":"blue"}           |
| Thinkpad Laptop | {"color":"black","warranty":"3 years"} |
+-----------------+----------------------------------------+
2 rows in set (0.00 sec)