tmp
You are viewing an old version of this article. View
the current version here.
Contents
Dynamic columns feature allows to store different sets of columns for each row in the table.
It is targeted at a case when one needs to store entities that have different attributes (like size, color, weight, etc). When the number of possible attributes is very large (or even not known in advance), it becomes impossible to create regular SQL columns. In that case, dynamic columns can be used.
It is done as follows. First, let's create a basic table with dynamic column storage:
create table assets ( item_name varchar(40), -- Name is a common attribute for all items dynamic_cols blob, -- A blob column that will be used as a storage -- for dynamic columns. primary key(item_name) -- not required, but it's a good practice to -- have a primary key );
Then, one can insert data like so:
insert into assets values ('MariaDB T-shirt', COLUMN_CREATE('color', 'blue', 'size', 'XL')), ('Thinkpad Laptop', COLUMN_CREATE('color', 'black', 'price', '500'));
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 | +-----------------+-------+
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.