MariaDB 10.0.X中，动态列（Dynamic Columns），可以支持 JSON 格式来获取数据。

为了兼容传统SQL语法，MariaDB 10和MySQL5.7支持原生JSON格式，即关系型数据库和文档型NoSQL数据库集于一身。

使用说明：

###表结构

createtableassets(item_namevarchar(32)primarykey,--Acommonattributeforallitemsdynamic_colsblob--Dynamiccolumnswillbestoredhere);

###插入JSON格式数据

mysql>INSERTINTOassetsVALUES->('MariaDBT-shirt',COLUMN_CREATE('color','blue','size','XL'));QueryOK,1rowaffected(0.02sec)mysql>INSERTINTOassetsVALUES->('ThinkpadLaptop',COLUMN_CREATE('color','black','price',500));QueryOK,1rowaffected(0.01sec)

###获取Key（键）color的Value（值）：

mysql>SELECTitem_name,COLUMN_GET(dynamic_cols,'color'aschar)AScolorFROMassets;+-----------------+-------+|item_name|color|+-----------------+-------+|MariaDBT-shirt|blue||ThinkpadLaptop|black|+-----------------+-------+2rowsinset(0.00sec)

###获取全部Key（键）

mysql>SELECTitem_name,column_list(dynamic_cols)FROMassets;+-----------------+---------------------------+|item_name|column_list(dynamic_cols)|+-----------------+---------------------------+|MariaDBT-shirt|`size`,`color`||ThinkpadLaptop|`color`,`price`|+-----------------+---------------------------+2rowsinset(0.00sec)

###获取全部Key-Value

mysql>SELECTitem_name,COLUMN_JSON(dynamic_cols)FROMassets;+-----------------+-------------------------------+|item_name|COLUMN_JSON(dynamic_cols)|+-----------------+-------------------------------+|MariaDBT-shirt|{"size":"XL","color":"blue"}||ThinkpadLaptop|{"color":"black","price":500}|+-----------------+-------------------------------+2rowsinset(0.01sec)

###删除一个Key-Value：

mysql>UPDATEassetsSETdynamic_cols=COLUMN_DELETE(dynamic_cols,"price")->WHERECOLUMN_GET(dynamic_cols,'color'aschar)='black';QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0mysql>SELECTitem_name,COLUMN_JSON(dynamic_cols)FROMassets;+-----------------+------------------------------+|item_name|COLUMN_JSON(dynamic_cols)|+-----------------+------------------------------+|MariaDBT-shirt|{"size":"XL","color":"blue"}||ThinkpadLaptop|{"color":"black"}|+-----------------+------------------------------+2rowsinset(0.00sec)

###增加一个Key-Value：

mysql>UPDATEassetsSETdynamic_cols=COLUMN_ADD(dynamic_cols,'warranty','3years')->WHEREitem_name='ThinkpadLaptop';QueryOK,1rowaffected(0.01sec)Rowsmatched:1Changed:1Warnings:0mysql>SELECTitem_name,COLUMN_JSON(dynamic_cols)FROMassets;+-----------------+----------------------------------------+|item_name|COLUMN_JSON(dynamic_cols)|+-----------------+----------------------------------------+|MariaDBT-shirt|{"size":"XL","color":"blue"}||ThinkpadLaptop|{"color":"black","warranty":"3years"}|+-----------------+----------------------------------------+2rowsinset(0.00sec)

###更改一个Key-Value：

mysql>UPDATEassetsSETdynamic_cols=COLUMN_ADD(dynamic_cols,'color','white')WHERECOLUMN_GET(dynamic_cols,'color'aschar)='black';QueryOK,1rowaffected(0.01sec)Rowsmatched:1Changed:1Warnings:0mysql>SELECTitem_name,COLUMN_JSON(dynamic_cols)FROMassets;+-----------------+----------------------------------------+|item_name|COLUMN_JSON(dynamic_cols)|+-----------------+----------------------------------------+|MariaDBT-shirt|{"size":"XL","color":"blue"}||ThinkpadLaptop|{"color":"white","warranty":"3years"}|+-----------------+----------------------------------------+2rowsinset(0.00sec)