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
Tree 1 : "Dynamic columns or JSON?"
Is the data ever read by non-MariaDB clients
(MySQL replica, BI tool, ETL, MongoDB pipeline) ?
Yes -> JSON. Dynamic columns are an opaque blob to every other tool.
Is portability across DBMS engines a requirement ?
Yes -> JSON. Dynamic columns do not exist outside MariaDB.
Does the schema need standards-compliant JSONPath
(JSON_VALUE, JSON_QUERY, JSON_TABLE) ?
Yes -> JSON. Dynamic columns have no JSONPath equivalent.
Is the data internal to one MariaDB instance, sparse,
and you have measured that JSON LONGTEXT is too large
or too slow for the workload ?
Yes -> dynamic columns are a defensible choice. Document the measurement.
Default : JSON with CHECK (JSON_VALID(col)) and functional indexes
on virtual columns.
Tree 2 : "Reading a key from a dynamic-column blob"
Do you know the key always exists ?
No -> first call COLUMN_EXISTS(blob, 'key'). Branch on 1 / 0.
Yes -> proceed directly to COLUMN_GET.
Do you know the stored type ?
Yes -> match the COLUMN_GET tag exactly :
stored CHAR -> AS CHAR(N)
stored INTEGER -> AS INTEGER (or AS SIGNED / UNSIGNED)
stored DECIMAL -> AS DECIMAL(M,D)
stored DATE / TIME / DATETIME -> the matching tag.
No -> default to AS CHAR(255) and accept that a stored INTEGER
is returned as the string "42". Numeric comparisons on the
result string compare lexicographically, NOT numerically.
The returned value is NULL ?
Possible causes :
1. The key does not exist (re-check with COLUMN_EXISTS).
2. The blob itself is NULL.
3. The type tag is incompatible with the stored type.
4. The blob is corrupt (run COLUMN_CHECK).
Tree 3 : "Index a dynamic-column key"
Is the key queried by equality or range in WHERE / JOIN / ORDER BY ?
Yes :
Step 1 : ALTER TABLE t
ADD COLUMN k_status VARCHAR(32)
AS (COLUMN_GET(attrs, 'status' AS CHAR(32))) VIRTUAL;
Step 2 : CREATE INDEX ix_status ON t (k_status);
Why VIRTUAL : computed on the fly, no extra disk space, still indexable.
Why PERSISTENT : if the COLUMN_GET expression is expensive and the column
is read often without using the index path.
No, the keys are dynamic and never queried directly ?
-> Do not index. Keep the blob compact.
Tree 4 : "Migrate a dynamic-column blob to JSON"
Goal : replace attrs BLOB (dynamic columns) with attrs_json JSON.
Step 1 : ALTER TABLE t ADD COLUMN attrs_json JSON NULL
CHECK (JSON_VALID(attrs_json)); -- D-010 reminder
Step 2 : UPDATE t SET attrs_json = COLUMN_JSON(attrs);
-- One-shot conversion. Run on a quiet window or chunked
-- (WHERE id BETWEEN ? AND ?) on a large table.
Step 3 : Verify : SELECT id FROM t
WHERE attrs IS NOT NULL AND attrs_json IS NULL;
-- Expect zero rows. Rows that fail are likely corrupt blobs
-- (run COLUMN_CHECK to confirm) and need manual repair.
Step 4 : Rewrite application reads from COLUMN_GET to JSON_VALUE.
Step 5 : Drop the legacy column :
ALTER TABLE t DROP COLUMN attrs;
ALTER TABLE t CHANGE COLUMN attrs_json attrs JSON NULL
CHECK (JSON_VALID(attrs));
Patterns
Pattern 1 : Create a row with a dynamic-column blob
-- 10.6+ : write a sparse attribute set into a single column.
CREATE TABLE product (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
sku VARCHAR(32) NOT NULL UNIQUE,
attrs BLOB NULL
) ENGINE=InnoDB;
INSERT INTO product (sku, attrs) VALUES (
'CHAIR-RED-L',
COLUMN_CREATE(
'color', 'red' AS CHAR(16),
'size', 'L' AS CHAR(4),
'weight', 12.5 AS DECIMAL(6,2),
'stock', 120 AS UNSIGNED INTEGER
)
);
Pattern 2 : Read a typed key with explicit existence check
-- 10.6+ : guard with COLUMN_EXISTS, then COLUMN_GET with the right type tag.
SELECT
id,
sku,
CASE
WHEN COLUMN_EXISTS(attrs, 'stock') = 1
THEN COLUMN_GET(attrs, 'stock' AS UNSIGNED INTEGER)
ELSE NULL
END AS stock_level,
COLUMN_GET(attrs, 'color' AS CHAR(16)) AS color
FROM product
WHERE sku = 'CHAIR-RED-L';
Pattern 3 : Add or update a key with COLUMN_ADD
-- 10.6+ : COLUMN_ADD inserts new keys and overwrites existing keys.
UPDATE product
SET attrs = COLUMN_ADD(
attrs,
'stock', 90 AS UNSIGNED INTEGER,
'reorder', '2026-06-01' AS DATE,
'finish', 'matte' AS CHAR(16)
)
WHERE sku = 'CHAIR-RED-L';
-- 'stock' is overwritten ; 'reorder' and 'finish' are added.
-- Passing NULL as the value for a key DELETES the key.
Pattern 4 : Delete one or more keys with COLUMN_DELETE
-- 10.6+ : drop a key from the blob.
UPDATE product
SET attrs = COLUMN_DELETE(attrs, 'finish', 'weight')
WHERE sku = 'CHAIR-RED-L';
-- Multiple keys can be deleted in one call.
-- Deleting a non-existent key is a no-op (no warning).
Pattern 5 : List all keys in a blob
-- 10.6+ : COLUMN_LIST returns a comma-separated list of backtick-quoted names.
SELECT sku, COLUMN_LIST(attrs) AS keys
FROM product
WHERE attrs IS NOT NULL;
-- Example output : `color`,`size`,`stock`,`reorder`
-- NEVER parse this string in SQL. Use it for debug / display only ;
-- iterate keys in application code by name.
Pattern 6 : Export to JSON with COLUMN_JSON
-- 10.6+ : single-shot conversion to a JSON document.
SELECT id, sku, COLUMN_JSON(attrs) AS attrs_json
FROM product
WHERE sku = 'CHAIR-RED-L';
-- attrs_json example :
-- {"color":"red","size":"L","stock":90,"reorder":"2026-06-01","color":"red"}
-- ALWAYS use COLUMN_JSON for export, audit logs, and migration to JSON column.
Pattern 7 : Index a dynamic-column key via a virtual column
-- 10.6+ : the blob is opaque ; index a virtual column instead.
ALTER TABLE product
ADD COLUMN k_color VARCHAR(16)
AS (COLUMN_GET(attrs, 'color' AS CHAR(16))) VIRTUAL,
ADD INDEX ix_product_color (k_color);
-- The optimiser uses ix_product_color for :
SELECT id, sku FROM product WHERE k_color = 'red';
-- WHERE COLUMN_GET(attrs, 'color' AS CHAR(16)) = 'red' -- also works,
-- but ONLY because MariaDB matches the expression to k_color's definition.
-- Always prefer querying the virtual column directly for clarity.
Pattern 8 : Validate blob integrity with CHECK + COLUMN_CHECK
-- 10.6+ : refuse corrupt writes at schema level.
CREATE TABLE product_v2 (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
sku VARCHAR(32) NOT NULL UNIQUE,
attrs BLOB NULL,
CONSTRAINT chk_attrs_blob CHECK (attrs IS NULL OR COLUMN_CHECK(attrs) = 1)
) ENGINE=InnoDB;
-- An INSERT or UPDATE that produces a malformed blob fails with
-- "CONSTRAINT 'chk_attrs_blob' failed".
Pattern 9 : Migrate to JSON in a single transaction
-- 10.6+ : add a JSON column, populate from COLUMN_JSON, verify, drop legacy.
START TRANSACTION;
ALTER TABLE product
ADD COLUMN attrs_json JSON NULL
CHECK (attrs_json IS NULL OR JSON_VALID(attrs_json));
UPDATE product
SET attrs_json = COLUMN_JSON(attrs)
WHERE attrs IS NOT NULL;
-- Verify no rows failed conversion.
SELECT COUNT(*) AS failed
FROM product
WHERE attrs IS NOT NULL AND attrs_json IS NULL;
-- If failed > 0, ROLLBACK and inspect with COLUMN_CHECK(attrs).
COMMIT;
Pattern 10 : Nested dynamic columns (up to 10 levels)
-- 10.6+ : a dynamic-column value can itself be a dynamic-column blob.
INSERT INTO product (sku, attrs) VALUES (
'BIKE-MTB-29',
COLUMN_CREATE(
'frame', 'aluminium' AS CHAR(16),
'wheels', COLUMN_CREATE(
'diameter', 29 AS UNSIGNED INTEGER,
'tubeless', 'Y' AS CHAR(1)
)
)
);
-- To read a nested key, COLUMN_GET the outer key as BINARY first,
-- then COLUMN_GET the inner key from the result.
SELECT
COLUMN_GET(
COLUMN_GET(attrs, 'wheels' AS BINARY),
'diameter' AS UNSIGNED INTEGER
) AS diameter
FROM product
WHERE sku = 'BIKE-MTB-29';
-- ALWAYS flatten or migrate to JSON if nesting exceeds 2 levels :
-- the SQL is unreadable and error-prone.
Reference Links
references/methods.md : full COLUMN_* function signatures, type tags, return types, version notes, and KB links.
references/examples.md : 10+ working dynamic-column examples (create, read, add, delete, list, JSON, virtual-column index, migration).
references/anti-patterns.md : 6+ dynamic-column mistakes with the fix.
External References
- MariaDB KB :
https://mariadb.com/kb/en/dynamic-columns/
- MariaDB KB :
https://mariadb.com/kb/en/dynamic-columns-functions/
- MariaDB KB :
https://mariadb.com/kb/en/column_create/
- MariaDB KB :
https://mariadb.com/kb/en/column_get/
- MariaDB KB :
https://mariadb.com/kb/en/column_add/
- MariaDB KB :
https://mariadb.com/kb/en/column_delete/
- MariaDB KB :
https://mariadb.com/kb/en/column_exists/
- MariaDB KB :
https://mariadb.com/kb/en/column_list/
- MariaDB KB :
https://mariadb.com/kb/en/column_check/
- MariaDB KB :
https://mariadb.com/kb/en/column_json/
- MariaDB KB :
https://mariadb.com/kb/en/generated-columns/
1---2name: mariadb-syntax-dynamic-columns3description: "Use when storing sparse or heterogeneous attributes inside a single column, comparing dynamic columns with JSON, migrating legacy MariaDB schemas that use dynamic columns, or porting data in or out of MariaDB. Prevents the common mistake of using dynamic columns where JSON would be simpler, the assumption that the feature is portable to MySQL (it is MariaDB-only), and the silent NULL return on a type-mismatched COLUMN_GET. Covers COLUMN_CREATE, COLUMN_ADD, COLUMN_GET, COLUMN_DELETE, COLUMN_EXISTS, COLUMN_LIST, COLUMN_CHECK, COLUMN_JSON, blob storage layout, named vs numbered keys, type tags (CHAR, BINARY, INTEGER, UNSIGNED, DECIMAL, DOUBLE, DATE, TIME, DATETIME), nesting up to 10 levels, when to choose dynamic columns vs JSON, and migration patterns to JSON. Keywords: dynamic columns, COLUMN_CREATE, COLUMN_GET, COLUMN_LIST, COLUMN_ADD, COLUMN_DELETE, COLUMN_EXISTS, COLUMN_JSON, COLUMN_CHECK, MariaDB only, MySQL incompatible, not in MySQL, sparse columns, EAV pattern, blob storage, key-value in column, BLOB co"4license: MIT5---67# MariaDB Dynamic Columns : COLUMN_CREATE, COLUMN_GET, COLUMN_JSON and the MariaDB-only key-value blob89Deterministic 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.1011## Quick Reference1213- **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/`.14- **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/`.15- **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/`.16- **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/`.17- **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/`.18- **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/`.19- **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/`.20- **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/`.21- **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.22- **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))`.23- **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).2425## Decision Trees2627### Tree 1 : "Dynamic columns or JSON?"2829```30Is the data ever read by non-MariaDB clients31 (MySQL replica, BI tool, ETL, MongoDB pipeline) ?32 Yes -> JSON. Dynamic columns are an opaque blob to every other tool.3334Is portability across DBMS engines a requirement ?35 Yes -> JSON. Dynamic columns do not exist outside MariaDB.3637Does the schema need standards-compliant JSONPath38 (JSON_VALUE, JSON_QUERY, JSON_TABLE) ?39 Yes -> JSON. Dynamic columns have no JSONPath equivalent.4041Is the data internal to one MariaDB instance, sparse,42 and you have measured that JSON LONGTEXT is too large43 or too slow for the workload ?44 Yes -> dynamic columns are a defensible choice. Document the measurement.4546Default : JSON with CHECK (JSON_VALID(col)) and functional indexes47 on virtual columns.48```4950### Tree 2 : "Reading a key from a dynamic-column blob"5152```53Do you know the key always exists ?54 No -> first call COLUMN_EXISTS(blob, 'key'). Branch on 1 / 0.55 Yes -> proceed directly to COLUMN_GET.5657Do you know the stored type ?58 Yes -> match the COLUMN_GET tag exactly :59 stored CHAR -> AS CHAR(N)60 stored INTEGER -> AS INTEGER (or AS SIGNED / UNSIGNED)61 stored DECIMAL -> AS DECIMAL(M,D)62 stored DATE / TIME / DATETIME -> the matching tag.63 No -> default to AS CHAR(255) and accept that a stored INTEGER64 is returned as the string "42". Numeric comparisons on the65 result string compare lexicographically, NOT numerically.6667The returned value is NULL ?68 Possible causes :69 1. The key does not exist (re-check with COLUMN_EXISTS).70 2. The blob itself is NULL.71 3. The type tag is incompatible with the stored type.72 4. The blob is corrupt (run COLUMN_CHECK).73```7475### Tree 3 : "Index a dynamic-column key"7677```78Is the key queried by equality or range in WHERE / JOIN / ORDER BY ?79 Yes :80 Step 1 : ALTER TABLE t81 ADD COLUMN k_status VARCHAR(32)82 AS (COLUMN_GET(attrs, 'status' AS CHAR(32))) VIRTUAL;83 Step 2 : CREATE INDEX ix_status ON t (k_status);84 Why VIRTUAL : computed on the fly, no extra disk space, still indexable.85 Why PERSISTENT : if the COLUMN_GET expression is expensive and the column86 is read often without using the index path.87 No, the keys are dynamic and never queried directly ?88 -> Do not index. Keep the blob compact.89```9091### Tree 4 : "Migrate a dynamic-column blob to JSON"9293```94Goal : replace attrs BLOB (dynamic columns) with attrs_json JSON.9596Step 1 : ALTER TABLE t ADD COLUMN attrs_json JSON NULL97 CHECK (JSON_VALID(attrs_json)); -- D-010 reminder9899Step 2 : UPDATE t SET attrs_json = COLUMN_JSON(attrs);100 -- One-shot conversion. Run on a quiet window or chunked101 -- (WHERE id BETWEEN ? AND ?) on a large table.102103Step 3 : Verify : SELECT id FROM t104 WHERE attrs IS NOT NULL AND attrs_json IS NULL;105 -- Expect zero rows. Rows that fail are likely corrupt blobs106 -- (run COLUMN_CHECK to confirm) and need manual repair.107108Step 4 : Rewrite application reads from COLUMN_GET to JSON_VALUE.109110Step 5 : Drop the legacy column :111 ALTER TABLE t DROP COLUMN attrs;112 ALTER TABLE t CHANGE COLUMN attrs_json attrs JSON NULL113 CHECK (JSON_VALID(attrs));114```115116## Patterns117118### Pattern 1 : Create a row with a dynamic-column blob119120```sql121-- 10.6+ : write a sparse attribute set into a single column.122CREATE TABLE product (123 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,124 sku VARCHAR(32) NOT NULL UNIQUE,125 attrs BLOB NULL126) ENGINE=InnoDB;127128INSERT INTO product (sku, attrs) VALUES (129 'CHAIR-RED-L',130 COLUMN_CREATE(131 'color', 'red' AS CHAR(16),132 'size', 'L' AS CHAR(4),133 'weight', 12.5 AS DECIMAL(6,2),134 'stock', 120 AS UNSIGNED INTEGER135 )136);137```138139### Pattern 2 : Read a typed key with explicit existence check140141```sql142-- 10.6+ : guard with COLUMN_EXISTS, then COLUMN_GET with the right type tag.143SELECT144 id,145 sku,146 CASE147 WHEN COLUMN_EXISTS(attrs, 'stock') = 1148 THEN COLUMN_GET(attrs, 'stock' AS UNSIGNED INTEGER)149 ELSE NULL150 END AS stock_level,151 COLUMN_GET(attrs, 'color' AS CHAR(16)) AS color152FROM product153WHERE sku = 'CHAIR-RED-L';154```155156### Pattern 3 : Add or update a key with COLUMN_ADD157158```sql159-- 10.6+ : COLUMN_ADD inserts new keys and overwrites existing keys.160UPDATE product161SET attrs = COLUMN_ADD(162 attrs,163 'stock', 90 AS UNSIGNED INTEGER,164 'reorder', '2026-06-01' AS DATE,165 'finish', 'matte' AS CHAR(16)166)167WHERE sku = 'CHAIR-RED-L';168-- 'stock' is overwritten ; 'reorder' and 'finish' are added.169-- Passing NULL as the value for a key DELETES the key.170```171172### Pattern 4 : Delete one or more keys with COLUMN_DELETE173174```sql175-- 10.6+ : drop a key from the blob.176UPDATE product177SET attrs = COLUMN_DELETE(attrs, 'finish', 'weight')178WHERE sku = 'CHAIR-RED-L';179-- Multiple keys can be deleted in one call.180-- Deleting a non-existent key is a no-op (no warning).181```182183### Pattern 5 : List all keys in a blob184185```sql186-- 10.6+ : COLUMN_LIST returns a comma-separated list of backtick-quoted names.187SELECT sku, COLUMN_LIST(attrs) AS keys188FROM product189WHERE attrs IS NOT NULL;190-- Example output : `color`,`size`,`stock`,`reorder`191-- NEVER parse this string in SQL. Use it for debug / display only ;192-- iterate keys in application code by name.193```194195### Pattern 6 : Export to JSON with COLUMN_JSON196197```sql198-- 10.6+ : single-shot conversion to a JSON document.199SELECT id, sku, COLUMN_JSON(attrs) AS attrs_json200FROM product201WHERE sku = 'CHAIR-RED-L';202-- attrs_json example :203-- {"color":"red","size":"L","stock":90,"reorder":"2026-06-01","color":"red"}204-- ALWAYS use COLUMN_JSON for export, audit logs, and migration to JSON column.205```206207### Pattern 7 : Index a dynamic-column key via a virtual column208209```sql210-- 10.6+ : the blob is opaque ; index a virtual column instead.211ALTER TABLE product212 ADD COLUMN k_color VARCHAR(16)213 AS (COLUMN_GET(attrs, 'color' AS CHAR(16))) VIRTUAL,214 ADD INDEX ix_product_color (k_color);215216-- The optimiser uses ix_product_color for :217SELECT id, sku FROM product WHERE k_color = 'red';218-- WHERE COLUMN_GET(attrs, 'color' AS CHAR(16)) = 'red' -- also works,219-- but ONLY because MariaDB matches the expression to k_color's definition.220-- Always prefer querying the virtual column directly for clarity.221```222223### Pattern 8 : Validate blob integrity with CHECK + COLUMN_CHECK224225```sql226-- 10.6+ : refuse corrupt writes at schema level.227CREATE TABLE product_v2 (228 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,229 sku VARCHAR(32) NOT NULL UNIQUE,230 attrs BLOB NULL,231 CONSTRAINT chk_attrs_blob CHECK (attrs IS NULL OR COLUMN_CHECK(attrs) = 1)232) ENGINE=InnoDB;233-- An INSERT or UPDATE that produces a malformed blob fails with234-- "CONSTRAINT 'chk_attrs_blob' failed".235```236237### Pattern 9 : Migrate to JSON in a single transaction238239```sql240-- 10.6+ : add a JSON column, populate from COLUMN_JSON, verify, drop legacy.241START TRANSACTION;242243ALTER TABLE product244 ADD COLUMN attrs_json JSON NULL245 CHECK (attrs_json IS NULL OR JSON_VALID(attrs_json));246247UPDATE product248 SET attrs_json = COLUMN_JSON(attrs)249 WHERE attrs IS NOT NULL;250251-- Verify no rows failed conversion.252SELECT COUNT(*) AS failed253FROM product254WHERE attrs IS NOT NULL AND attrs_json IS NULL;255-- If failed > 0, ROLLBACK and inspect with COLUMN_CHECK(attrs).256257COMMIT;258```259260### Pattern 10 : Nested dynamic columns (up to 10 levels)261262```sql263-- 10.6+ : a dynamic-column value can itself be a dynamic-column blob.264INSERT INTO product (sku, attrs) VALUES (265 'BIKE-MTB-29',266 COLUMN_CREATE(267 'frame', 'aluminium' AS CHAR(16),268 'wheels', COLUMN_CREATE(269 'diameter', 29 AS UNSIGNED INTEGER,270 'tubeless', 'Y' AS CHAR(1)271 )272 )273);274-- To read a nested key, COLUMN_GET the outer key as BINARY first,275-- then COLUMN_GET the inner key from the result.276SELECT277 COLUMN_GET(278 COLUMN_GET(attrs, 'wheels' AS BINARY),279 'diameter' AS UNSIGNED INTEGER280 ) AS diameter281FROM product282WHERE sku = 'BIKE-MTB-29';283-- ALWAYS flatten or migrate to JSON if nesting exceeds 2 levels :284-- the SQL is unreadable and error-prone.285```286287## Reference Links288289- `references/methods.md` : full COLUMN_* function signatures, type tags, return types, version notes, and KB links.290- `references/examples.md` : 10+ working dynamic-column examples (create, read, add, delete, list, JSON, virtual-column index, migration).291- `references/anti-patterns.md` : 6+ dynamic-column mistakes with the fix.292293## External References294295- MariaDB KB : `https://mariadb.com/kb/en/dynamic-columns/`296- MariaDB KB : `https://mariadb.com/kb/en/dynamic-columns-functions/`297- MariaDB KB : `https://mariadb.com/kb/en/column_create/`298- MariaDB KB : `https://mariadb.com/kb/en/column_get/`299- MariaDB KB : `https://mariadb.com/kb/en/column_add/`300- MariaDB KB : `https://mariadb.com/kb/en/column_delete/`301- MariaDB KB : `https://mariadb.com/kb/en/column_exists/`302- MariaDB KB : `https://mariadb.com/kb/en/column_list/`303- MariaDB KB : `https://mariadb.com/kb/en/column_check/`304- MariaDB KB : `https://mariadb.com/kb/en/column_json/`305- MariaDB KB : `https://mariadb.com/kb/en/generated-columns/`