MariaDB 10.0.X中,动态列支持 JSON 格式来获取数据
MariaDB 10.0.X中,动态列支持 JSON 格式来获取数据
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: 0
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" } |
+-----------------+------------------------------+
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: 0
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" , "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: 0
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" : "white" , "warranty" : "3 years" } |
+-----------------+----------------------------------------+
2 rows in set (0.00 sec)
|
Linux系统教程:如何检查MariaDB服务端版本
MariaDB Proxy读写分离的实现
Linux下编译安装配置MariaDB数据库的方法
CentOS系统使用yum安装MariaDB数据库
安装MariaDB与MySQL并存
Ubuntu 上如何将 MySQL 5.5 数据库迁移到 MariaDB 10
[翻译]Ubuntu 14.04 (Trusty) Server 安装 MariaDB
评论暂时关闭