MariaDB Dynamic Columns : COLUMN_CREATE, COLUMN_GET, COLUMN_JSON and the MariaDB-only key-value blob

Deterministic guidance for working with MariaDB dynamic columns. Use this skill to read or write a dynamic-column blob, choose between dynamic columns and JSON, index a dynamic-column key, and migrate a dynamic-column blob to a JSON document.

Quick Reference

• Dynamic columns are MariaDB-only. The COLUMN_* function family does NOT exist in MySQL at any version. NEVER use dynamic columns if cross-DBMS portability is a requirement. ALWAYS prefer JSON for new schemas unless a measured size or performance advantage of dynamic columns is documented for the workload. Verified : KB dynamic-columns/.
• The eight functions are : COLUMN_CREATE, COLUMN_ADD, COLUMN_GET, COLUMN_DELETE, COLUMN_EXISTS, COLUMN_LIST, COLUMN_CHECK, COLUMN_JSON. The blob is stored in a single BLOB, MEDIUMBLOB, or LONGBLOB column. There is no dedicated DYNAMIC_COLUMNS type. Verified : KB dynamic-columns-functions/.
• COLUMN_GET REQUIRES a type tag and ALWAYS returns NULL on a type mismatch. Syntax : COLUMN_GET(blob, 'name' AS CHAR(64)). If the key was stored as INTEGER and you ask for it AS CHAR, you may still receive a value because the engine coerces ; if the key does not exist, the result is NULL with NO warning. ALWAYS guard with COLUMN_EXISTS before relying on a NULL result to mean "absent". Verified : KB column_get/.
• Supported type tags : BINARY[(N)], CHAR[(N)], DATE, TIME, DATETIME, DECIMAL[(M,D)], DOUBLE, INTEGER, SIGNED [INTEGER], UNSIGNED [INTEGER]. There is NO JSON type tag ; nested dynamic columns are flagged by passing a dynamic-column blob as the value of another key. Verified : KB dynamic-columns/.
• NEVER index a dynamic-column blob directly. The blob is opaque to the optimizer. ALWAYS materialise the wanted key into a VIRTUAL or PERSISTENT generated column with COLUMN_GET, then index that column. The pattern is identical to indexing a JSON path through JSON_VALUE. Verified : KB generated-columns/.
• Named columns are the preferred mode since MariaDB 10.0. Pre-10.0 (now end-of-life) used numbered columns ; numbered mode still parses on modern servers but ALWAYS use named mode in new code. The two modes cannot be mixed inside a single blob. Verified : KB dynamic-columns/.
• Nesting is limited to 10 levels deep. A dynamic-column value may itself be a dynamic-column blob, recursively, up to 10 nested layers. Deeper structures must be flattened or stored in JSON. Verified : KB dynamic-columns/.
• Max columns per blob = 65 535. Max blob size = max_allowed_packet (default 1 GB on modern releases ; 16 MB on legacy 10.6 stock). For large attribute sets, increase max_allowed_packet ; for very wide records, redesign into a related table. Verified : KB dynamic-columns/.
• COLUMN_JSON converts the blob to a JSON document in standard JSON syntax. ALWAYS use COLUMN_JSON to export, debug, or migrate a dynamic-column blob. The reverse direction (JSON to dynamic columns) requires COLUMN_CREATE calls in application code ; there is NO JSON_TO_DYNAMIC function.
• COLUMN_CHECK validates blob integrity. Returns 1 for a well-formed blob, 0 otherwise. Use COLUMN_CHECK in a CHECK constraint to refuse corrupt writes : CHECK (COLUMN_CHECK(attrs)).
• Default to JSON for new schemas. Dynamic columns are mature but a niche choice : opaque to non-MariaDB tools, harder to debug, and offer no JSONPath equivalent. ALWAYS justify a dynamic-column choice with a concrete reason (measured smaller payload, deeper nesting needs, legacy compatibility).

Decision Trees