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 dynamic col or remove dynamic columns in a row:

-- Removing a column:
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

-- Adding a column:
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 (starting from MariaDB 10.0.1):

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"} |
+-----------------+----------------------------------------+

Subsequent sections have a complete reference.

Dynamic columns 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]]...);

Creates a dynamic column object.

'type' is not required and is mostly there for testing except in cases like date/time/datetime where you can't create literals of the type you need.

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

COLUMN_GET(dynamic_column, column_nr as type);
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);

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

COLUMN_LIST

COLUMN_LIST(dynamic_column);

Returns a string of all columns in the dynamic_column separated with ','. Since MariaDB 10.0.1 column names are quoted.

COLUMN_CHECK

COLUMN_CHECK(dynamic_column);

This function returns 1 if the column is OK or 0 if problems were found.

Introduced in MariaDB 10.0.1.

COLUMN_JSON

Gets dynamic column content as JSON.

COLUMN_JSON(dynamic_column);

This function returns a text string with a JSON object inside. All values are converted to strings except dynamic column type (new for MariaDB 10.0.1) which is represented as an embedded JSON object (the level of embedding object output is limited in the current representation to 10, but output is only limited internally. One could create as deep embedding as one wants).

Introduced in MariaDB 10.0.1.

MariaDB 5.3 vs MariaDB 10.0

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.