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, only numbers could be used. See [#mariadb-5.3] for details
And query it like so:
select item_name, COLUMN_GET(dynamic_cols, 'color' as char) as color from assets; +-----------------+-------+ | item_name | color | +-----------------+-------+ | MariaDB T-shirt | blue | | Thinkpad Laptop | black | +-----------------+-------+
SQL 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 byCOLUMN_CREATE()
or an empty string. - If '
dynamic_col
' isNULL
then the function returnsNULL
. - 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 not required (it is detected automatically) and is there mostly for testing purposes.
Typical usage:
UPDATE t1 set dynamic_col=COLUMN_ADD(dynamic_col, 5, "test") WHERE id=1; UPDATE t1 set dynamic_col=COLUMN_ADD(dynamic_col, "stage", "test") WHERE id=1;
The above adds or replaces dynamic_column
'5
' or "stage
", with
value "test"
, stored in the 'dynamic_col
' column, for the row with
id=1
The reason you have dynamic_col
twice is the COLUMN_ADD()
is a normal
function, like CONCAT()
and you want to replace the old value in
the 'dynamic_col
' value with the new value from the COLUMN_ADD()
function.
COLUMN_DELETE
COLUMN_DELETE(dynamic_column, column_nr, column_nr...); COLUMN_DELETE(dynamic_column, column_name, column_name...);
Deletes a dynamic column.
COLUMN_EXISTS
COLUMN_EXISTS(dynamic_column, column_nr); COLUMN_EXISTS(dynamic_column, column_name);
This returns 1
if the column exists, 0
if not, and NULL
if
dynamic_column
is NULL
.
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.
COLUMN_GET
COLUMN_GET(dynamic_column, column_nr as type); COLUMN_GET(dynamic_column, column_name as type);
This function querys a dynamic column.