I think this is nice for completeness but is probably not very practical.

I work with the Yii 2.0 Active Record ORM. This ORM has no configuration. It reads a table description from the server and uses it to relate object properties with table column names, datatypes and values. My work attempts to extend this ORM to dynamic columns yii2-dynamic-ar and has three main parts:

1. Saving models to table records
2. Loading table records into models
3. Allowing individual dynamic columns to be used in queries, e.g. in a WHERE condition

2. is the hardest because, unlike normal columns, the ORM doesn't know anything about the content of dyncol_blobs. COLUMN_JSON() is convenient because it can load the whole dyncol_blob at once. But if I rely on it then I have to avoid using BINARY in a dynamic column (JSON can represent only valid Unicode strings) and avoid non-Unicode column names. Let's see how could I use COLUMN_GET_SQL() to overcome this limitation...

First the ORM would use COLUMN_LIST() to get dynamic column names. In the next query, it would use a number of COLUMN_GET_SQL() expressions to load datatypes and values. COLUMN_GET_SQL() would somehow indicate if a dynamic column itself contains a dyncol_blob, in which case the ORM makes two more queries to load first the names and then the types/values, and it keeps iterating like this down the data structure tree until it has everything.

You can imagine how I don't think this is very practical.

Alternatives:

1. COLUMN_GET_SQL(dyncol_blob) without the column name to that returns a possibly long, possibly nested COLUMN_CREATE() expression (with casts).
2. Follow the example of SHOW CREATE TABLE. I haven't worked out any details but it would return a table of strings which describe everything in the dyncol_blob: structure, column names, datatypes, values. It would use the standard client protocol to return the table.
3. Use the same serialization that mysqldump uses for dynamic columns.
4. Give up and impelment a new protocol, e.g. MongoDB's BSON, or Google's Protocol Buffers.

I like the 2. best. 1. requires me to write an SQL parser. 3. Requires standardizing (and probably versioning) the internal blob encoding. 4. is radical. 2 uses current protocols and is easy to live with. Perhaps:

> SELECT COLUMN_GET_ALL(`specification`) FROM `product`;

+--------------+--------------+------------+

| Name         | Datatype     | Value      |

+--------------+--------------+------------+

| category     | CHAR         | Transistor |

| description  | CHAR         | TO-91      |

| manufacturer | CHAR         | Fairchild  |

| price        | BLOB         | NULL       |

| price.unit   | DECIMAL(9,5) | 0.03315    |

| price.100    | DECIMAL(9,5) | 2.45000    |

+--------------+--------------+------------+