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.
• 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. If no column of given name exists, NULL is returned.

COLUMN_DELETE

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

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

COLUMN_EXISTS

COLUMN_EXISTS(dynamic_column, column_nr);
COLUMN_EXISTS(dynamic_column, column_name);

Check if a column with specified name exists. If yes, return 1, otherwise return 0.

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.

This function was introduced in MariaDB 10.0.1.

COLUMN_JSON

COLUMN_JSON(dynamic_column);

Return a JSON representation of a dynamic column.

This function was 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 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.