Динамические колонки

Динамические колонки позволяют хранить разные наборы колонок для каждой строки в таблице. Они работают, храня набор колонок в BLOB и имея небольшой набор функций для его обработки.

Динамические колонки следует использовать, когда нельзя использовать обычные колонки.

Типичный случай использования — когда необходимо хранить элементы, которые могут иметь много различных атрибутов (например, размер, цвет, вес и т. д.), а набор возможных атрибутов очень большой и/или неизвестен заранее. В этом случае атрибуты можно поместить в динамические колонки.

Основы динамических колонок

Таблица должна иметь колонку BLOB, которая будет использоваться для хранения динамических колонок:

create table assets (
  item_name varchar(32) primary key, -- A common attribute for all items
  dynamic_cols  blob  -- Dynamic columns will be stored here
);

После создания можно получить доступ к динамическим колонкам через функции динамических колонок:

Вставьте строку с двумя динамическими колонками: цвет=синий, размер=XL

INSERT INTO assets VALUES 
  ('MariaDB T-shirt', COLUMN_CREATE('color', 'blue', 'size', 'XL'));

Вставьте ещё одну строку с динамическими колонками: цвет=чёрный, цена=500

INSERT INTO assets VALUES
  ('Thinkpad Laptop', COLUMN_CREATE('color', 'black', 'price', 500));

Выберите динамическую колонку 'цвет' для всех элементов:

SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) 
  AS color FROM assets;
+-----------------+-------+
| item_name       | color |
+-----------------+-------+
| MariaDB T-shirt | blue  |
| Thinkpad Laptop | black |
+-----------------+-------+

Возможно добавлять и удалять динамические колонки из строки:

-- Remove a column:
UPDATE assets SET dynamic_cols=COLUMN_DELETE(dynamic_cols, "price") 
WHERE COLUMN_GET(dynamic_cols, 'color' as char)='black'; 

-- Add a column:
UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '3 years')
WHERE item_name='Thinkpad Laptop';

Вы также можете перечислить все колонки или получить их вместе со значениями в формате JSON:

SELECT item_name, column_list(dynamic_cols) FROM assets;
+-----------------+---------------------------+
| item_name       | column_list(dynamic_cols) |
+-----------------+---------------------------+
| MariaDB T-shirt | `size`,`color`            |
| Thinkpad Laptop | `color`,`warranty`        |
+-----------------+---------------------------+

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

Справочник по динамическим колонкам

Остальная часть этой страницы — это полный справочник по динамическим колонкам в MariaDB

Функции динамических колонок

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

• • хранения в таблице
  • дальнейшего изменения с помощью других функций динамических колонок

-- 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 должно быть либо допустимым BLOB динамических колонок (например, COLUMN_CREATE возвращает такой BLOB), либо пустой строкой.
  • column_name указывает имя добавляемой колонки. Если у dyncol_blob уже есть колонка с этим именем, она будет перезаписана.
  • value указывает новое значение для колонки. Передача значения NULL приведёт к удалению колонки.
  • as type необязательно. См. раздел #типы_данных для обсуждения типов.

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

SELECT column_list(column_create('col1','val1','col2','val2'));
+---------------------------------------------------------+
| column_list(column_create('col1','val1','col2','val2')) |
+---------------------------------------------------------+
| `col1`,`col2`                                           |
+---------------------------------------------------------+

COLUMN_CHECK

COLUMN_CHECK(dyncol_blob);

COLUMN_JSON

COLUMN_JSON(dyncol_blob);

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

Вложенные динамические колонки

Можно использовать вложенные динамические колонки, поместив один BLOB динамических колонок внутрь другого. Функция COLUMN_JSON отобразит вложенные колонки.

SET @tmp= column_create('parent_column', 
  column_create('child_column', 12345));
Query OK, 0 rows affected (0.00 sec)

SELECT column_json(@tmp);
+------------------------------------------+
| column_json(@tmp)                        |
+------------------------------------------+
| {"parent_column":{"child_column":12345}} |
+------------------------------------------+

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

Если вы пытаетесь получить вложенную динамическую колонку как строку, используйте 'as BINARY' в качестве последнего аргумента COLUMN_GET (иначе возможны проблемы с преобразованием набора символов и недопустимыми символами):

select column_json( column_get(
  column_create('test1', 
    column_create('key1','value1','key2','value2','key3','value3')),
  'test1' as BINARY));

Типы данных

В SQL необходимо определить тип каждой колонки в таблице. Динамические колонки не предоставляют никакого способа заранее объявить тип («всякий раз, когда есть колонка 'вес', она должна быть целым числом» не возможно). Однако каждое конкретное значение динамической колонки хранится вместе со своим типом данных.

Набор возможных типов данных в основном такой же, как и используемый функциями SQL CAST и CONVERT. Однако обратите внимание, что есть некоторые различия — см. MDEV-597.

Примечание о длинах

Если вы выполняете запросы, такие как

SELECT COLUMN_GET(blob, 'colname' as CHAR) ... 

без указания максимальной длины (т. е. используя #as CHAR#, а не as CHAR(n)), MariaDB сообщит о максимальной длине колонки результата как 53,6870,911 (байты или символы?) для MariaDB 5.3-10.0.0 и 16,777,216 для MariaDB 10.0.1+. Это может привести к чрезмерному использованию памяти в некоторых библиотеках клиентов, так как они пытаются предварительно выделить буфер максимальной ширины набора результатов. Если вы подозреваете, что сталкиваетесь с этой проблемой, используйте CHAR(n) всякий раз, когда используете COLUMN_GET в списке select.

MariaDB 5.3 против MariaDB 10.0

Функция динамических колонок была добавлена в MariaDB в два этапа:

1. MariaDB 5.3 был первой версией, поддерживающей динамические столбцы. В этой версии в качестве имён столбцов можно было использовать только числа.
2. В MariaDB 10.0.1 имена столбцов могут быть как числами, так и строками. Также были добавлены функции COLUMN_JSON и COLUMN_CHECK.

См. также Динамические столбцы в MariaDB 10.

API клиентской стороны

Также возможно создание или парсинг блобов динамических столбцов на стороне клиента. libmysql библиотека клиента теперь включает API для записи/чтения блобов динамических столбцов. Подробнее см. dynamic-columns-api.

Ограничения

См. также

• Динамические столбцы с MariaDB 10