Learn about dynamic column functions in MariaDB Server. This section details SQL functions for manipulating and querying dynamic columns, enabling flexible schema design and data storage.
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]]...)Adds or updates dynamic columns.
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.
The return value is a dynamic column blob after the modifications.
UPDATE t1 SET dyncol_blob=COLUMN_ADD(dyncol_blob, "column_name", "value") WHERE id=1;Note: 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.
This page is licensed: CC BY-SA / Gnu FDL
COLUMN_CHECK(dyncol_blob);Check if dyncol_blob is a valid packed dynamic columns blob. Return value of 1 means the blob is valid, return value of 0 means it is not.
Rationale: Normally, one works with valid dynamic column blobs. Functions like COLUMN_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 will be corrupted. This function can be used to check if a value in a blob field is a valid dynamic column blob.
This page is licensed: CC BY-SA / Gnu FDL
COLUMN_CREATE(column_nr, value [as type], [column_nr, value [as type]]...)
COLUMN_CREATE(column_name, value [as type], [column_name, value [as type]]...)Returns a dynamic columns blob that stores the specified columns with values.
The return value is suitable for
storing in a table;
further modification with other dynamic columns functions.
The 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 Dynamic Columns:Datatypes for further details.
INSERT INTO tbl SET dyncol_blob=COLUMN_CREATE("column_name", "value");This page is licensed: CC BY-SA / Gnu FDL
COLUMN_DELETE(dyncol_blob, column_nr, column_nr...)
COLUMN_DELETE(dyncol_blob, column_name, column_name...)Deletes a dynamic column with the specified name. Multiple names can be given. The return value is a dynamic column blob after the modification.
This page is licensed: CC BY-SA / Gnu FDL
COLUMN_EXISTS(dyncol_blob, column_nr)
COLUMN_EXISTS(dyncol_blob, column_name)Checks if a column with name column_name exists in dyncol_blob. If yes, return 1, otherwise return 0. See dynamic columns for more information.
This page is licensed: CC BY-SA / Gnu FDL
COLUMN_GET(dyncol_blob, column_nr as type)
COLUMN_GET(dyncol_blob, column_name as type)Gets the value of a dynamic column by its name. If no column with the given name exists, NULL will be returned.
column_name as type requires that one specify the datatype of the dynamic column they are reading.
This may seem counter-intuitive: why would one need to specify which datatype they're retrieving? Can't the dynamic columns system figure the datatype from the data being stored?
The 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).
Suppose running a query like this:
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 result set column to be 16,777,216. This may cause excessive memory usage in some client libraries, because they try to pre-allocate a buffer of maximum result set width. To avoid this problem, use CHAR(n) whenever you're using COLUMN_GET in the select list.
See Dynamic Columns:Datatypes for more information about datatypes.
This page is licensed: CC BY-SA / Gnu FDL
COLUMN_JSON(dyncol_blob)Returns a JSON representation of data in dyncol_blob. Can also be used to display nested columns. See dynamic columns for more information.
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"} |
+-----------------+----------------------------------------+Limitation: 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 BINARY string, without encoding.
This page is licensed: CC BY-SA / Gnu FDL
COLUMN_LIST(dyncol_blob);Returns a comma-separated list of column names. The names are quoted with backticks.
See dynamic columns for more information.
This page is licensed: CC BY-SA / Gnu FDL