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

-- Remove a column:
update assets set dynamic_cols=COLUMN_DELETE(dynamic_cols, "price") 
where COLUMN_GET(dynamic_cols, 'color' as char)='black'; 

-- Add a column:
update assets set dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '3 years')
where item_name='Thinkpad Laptop';

Finally, you can get all columns at once 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]]...);

Return a dynamic columns blob that stores the specified columns with values.

The return value is suitable for

• storing it in a table
• further modification with other dynamic columns functions.

The as type part allows to specify value type. In most cases, this is redundant, because MariaDB will be able to deduce the type of the value. Explicit type specification may be needed when type of the value is not apparent. For example, a literal '2012-12-01' has a CHAR type by default, one will need to specify '2012-12-01' AS DATE to have it stored as a date. See Dynamic Column types for further details.

COLUMN_ADD

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

Adds or updates dynamic columns.

• dyncol_blob must be either a valid dynamic columns blob (for example, COLUMN_CREATE returns such blob), or an empty string.
• column_name specifies the name of the column to be added. If dyncol_blob already has a column with this name, it will be overwritten.
• value specifies the new value for the column. Passing a NULL value will cause the column to be deleted.
• as type is optional. See #column_types for discussion about types.

The return value is a dynamic column blob after the modifications.

Typical usage:

-- MariaDB 5.3+:
UPDATE tbl SET dyncol_blob=COLUMN_ADD(dyncol_blob, 1 /*column id*/, "value") WHERE id=1;
-- MariaDB 10.0.1:
UPDATE t1 SET dyncol_blob=COLUMN_ADD(dyncol_blob, "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(dyncol_blob, column_nr as type);
COLUMN_GET(dyncol_blob, column_name as type);

Get the value of a dynamic column by its name. If no column with given name exists, NULL will be returned.

column_name as type requires that one specifies the datatype of the dynamic column he is reading.

This may seem counter-intuitive: why would one need specify which datatype he's retrieving? Can't dynamic columns system figure the datatype from the data being stored?

The answer is: SQL is a statically-typed language. SQL interpreter needs to know the datatypes of all expressions before the query is run (for example, when one is using prepared statements and runs "select COLUMN_GET(...)", prepared statement API requires the server to inform the client about the datatype of the column being read before the query is executed and the server can see what datatype the column actually has).

COLUMN_DELETE

COLUMN_DELETE(dyncol_blob, column_nr, column_nr...);
COLUMN_DELETE(dyncol_blob, column_name, column_name...);

Delete a dynamic column with specified name. Multiple names can be given.

The return value is dynamic column blob after the modification.

COLUMN_EXISTS

COLUMN_EXISTS(dyncol_blob, column_nr);
COLUMN_EXISTS(dyncol_blob, column_name);

Check if a column with name column_name exists in dyncol_blob. If yes, return 1, otherwise return 0.

COLUMN_LIST

COLUMN_LIST(dyncol_blob);

Before MariaDB 10.0.1: Return a comma-separated list of column numbers. After MariaDB 10.0.1: Return a comma-separated list of column names. The names are quoted with backticks.

Example using MariaDB 10.0.1:

MariaDB [test]> select column_list(column_create('col1','val1','col2','val2'));
+---------------------------------------------------------+
| column_list(column_create('col1','val1','col2','val2')) |
+---------------------------------------------------------+
| `col1`,`col2`                                           |
+---------------------------------------------------------+

COLUMN_CHECK

COLUMN_CHECK(dyncol_blob);

Check if dyncol_blob is a valid packed dynamic columns blob. Return value of 1 means the blob is valid, return value of 0 means it is not.

Rationale: Normally, one works with valid dynamic column blobs. Functions like COLUMN_CREATE, COLUMN_ADD, COLUMN_DELETE always return valid dynamic column blobs.

However, if dynamic column blob is accidentally truncated, or transcoded from one character set to another, it will be corrupted. This function can be used to check if a value in a blob field is a valid dynamic column blob.

This function was introduced in MariaDB 10.0.1.

COLUMN_JSON

COLUMN_JSON(dyncol_blob);

Return a JSON representation of data in dyncol_blob.

Example:

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

Limitation: currently, maximum depth is limited to 10. If the data in dyncol_blob is nested deeper than 10 levels, then data at levels deeper than level 10 will not be decoded.

This function was introduced in MariaDB 10.0.1.

Nesting dynamic columns

It is possible to use nested dynamic columns by putting one dynamic column blob inside another. COLUMN_JSON function will display nested columns.

MariaDB [test]> set @tmp= column_create('parent_column', column_create('child_column', 12345));
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select column_json(@tmp);
+------------------------------------------+
| column_json(@tmp)                        |
+------------------------------------------+
| {"parent_column":{"child_column":12345}} |
+------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> select column_get(column_get(@tmp, 'parent_column' as char), 'child_column' as int);
+------------------------------------------------------------------------------+
| column_get(column_get(@tmp, 'parent_column' as char), 'child_column' as int) |
+------------------------------------------------------------------------------+
|                                                                        12345 |
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Datatype support

.

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 implementation, 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.