Dynamic Columns

Dynamic columns allow one to store different sets of columns for each row in a 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.

Dynamic Columns Basics

The table should have a blob column which will be used as 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
);

Once created, one can access dynamic columns via dynamic column functions:

Insert a row with two dynamic columns: color=blue, size=XL

INSERT INTO assets VALUES 
  ('MariaDB T-shirt', COLUMN_CREATE('color', 'blue', 'size', 'XL'));

Insert another row with dynamic columns: color=black, price=500

INSERT INTO assets VALUES
  ('Thinkpad Laptop', COLUMN_CREATE('color', 'black', 'price', 500));

Select dynamic column 'color' for all items:

SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color FROM assets;
+-----------------+-------+
| item_name       | color |
+-----------------+-------+
| MariaDB T-shirt | blue  |
| Thinkpad Laptop | black |
+-----------------+-------+

(note: the above example uses MariaDB 10.0.1. In MariaDB 5.3, columns can only be identified by numbers. See the #mariadb-53-vs-mariadb-100 section below)

It is possible to add and 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';

You can also list all columns, or (starting from MariaDB 10.0.1) get them together with their values in JSON format:

SELECT item_name, column_list(dynamic_cols) FROM assets;
+-----------------+---------------------------+
| item_name       | column_list(dynamic_cols) |
+-----------------+---------------------------+
| MariaDB T-shirt | `size`,`color`            |
| Thinkpad Laptop | `color`,`warranty`        |
+-----------------+---------------------------+

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

Dynamic Columns Reference

The rest of this page is a complete reference of dynamic columns in MariaDB

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

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

-- MariaDB 5.3+:
INSERT INTO tbl SET dyncol_blob=COLUMN_CREATE(1 /*column id*/, "value");
-- MariaDB 10.0.1+:
INSERT INTO tbl SET dyncol_blob=COLUMN_CREATE("column_name", "value");

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

• • 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 #datatypes section for a discussion about types.

-- 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;

COLUMN_GET

COLUMN_GET(dyncol_blob, column_nr as type);
COLUMN_GET(dyncol_blob, column_name as type);

COLUMN_DELETE

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

COLUMN_EXISTS

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

COLUMN_LIST

COLUMN_LIST(dyncol_blob);

SELECT column_list(column_create('col1','val1','col2','val2'));
+---------------------------------------------------------+
| column_list(column_create('col1','val1','col2','val2')) |
+---------------------------------------------------------+
| `col1`,`col2`                                           |
+---------------------------------------------------------+

COLUMN_CHECK(dyncol_blob);

COLUMN_JSON(dyncol_blob);

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

Nesting Dynamic Columns

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

SET @tmp= column_create('parent_column', column_create('child_column', 12345));
Query OK, 0 rows affected (0.00 sec)

SELECT column_json(@tmp);
+------------------------------------------+
| column_json(@tmp)                        |
+------------------------------------------+
| {"parent_column":{"child_column":12345}} |
+------------------------------------------+

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

If you are trying to get a nested dynamic column as a string use 'as BINARY' as the last argument of COLUMN_GET (otherwise problems with character set conversion and illegal symbols are possible):

select column_json( column_get(
  column_create('test1', column_create('key1','value1','key2','value2','key3','value3')),
  'test1' as BINARY));

Datatypes

In SQL, one needs to define the type of each column in a table. Dynamic columns do not provide any way to declare a type in advance ("whenever there is a column 'weight', it should be integer" is not possible). However, each particular dynamic column value is stored together with its datatype.

The set of possible datatypes is mostly the same as that used by the SQL CAST and CONVERT functions. However, note that there are currently some differences - see MDEV-597.

A Note About Lengths

If you're running queries like

SELECT COLUMN_GET(blob, 'colname' as CHAR) ... 

without specifying a maximum length (i.e. using #as CHAR#, not as CHAR(n)), MariaDB will report the maximum length of the resultset column to be 53,6870,911 (bytes or characters?) for MariaDB 5.3-10.0.0 and 16,777,216 for MariaDB 10.0.1+. This may cause excessive memory usage in some client libraries, because they try to pre-allocate a buffer of maximum resultset width. If you suspect you're hitting this problem, use CHAR(n) whenever you're using COLUMN_GET in the select list.

MariaDB 5.3 vs MariaDB 10.0

The dynamic columns feature was introduced into MariaDB in two steps:

1. MariaDB 5.3 was the first version to support dynamic columns. Only numbers could be used as column names in this version.
2. In MariaDB 10.0.1, column names can be either numbers or strings. Also, the COLUMN_JSON and COLUMN_CHECK functions were added.

See also Dynamic Columns in MariaDB 10.

Client-side API

It is also possible to create or parse dynamic columns blobs on the client side. libmysql client library now includes an API for writing/reading dynamic column blobs. See dynamic-columns-api for details.

Limitations