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.