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 possible attributes is very large and/or unknown in advance. In that case, attributes can be put into dynamic columns.

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 in a row:

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

Finally, you can get all columns at once packed in JSON format (again, starting from MariaDB 10.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)

Subsequent sections have a complete reference.

SQL Functions

COLUMN_CREATE

COLUMN_CREATE(column_nr, value [as type], [column_nr, value [as type]]...);
COLUMN_CREATE(column_name, value [as type], [column_name, value [as type]]...);

COLUMN_ADD

COLUMN_ADD(dynamic_col, column_nr, value [as type], [column_nr, value [as type]]...);
COLUMN_ADD(dynamic_col, column_name, value [as type], [column_name, value [as type]]...);

Adds or updates dynamic columns.

• 'dynamic_col' must either be data created by COLUMN_CREATE() or an empty string.
• If you add a column that already exists, the new value will replace it.
• If you add NULL for a column, the entry will be deleted.
• type is optional, when not specified, it is detected automatically.

Typical usage:

-- MariaDB 5.3+:
UPDATE tbl SET dynamic_col=COLUMN_ADD(dynamic_col, 1 /*column id*/, "value") WHERE id=1;
-- MariaDB 10.0.1+:
UPDATE t1 SET dynamic_col=COLUMN_ADD(dynamic_col, "column_name", "value") WHERE id=1;

Note: COLUMN_ADD() is a regular function (just like CONCAT()), hence, in ourder to update the value in the table you have to use the UPDATE ... SET dynamic_col=COLUMN_ADD(dynamic_col, ....) pattern.

COLUMN_GET

-- MariaDB 5.3+:
COLUMN_GET(dynamic_column, column_nr as type);
-- MariaDB 10.0.1+:
COLUMN_GET(dynamic_column, column_name as type);

Get the value of dynamic column.

COLUMN_DELETE

-- MariaDB 5.3+:
COLUMN_DELETE(dynamic_column, column_nr, column_nr...);
-- MariaDB 10.0.1+:
COLUMN_DELETE(dynamic_column, column_name, column_name...);

Delete a dynamic column with a specified name.

COLUMN_EXISTS

-- MariaDB 5.3+:
COLUMN_EXISTS(dynamic_column, column_nr);
-- MariaDB 10.0.1+:
COLUMN_EXISTS(dynamic_column, column_name);

Retur 1 if a column with specified name (or number) exists, and 0 otherwise.

COLUMN_LIST

COLUMN_LIST(dynamic_column);

Returns a comma-separated list of all columns in the dynamic_column. Starting from MariaDB 10.0.1 column names are quoted.

MariaDB [test]> select COLUMN_LIST(dynamic_cols) from assets;
+---------------------------+
| COLUMN_LIST(dynamic_cols) |
+---------------------------+
| `size`,`color`            |
| `color`,`warranty`        |
| `foo`                     |
+---------------------------+

COLUMN_CHECK

-- MariaDB 10.0.1+
COLUMN_CHECK(dynamic_col);

Return 1 if dynamic_col is a valid packed dynamic column blob, and 0 otherwise.

This function was introduced in MariaDB 10.0.1.

COLUMN_JSON

-- MariaDB 10.0.1+
COLUMN_JSON(dynamic_column);

Get dynamic column content as a JSON string.

TODO: why this limitation: For nested dynamic columns, maximum depth is 10. If dynamic_column is a blob with a deeper nesting, "deep" structures will be returned as blob, without decoding to JSON.

This function was introduced in MariaDB 10.0.1.

MariaDB 5.3 vs MariaDB 10.0

TODO FIX MESSY WORDING BELOW

The first implementation of dynamic columns was meant to be highly efficient for programs that generate SQL code (which is what we belive most store applications today that try to handle objects with different attributes use). So for the original implementaion, access to dynamic columns was via numbers. In MariaDB 10.0.1 name support for dynamic columns was added.

So there are 2 formats for Dynamic Columns:

1. numeric column referencing
2. named column referencing

Versions of MariaDB from 5.3 through 10.0.0 only support numeric referencing.

Starting in 10.0.1, MariaDB can read and write either format and convert tables from the old format to the new one (conversion is one-way and cannot be reversed). Conversion happens if you add names to records which use the numeric format.

When creating columns, MariaDB 10.0.1 and up will create columns in the numeric format if all column references are numeric, otherwise it will use the named format.