Columnas Dinamicas

Las columnas dinámicas permiten poder guardar un conjunto de columnas para cada fila de una tabla. Funciona guardando un conjunto de columnas en un tipo de dato blob y contando con una pequeña serie de funciones para manipularlas.

Las columnas dinámicas deben ser usadas cuando no sea posible utilizar columnas estándar o regulares.

Un caso típico para esto es cuando uno necesita guardar datos que tengan diferentes atributos (colores, tamaños, peso, etc), y las diferentes posibilidades son muy grandes o desconocidas en cierto modo. En ese caso estos datos se pueden guardar en columnas dinámicas.

Columnas Dinamicas

La tabla debe contener una columna del tipo blob que se utilizara para guardar las columnas dinamicas:

create table assets (
  item_name     varchar(32) primary key, -- Un atributo comun a todos los items
  dynamic_cols  blob  -- Aca se guardaran las columnas dinamicas
);

Una vez creada la columna, se puede acceder a la misma con una sere de funciones de columnas dinámicas.

Insertar un registro con dos columnas dinámicas: color=blue, size=XL

insert into assets values ('MariaDB T-shirt', COLUMN_CREATE('color', 'blue', 'size', 'XL'));

Insertar otro registro con columnas dinámicas: color=blue, price=500

insert into assets values ('Thinkpad Laptop', COLUMN_CREATE('color', 'black', 'price', 500));

Seleccionado sobre la columna dinamica 'color' para todos los items:

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)

(nota: el ejemplo de arriba utiliza MariaDB 10.0.1. En MariaDB 5.3, las columnas solo pueden ser identificadas por numeros. Ver #mariadb-53-vs-mariadb-100 en la seccion inferior)

Es posible agregar y remover columnas dinámicas de una fila:

-- Remover una columna:
update assets set dynamic_cols=COLUMN_DELETE(dynamic_cols, "price") 
where COLUMN_GET(dynamic_cols, 'color' as char)='black'; 

-- Agregar una columna:
update assets set dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '3 years')
where item_name='Thinkpad Laptop';

También se pueden listar todas las columnas o (desde MariaDB 10.0.1) traerlas juntas con sus valores en formato JSON:

MariaDB [test]> select item_name, column_list(dynamic_cols) from assets;
+-----------------+---------------------------+
| item_name       | column_list(dynamic_cols) |
+-----------------+---------------------------+
| MariaDB T-shirt | `size`,`color`            |
| Thinkpad Laptop | `color`,`warranty`        |
+-----------------+---------------------------+
2 rows in set (0.00 sec)

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)

Referencia para columnas dinámicas

El resto de esta pagina es una referencia completa sobre columnas dinamicas en MariaDB

Funciones para columnas dinámicas:

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

• nada
  • guardar en una tabla
  • futuras modificaciones con otras funciones para columnas dinamicas.

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

MariaDB [test]> 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);

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

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.

MariaDB [test]> set @tmp= column_create('parent_column', column_create('child_column', 12345));
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select column_json(@tmp);
+------------------------------------------+
| column_json(@tmp)                        |
+------------------------------------------+
| {"parent_column":{"child_column":12345}} |
+------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> 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 |
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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 the same as that used by the SQL CAST and CONVERT functions:

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