Description\n-----------\n\nDynamic columns allow one to store different sets of columns for each row in a table. It works by storing a set of columns in a blob and having a small set of functions to manipulate it.\n\nDynamic columns should be used when it is not possible to use regular columns.\n\nA 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 possible attributes is very large and/or unknown in advance. In that case, attributes can be put into dynamic columns.\n\nDynamic Columns Basics\n\nThe table should have a blob column which will be used as storage for dynamic columns:\n\n``sql\nmain\nCREATE TABLE assets (\n  item_name VARCHAR(32) PRIMARY KEY, -- A common attribute for all items\n  dynamic_cols  BLOB  -- Dynamic columns will be stored here\n);\n`\n\nOnce created, you can access dynamic columns via dynamic column functions.\n\nInsert a row with two dynamic columns: color=blue, size=XL:\n\n`sql\nINSERT INTO assets VALUES \n  ('MariaDB T-shirt', COLUMN_CREATE('color', 'blue', 'size', 'XL'));\n`\n\nInsert another row with dynamic columns: color=black, price=500:\n\n`sql\nINSERT INTO assets VALUES\n  ('Thinkpad Laptop', COLUMN_CREATE('color', 'black', 'price', 500));\n`\n\nSelect the dynamic column 'color' for all items:\n\n`sql\nSELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) \n  AS color FROM assets;\n+-----------------+-------+\n| item_name       | color |\n+-----------------+-------+\n| MariaDB T-shirt | blue  |\n| Thinkpad Laptop | black |\n+-----------------+-------+\n`\n\nIt is possible to add and remove dynamic columns from a row:\n\n`sql\n-- Remove a column:\nUPDATE assets SET dynamic_cols=COLUMN_DELETE(dynamic_cols, "price") \nWHERE COLUMN_GET(dynamic_cols, 'color' AS CHAR)='black'; \n\n-- Add a column:\nUPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '3 years')\nWHERE item_name='Thinkpad Laptop';\n`\n\nYou can also list all columns, or get them together with their values in JSON format:\n\n`sql\nSELECT item_name, column_list(dynamic_cols) FROM assets;\n+-----------------+---------------------------+\n| item_name       | column_list(dynamic_cols) |\n+-----------------+---------------------------+\n| MariaDB T-shirt | size,color            |\n| Thinkpad Laptop | color,warranty        |\n+-----------------+---------------------------+\n\nSELECT item_name, COLUMN_JSON(dynamic_cols) FROM assets;\n+-----------------+----------------------------------------+\n| item_name       | COLUMN_JSON(dynamic_cols)              |\n+-----------------+----------------------------------------+\n| MariaDB T-shirt | {"size":"XL","color":"blue"}           |\n| Thinkpad Laptop | {"color":"black","warranty":"3 years"} |\n+-----------------+----------------------------------------+\n`\n\nDynamic Columns Reference\n\nThis is a complete reference of dynamic columns in MariaDB.\n\nColumn can be referred to by name (column_name in the following code blocks). This is the preferred method.\\\nIf you need support for old (< 10.0) MariaDB versions, you have to refer to columns by number. In that case, replace _column_name_ with _column_nr_. This method is not recommended.\n\nDynamic Columns Functions\n\nCOLUMN_CREATE\n\n`sql\nCOLUMN_CREATE(column_name, value [as type], [column_name, value \n  [as type]]...);\n`\n\nReturns a dynamic columns blob that stores the specified columns with values. The return value is suitable for\n\n storing in a table,\n further modification with other dynamic columns functions.\n\nThe as type part allows one to specify the value type. In most cases, this is redundant because MariaDB will be able to deduce the type of the value. Explicit type specification may be needed when the type of the value is not apparent. For example, a literal '2012-12-01' has a CHAR type by default, one will need to specify '2012-12-01' AS DATE to have it stored as a date. See the Datatypes section for further details. Note also MDEV-597.\n\nTypical usage:\n\n`sql\nINSERT INTO tbl SET dyncol_blob=COLUMN_CREATE("column_name", "value");\n`\n\nCOLUMN_ADD\n\n`sql\nCOLUMN_ADD(dyncol_blob, column_name, value [as type], \n  [column_name, value [as type]]...);\n`\n\nAdds or updates dynamic columns.\n\n dyncol_blob must be either a valid dynamic columns blob (for example, COLUMN_CREATE returns such blob), or an empty string.\n 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.\n value specifies the new value for the column. Passing a NULL value will cause the column to be deleted.\n as type is optional. See the datatypes section for a discussion about types.\n\nThe return value is a dynamic column blob after the modifications.\n\nTypical usage:\n\n`sql\nUPDATE t1 SET dyncol_blob=COLUMN_ADD(dyncol_blob, "column_name", "value") \n  WHERE id=1;\n`\n\nNote: COLUMN_ADD() is a regular function (just like CONCAT()), hence, in order to update the value in the table you have to use the UPDATE ... SET dynamic_col=COLUMN_ADD(dynamic_col, ....) pattern.\n\nCOLUMN_GET\n\n`sql\nCOLUMN_GET(dyncol_blob, column_name as type);\n`\n\nRetrieves the value of a dynamic column by its name. If no column with the given name exists, NULL is returned.\n\ncolumn_name as type requires that one specify the datatype of the dynamic column they are reading.\n\nThis may seem counter-intuitive: Why would you need to specify which datatype they're retrieving? Can't the dynamic columns system figure the datatype from the data being stored?\n\nThe answer is: SQL is a statically typed language. The SQL interpreter needs to know the datatypes of all expressions before the query is run (for example, when one is using prepared statements and runs"select COLUMN_GET(...)", the prepared statement API requires the server to inform the client about the datatype of the column being read before the query is executed and the server can see what datatype the column actually has).\n\nSee the Datatypes section for more information about datatypes.\n\nCOLUMN_DELETE\n\n`sql\nCOLUMN_DELETE(dyncol_blob, column_name, column_name...);\n`\n\nDeletes a dynamic column with the specified name. Multiple names can be given.\n\nThe return value is a dynamic column blob after the modification.\n\nCOLUMN_EXISTS\n\n`sql\nCOLUMN_EXISTS(dyncol_blob, column_name);\n`\n\nChecks if a column with name column_name exists in dyncol_blob. If yes, return 1, otherwise return 0.\n\nCOLUMN_LIST\n\n`sql\nCOLUMN_LIST(dyncol_blob);\n`\n\nReturns a comma-separated list of column names. The names are quoted with backticks.\n\n`sql\nSELECT column_list(column_create('col1','val1','col2','val2'));\n+---------------------------------------------------------+\n| column_list(column_create('col1','val1','col2','val2')) |\n+---------------------------------------------------------+\n| col1,col2                                           |\n+---------------------------------------------------------+\n`\n\nCOLUMN_CHECK\n\n`sql\nCOLUMN_CHECK(dyncol_blob);\n`\n\nChecks if dyncol_blob is a valid packed dynamic columns blob. A return value of 1 means the blob is valid, a return value of 0 means it is not.\n\nRationale:\\\nNormally, you work with valid dynamic column blobs. Functions likeCOLUMN_CREATE, COLUMN_ADD, COLUMN_DELETE always return valid dynamic column blobs. However, if a dynamic column blob is accidentally truncated, or transcoded from one character set to another, it is corrupted. This function can be used to check if a value in a blob field is a valid dynamic column blob.\n\nIt is possible that a truncation cuts a Dynamic Column "clearly" so that COLUMN_CHECK does not notice the corruption, but, in any case of truncation, a warning is issued during value storing.\n\nCOLUMN_JSON\n\n`sql\nCOLUMN_JSON(dyncol_blob);\n`\n\nReturns a JSON representation of data in dyncol_blob :\n\n`sql\nSELECT item_name, COLUMN_JSON(dynamic_cols) FROM assets;\n+-----------------+----------------------------------------+\n| item_name       | COLUMN_JSON(dynamic_cols)              |\n+-----------------+----------------------------------------+\n| MariaDB T-shirt | {"size":"XL","color":"blue"}           |\n| Thinkpad Laptop | {"color":"black","warranty":"3 years"} |\n+-----------------+----------------------------------------+\n`\n\nLimitation: COLUMN_JSON decodes nested dynamic columns at a nesting level of not more than 10 levels deep. Dynamic columns that are nested deeper than 10 levels are shown as a BINARY string, without encoding.\n\nNesting Dynamic Columns\n\nIt is possible to use nested dynamic columns by putting one dynamic column blob inside another. The COLUMN_JSON function will display nested columns.\n\n`sql\nSET @tmp= column_create('parent_column', \n  column_create('child_column', 12345));\nQuery OK, 0 rows affected (0.00 sec)\n\nSELECT column_json(@tmp);\n+------------------------------------------+\n| column_json(@tmp)                        |\n+------------------------------------------+\n| {"parent_column":{"child_column":12345}} |\n+------------------------------------------+\n\nSELECT column_get(column_get(@tmp, 'parent_column' AS char), \n  'child_column' AS int);\n+------------------------------------------------------------------------------+\n| column_get(column_get(@tmp, 'parent_column' as char), 'child_column' as int) |\n+------------------------------------------------------------------------------+\n|                                                                        12345 |\n+------------------------------------------------------------------------------+\n`\n\nIf you are trying to get a nested dynamic column as a string, use AS BINARY as the last argument of COLUMN_GET . Otherwise, problems with character set conversion and illegal symbols are possible:\n\n`sql\nSELECT column_json( column_get(\n  column_create('test1', \n    column_create('key1','value1','key2','value2','key3','value3')),\n  'test1' AS BINARY));\n`\n\nDatatypes\n\nIn 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.\n\nThe set of possible datatypes is mostly the same as that used by the CAST and CONVERT functions. However, note that there are currently some differences - see MDEV-597.\n\n| Type                | Dynamic column internal type | Description                                                                                                                 |\n| ------------------- | ---------------------------- | --------------------------------------------------------------------------------------------------------------------------- |\n| BINARY\\[(N)]        | DYN_COL_STRING             | (variable length string with binary charset)                                                                                |\n| CHAR\\[(N)]          | DYN_COL_STRING             | (variable length string with charset)                                                                                       |\n| DATE                | DYN_COL_DATE               | (date - 3 bytes)                                                                                                            |\n| DATETIME\\[(D)]      | DYN_COL_DATETIME           | (date and time (with microseconds) - 9 bytes)         |\n| DECIMAL\\[(M\\[,D])]  | DYN_COL_DECIMAL            | (variable length binary decimal representation with MariaDB limitation)                                                     |\n| DOUBLE\\[(M,D)]      | DYN_COL_DOUBLE             | (64 bit double-precision floating point)                                                                                    |\n| INTEGER             | DYN_COL_INT                | (variable length, up to 64-bit signed integer)                                                                              |\n| SIGNED \\[INTEGER]   | DYN_COL_INT                | (variable length, up to 64-bit signed integer)                                                                              |\n| TIME\\[(D)]          | DYN_COL_TIME               | (time (with microseconds, may be negative) - 6 bytes) |\n| UNSIGNED \\[INTEGER] | DYN_COL_UINT               | (variable length, up to 64bit unsigned integer)                                                                             |\n\nA Note About Lengths\n\nIf you're running queries without specifying a maximum length (i.e. using #as CHAR#, not as CHAR(n)), MariaDB reports the maximum length of the result set column to be53,6870,911 (bytes or characters?). This may cause excessive memory usage in some client libraries, because they try to pre-allocate a buffer of maximum result set width. If you suspect you're hitting this problem, use CHAR(n)\\\nwhenever you're using COLUMN_GET in the select list.\n\n`sql\nSELECT COLUMN_GET(blob, 'colname' as CHAR) ...\n`\n\nClient-side API\n\nIt 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.\n\nLimitations\n\n| Description                                   | Limit                                                                                                                                           |\n| --------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------- |\n| Max number of columns                         | 65535                                                                                                                                           |\n| Maximum total length of packed dynamic column | max_allowed_packet (1G) |\n\nURL: https://mariadb.com/docs/server/reference/sql-structure/nosql/dynamic-columns