tmp
Contents
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 attributes varies between the items.
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)