MariaDB 10.0.X中，动态列（Dynamic Columns），可以支持 JSON 格式来获取数据。
为了兼容传统SQL语法，MariaDB 10和MySQL5.7支持原生JSON格式，即关系型数据库和文档型NoSQL数据库集于一身。
使用说明：
###表结构
|
1
2
3
4
|
create table assets ( item_name varchar(32) primary key, -- A common attribute for all items
dynamic_cols blob -- Dynamic columns will be stored here
); |
###插入JSON格式数据
|
1
2
3
4
5
6
7
|
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（键）color的Value（值）：
|
1
2
3
4
5
6
7
8
|
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（键）
|
1
2
3
4
5
6
7
8
|
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
|
1
2
3
4
5
6
7
8
|
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":500} |
+-----------------+-------------------------------+2 rows in set (0.01 sec)
|
###删除一个Key-Value：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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)
|
本文转自hcymysql51CTO博客，原文链接： http://blog.51cto.com/hcymysql/1694181，如需转载请自行联系原作者