版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
Part1:JSON格式的支持
版本终于支持了原生的JSON格式,即将关系型数据库和文档型NO_SQL数据库集于一身。本文接下来将对这特性分别就和各自实现的方法异同进行介绍和演示。
Part2:创建相应表结构
[root@HE3 ~]# mysql -V
mysql Ver Distrib , for linux- (x86_64) using EditLine wrapper
- mysql> create database helei;
- Query OK, 1 row affected ( sec)
- mysql> use helei;
- Database changed
- mysql> create table helei (id int(10) unsigned NOT NULL,context json default null,primary key(id));
- Query OK, 0 rows affected ( sec)
- mysql> show create table helei \G
- *************************** 1. row ***************************
- Table: helei
- Create Table: CREATE TABLE `helei` (
- `id` int(10) unsigned NOT NULL,
- `context` json DEFAULT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8
- 1 row in set ( sec)
Part3:构造数据&测试
- mysql> desc helei;
- +---------+------------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +---------+------------------+------+-----+---------+-------+
- | id | int(10) unsigned | NO | PRI | NULL | |
- | context | json | YES | | NULL | |
- +---------+------------------+------+-----+---------+-------+
- 2 rows in set ( sec)
- mysql> insert into helei values(1,'{"name":"贺磊","age":100}'),(2,'{"name":"陈加持","age":30}'),(3,'{"name":"于浩","age":28}');
- Query OK, 3 rows affected ( sec)
- Records: 3 Duplicates: 0 Warnings: 0
- mysql> select * from helei;
- +----+----------------------------------+
- | id | context |
- +----+----------------------------------+
- | 1 | {"age": 100, "name": "贺磊"} |
- | 2 | {"age": 30, "name": "陈加持"} |
- | 3 | {"age": 28, "name": "于浩"} |
- +----+----------------------------------+
- 3 rows in set ( sec)
- mysql> select id,JSON_EXTRACT(context,'$.name') name,JSON_EXTRACT(context,'$.age') age from helei;
- +----+-------------+------+
- | id | name | age |
- +----+-------------+------+
- | 1 | "贺磊" | 100 |
- | 2 | "陈加持" | 30 |
- | 3 | "于浩" | 28 |
- +----+-------------+------+
- 3 rows in set ( sec)
- 获取Key-Value
- mysql> select id,json_keys(context) from helei;
- +----+--------------------+
- | id | json_keys(context) |
- +----+--------------------+
- | 1 | ["age", "name"] |
- | 2 | ["age", "name"] |
- | 3 | ["age", "name"] |
- +----+--------------------+
- 3 rows in set ( sec)
- 获取全部Key
- mysql> update helei set context=JSON_INSERT(context,'$.name',"贺磊",'$.address','beijing')where id=1;
- Query OK, 1 row affected ( sec)
- Rows matched: 1 Changed: 1 Warnings: 0
- mysql> select * from helei;
- +----+------------------------------------------------------+
- | id | context |
- +----+------------------------------------------------------+
- | 1 | {"age": 100, "name": "贺磊", "address": "beijing"} |
- | 2 | {"age": 30, "name": "陈加持"} |
- | 3 | {"age": 28, "name": "于浩"} |
- +----+------------------------------------------------------+
- 3 rows in set ( sec)
- 增加Key-Value
- mysql> update helei set context=JSON_SET(context,'$.name',"高穷帅")where id=1;
- Query OK, 1 row affected ( sec)
- Rows matched: 1 Changed: 1 Warnings: 0
- mysql> select * from helei;
- +----+---------------------------------------------------------+
- | id | context |
- +----+---------------------------------------------------------+
- | 1 | {"age": 100, "name": "高穷帅", "address": "beijing"} |
- | 2 | {"age": 30, "name": "陈加持"} |
- | 3 | {"age": 28, "name": "于浩"} |
- +----+---------------------------------------------------------+
- 3 rows in set ( sec)
- 变更key-value
- mysql> update helei set context=JSON_REMOVE(context,'$.name') where id=1;
- Query OK, 1 row affected ( sec)
- Rows matched: 1 Changed: 1 Warnings: 0
- mysql> select * from helei;
- +----+------------------------------------+
- | id | context |
- +----+------------------------------------+
- | 1 | {"age": 100, "address": "beijing"} |
- | 2 | {"age": 30, "name": "陈加持"} |
- | 3 | {"age": 28, "name": "于浩"} |
- +----+------------------------------------+
- 3 rows in set ( sec)
- 删除Key-Value
JSON格式存储BLOB的测试
Part1:Dynamic Columns处理方式的异同
①的动态列JSON格式存储
- mysql> insert into helei_blob values(1,'{"name":"贺磊","age":100}'),(2,'{"name":"陈加持","age":30}'),(3,'{"name":"于浩","age":28}');
- Query OK, 3 rows affected ( sec)
- Records: 3 Duplicates: 0 Warnings: 0
- mysql> select * from helei_blob;
- +----+-------------------------------+
- | id | blob_col |
- +----+-------------------------------+
- | 1 | {"name":"贺磊","age":100} |
- | 2 | {"name":"陈加持","age":30} |
- | 3 | {"name":"于浩","age":28} |
- +----+-------------------------------+
- 3 rows in set ( sec)
②MariaDB的动态列JSON格式存储
- MariaDB [helei]> create table helei (id int(10) unsigned NOT NULL,context json default null,primary key(id));
- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json default null,primary key(id))' at line 1
- 可以看到MariaDB并不能直接存储JSON类型。
- MariaDB [helei]> show create table helei_blob\G;
- *************************** 1. row ***************************
- Table: helei_blob
- Create Table: CREATE TABLE `helei_blob` (
- `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
- `blob_col` blob,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8
- 1 row in set ( sec)
- ERROR: No query specified
- MariaDB [helei]> insert into helei_blob values(5,column_create('color','blue','size','XL'));
- Query OK, 1 row affected ( sec)
- MariaDB [helei]> select * from helei_blob;
- +----+--------------------------------+
- | id | blob_col |
- +----+--------------------------------+
- | 1 | {"name":"贺磊","age":100} |
- | 2 | {"name":"陈加持","age":30} |
- | 3 | {"name":"于浩","age":28} |
- | 5 | 3 sizecolor!XL!blue |
- +----+--------------------------------+
- 4 rows in set ( sec)
- 直接查询是乱码需用以下函数查询
- MariaDB [helei]> select id,column_json(blob_col) from helei_blob where id =5;
- +----+------------------------------+
- | id | column_json(blob_col) |
- +----+------------------------------+
- | 5 | {"size":"XL","color":"blue"} |
- +----+------------------------------+
- 1 row in set ( sec)
- MariaDB [helei]> select id,column_list(blob_col) from helei_blob where id =5;
- +----+-----------------------+
- | id | column_list(blob_col) |
- +----+-----------------------+
- | 5 | `size`,`color` |
- +----+-----------------------+
- 1 row in set ( sec)
- 获取全部Key
- MariaDB [helei]> select id,column_get(blob_col,'color' as char) as color from helei_blob where id =5;
- +----+-------+
- | id | color |
- +----+-------+
- | 5 | blue |
- +----+-------+
- 1 row in set ( sec)
- 获取Key-Value
- MariaDB [helei]> update helei_blob set blob_col=column_add(blob_col,'sex','man') where id=5;
- Query OK, 1 row affected ( sec)
- Rows matched: 1 Changed: 1 Warnings: 0
- MariaDB [helei]> select id,column_json(blob_col) from helei_blob where id=5;
- +----+------------------------------------------+
- | id | column_json(blob_col) |
- +----+------------------------------------------+
- | 5 | {"sex":"man","size":"XL","color":"blue"} |
- +----+------------------------------------------+
- 1 row in set ( sec)
- 增加Key-Value
- MariaDB [helei]> update helei_blob set blob_col=column_add(blob_col,'color','black') where id=5;
- Query OK, 1 row affected ( sec)
- Rows matched: 1 Changed: 1 Warnings: 0
- MariaDB [helei]> select id,column_json(blob_col) from helei_blob where id=5;
- +----+-------------------------------------------+
- | id | column_json(blob_col) |
- +----+-------------------------------------------+
- | 5 | {"sex":"man","size":"XL","color":"black"} |
- +----+-------------------------------------------+
- 1 row in set ( sec)
- 更改Key-Value
- MariaDB [helei]> update helei_blob set blob_col=column_delete(blob_col,'sex','man') where id=5;
- Query OK, 1 row affected ( sec)
- Rows matched: 1 Changed: 1 Warnings: 0
- MariaDB [helei]> select id,column_json(blob_col) from helei_blob where id=5;
- +----+-------------------------------+
- | id | column_json(blob_col) |
- +----+-------------------------------+
- | 5 | {"size":"XL","color":"black"} |
- +----+-------------------------------+
- 1 row in set ( sec)
- 删除Key-Value
——总结——
虽然和/版本对于JSON的支持是比较完整的,不过MongoDB的Sharding功能更加好用,个人更倾向于MongoDB。由于笔者的水平有限,编写时间也很仓促,文中难免会出现一些错误或者不准确的地方,不妥之处恳请读者批评指正。