In MariaDB 10.0.X, dynamic columns support JSON format for data acquisition.

MariaDB 10.0.X, dynamic columns (Columns) can support JSON format for data acquisition.

To be compatible with traditional SQL syntax, MariaDB 10 and MySQL5.7 support native JSON format, which is a relational database and a document-type NoSQL database .

Instructions for use:

# # #表结构

CREATE TABLE assets (item_name varchar) primary key,--A common attribute for all items Dynamic_cols blob--Dyna Mic columns'll be stored here);

# # #插入JSON格式数据

Mysql> INSERT into Assets VALUES--(' MariaDB T-shirt ', column_create (' Color ', ' blue ', ' size ', ' XL ')); Query OK, 1 row affected (0.02 sec) mysql> INSERT into assets VALUES---' Thinkpad Laptop ', column_create (' Color ', ' black ', ' price ', 500)); Query OK, 1 row affected (0.01 sec)

# # #获取Key (key) color value (value):

Mysql> SELECT Item_name, Column_get (dynamic_cols, ' color ' as char) as color from assets;+-----------------+-------+| Item_name | Color |+-----------------+-------+| MariaDB T-shirt | Blue | | Thinkpad Laptop | Black |+-----------------+-------+2 rows in Set (0.00 sec)

# # #获取全部Key (Key)

Mysql> SELECT Item_name, column_list (dynamic_cols) from assets;+-----------------+---------------------------+| Item_name | Column_list (dynamic_cols) |+-----------------+---------------------------+| MariaDB T-shirt | ' Size ', ' Color ' | | Thinkpad Laptop | ' Color ', ' price ' |+-----------------+---------------------------+2 rows in Set (0.00 sec)

# # #获取全部Key-value

Mysql> 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", "price": $ |+-----------------+-------------------------------+2 rows in Set (0.01 sec)

# # #删除一个Key-value:

Mysql> update assets set dynamic_cols=column_delete (dynamic_cols,  "price")       -> where column_get (dynamic_cols,  ' color '  as char) = ' black ' ; query ok, 1 row affected  (0.00 sec) rows matched: 1  changed:  1  warnings: 0mysql> 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"}             |+-----------------+------------------------------+2 rows in set  (0.00  SEC)

# # #增加一个Key-value:

Mysql> update assets set dynamic_cols=column_add (dynamic_cols,  ' warranty ',  ' 3  years ')     -> where item_name= ' thinkpad laptop '; query ok, 1 row affected  (0.01 sec) rows matched: 1  changed:  1  warnings: 0mysql> 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"} |+-----------------+-- --------------------------------------+2 rows in set  (0.00 SEC)

# # #更改一个Key-value:

Mysql> update assets set dynamic_cols=column_add (dynamic_cols, ' Color ',  ' white ')  where column_get (dynamic_cols,  ' color '  as char) = ' black '; query ok, 1 row affected  (0.01 sec) rows matched: 1  changed:  1  warnings: 0mysql> 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": "White", "Warranty": "3 years"} |+-----------------+-- --------------------------------------+2 rows in set  (0.00 SEC) 

This article is from the "Spring Yang Technical column" blog, please be sure to keep this source http://hcymysql.blog.51cto.com/5223301/1694181

In MariaDB 10.0.X, dynamic columns support JSON format for data acquisition.