一个技术博客
Hi! (192.168.42.7)

(二) Mysql5.7 原生JSON格式支持 新增X plugin

本文由 Tim 于 2016-04-19 17:48:21 发表
2016-04-19 分类：mysql / 技术 阅读(3875) 评论(0)

一、原生JSON格式支持

JSON(JavaScript Object Notation) 是一种轻量级的数据交换格式。 易于人阅读和编写，同时也易于机器解析和生成(一般用于提升网络传输速率)。JSON的格式非常简单：名称/键值。之前MySQL版本里面要实现这样的存储，通常使用VARCHAR或者TEXT。 并且由程序进行解析，之前MariaDB也有对非结构化的数据进行存储的方案，称为dynamic column，但是方案是通过BLOB类型的方式来存储。查询性能不高，不能有效建立索引。有兴趣的同学可以了解下dynamic column     通过MARIADB的DYNAMIC COLUMN功能实现非结构化存储

现在Mysql5.7.9（GA）InnoDB存储引擎已经原生支持JSON格式，该格式不是简单的BLOB类似的替换。原生的JSON格式支持有以下的优势：

（1）JSON数据有效性检查：BLOB类型无法在数据库层做这样的约束性检查
（2）查询性能的提升：查询不需要遍历所有字符串才能找到数据
（3）支持索引：通过虚拟列的功能可以对JSON中的部分数据进行索引

建表

1
2
3
4
5
CREATE TABLE `jsontest` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

将列data定义为了JSON类型。这意味着我们可以对插入的数据做JSON格式检查，确保其符合JSON格式的约束，如插入一条不合法的JSON数据会报错

插入一行，使用内置的json_object转换成JSON格式

1
2
3
4
mysql> insert into jsontest(data) values(json_object('uid' ,1, 'name', 'Tim', 'age' , 1));
Query OK, 1 row affected (0.00 sec)
mysql> insert into jsontest(data) values(json_object('uid' ,2, 'name', 'test', 'age' , 1));
Query OK, 1 row affected (0.01 sec)

此时jsontest表内数据如下

1
2
3
4
5
6
7
8
mysql> select * from jsontest;
+----+--------------------------------------+
| id | data |
+----+--------------------------------------+
| 1 | {"age": 1, "uid": 1, "name": "Tim"} |
| 2 | {"age": 1, "uid": 2, "name": "test"} |
+----+--------------------------------------+
2 rows in set (0.00 sec)

使用json_extract解析出来

1
2
3
4
5
6
7
8
mysql> select json_extract(data,'$.uid'),json_extract(data,'$.name'),json_extract(data,'$.age') from jsontest;
+----------------------------+-----------------------------+----------------------------+
| json_extract(data,'$.uid') | json_extract(data,'$.name') | json_extract(data,'$.age') |
+----------------------------+-----------------------------+----------------------------+
| 1 | "Tim" | 1 |
| 2 | "test" | 1 |
+----------------------------+-----------------------------+----------------------------+
2 rows in set (0.00 sec)

可以看到json_object，json_extract类似php里的json_encode，json_decode; 同时Mysql还提供了合并多个Json格式的json_merge，移除元素的json_remove等

详见http://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

最令人的激动的功能应该是MySQL 5.7的虚拟列功能，通过传统的B+树索引即可实现对JSON格式部分属性的快速查询。使用方法是首先创建该虚拟列，然后在该虚拟列上创建索引：

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> ALTER TABLE jsontest ADD user_name varchar(128) GENERATED ALWAYS AS (json_extract(data,'$.name')) VIRTUAL;
 
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
 
mysql> select user_name from jsontest;
+-----------+
| user_name |
+-----------+
| "Tim" |
| "test" |
+-----------+
2 rows in set (0.00 sec)

为虚拟列添加索引

1
alter table jsontest add index idx_username (user_name);

1
2
3
4
5
6
7
mysql> explain select * from jsontest where user_name ='Tim';
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | jsontest | NULL | ref | idx_username | idx_username | 387 | const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

通过explain可以看到优化器已经选择了在虚拟列上创建的新索引。

 

二、X plugin存储文件数据

在Mysql最新发布的5.7.12（GA）上发布了X plugin，官方解释如下，大意为X plugin可以将Mysql扩展为文件数据库存储数据，X Plugin使用X Protocol协议连接，当基于ACID的存储引擎时可以实现事务等特性，提供接近NoSQL的操作。

X Plugin extends MySQL Server to be able to function as a document store. Running X Plugin enables MySQL Server to communicate with clients using the X Protocol, which is designed to expose the ACID compliant storage abilities of MySQL as a document store. Documents are stored in JSON format and enable schema-less storage. Using the X DevAPI you can use a NoSQL-like syntax to execute Create, Read, Update, Delete (CRUD) operations against these documents.

1、安装mysql-shell，官方安装文档

If you already have the MySQL Yum repository as a software repository on your system and the repository was configured with the new release package mysql57-community-release, skip to the next step (“Enable the MySQL Tools Preview subrepository…”).

看到如上这段，因为我之前装Mysql5.7时使用了MySQL Yum repository，因此直接到Enable the MySQL Tools Preview subrepository这一段，读者可以根据自行情况安装。

vi /etc/yum.repos.d/mysql-community.repo 添加如下段，并将修改enabled=1

1
2
3
4
5
6
[mysql-tools-preview]
name=MySQL Tools Preview
baseurl=http://repo.mysql.com/yum/mysql-tools-preview/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

安装mysql-shell

1
yum -y install mysql-shell

1
2
[root@mysql5 mysql]# mysqlsh --version
MySQL Shell Version 1.0.3 Development Preview

安装完成，可以看到Mysql shell 版本为1.0.3
此时在运行show plugins; 也可以看到

1
2
mysql> show plugins;
| mysqlx                     | ACTIVE   | DAEMON             | mysqlx.so            | GPL     |

2、连接

第一次使用mysqlsh -u root -h localhost -p –classic –dba enableXProtocol 进行初始化，密码为Mysql的密码

1
2
3
4
5
6
7
[root@mysql5 mysql]# mysqlsh -u root -h localhost -p --classic --dba enableXProtocol
Creating a Classic Session to root@localhost:3306
Enter password:
No default schema selected.
 
enableXProtocol: Installing plugin mysqlx...
enableXProtocol: done

mysqlsh默认端口为33060
使用mysqlsh –u root 连接，进入mysql-js命令行模式

1
2
3
4
5
6
7
8
[root@mysql5 mysql]# mysqlsh --u root
Creating an X Session to root@localhost:33060
Enter password:
No default schema selected.
Welcome to MySQL Shell 1.0.3 Development Preview
 
Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js>

\h可以查看帮助，\sql可以进入sql模式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql-js> \h
===== Global Commands =====
\help (\?,\h) Print this help.
\sql Sets shell on SQL processing mode.
\js Sets shell on JavaScript processing mode.
\py Sets shell on Python processing mode.
\source (\.) Execute a script file. Takes a file name as an argument.
\ Start multiline input when in SQL mode. Finish and execute with an empty line.
\quit (\q,\exit) Quit mysh.
\connect (\cx) Connect to server using an application mode session.
\connect_node (\cn) Connect to server using a node session.
\connect_classic (\cc) Connect to server using the MySQL protocol.
\warnings (\W) Show warnings after every statement.
\nowarnings (\w) Don't show warnings after every statement.
\status (\s) Prints information about the current global connection.
\addconn (\addc) Inserts/updates new/existing connection into the connection registry.
\rmconn Removes a connection from the connection registry.
\lsconn (\lsc) List the contents of all connections currently in the registry.
\chconn Updates a stored connection.
 
For help on a specific command use the command as \? <command>

 

导入示例库，world_x-db.zip下载地址http://downloads.mysql.com/docs/world_x-db.zip

解压后有两个文件，查看README.txt中有执行命令，写的很详细，看到需要MySQL Server 5.7.12 or higher

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
[root@mysql5 world_x-db]# ls
README.txt world_x.sql
[root@mysql5 world_x-db]# cat README.txt
This directory contains the file that can be used to set up the world_x
database that is used in the guides of the MySQL Reference
Manual:
Quick-Start Guide: MySQL Shell for JavaScript
Quick-Start Guide: MySQL Shell for Python
These instructions assume that your current working directory is
the directory that contains the files created by unpacking the
world_x.zip or world_x.tar.gz distribution.
You must install MySQL Shell and MySQL Server 5.7.12 or higher
with the X Plugin enabled. Start the server before you load the
world_x database.
Extract the installation archive to a temporary location such as /tmp/.
Unpacking the archive results in a single file named world_x.sql.
Create or recreate the schema with one of the following commands:
 
Either use MySQL Shell:
shell> mysqlsh -u root --sql --recreate-schema world_x < /tmp/world_x.sql
Or the standard MySQL command-line client:
Connect to MySQL:
shell> mysql -u root -p
Load the file:
mysql> SOURCE /tmp/world_x.sql;
Enter your password when prompted. A non-root account can be used as long as
the account has privileges to create new databases.
Replace /tmp/ with the path to the world_x.sql file on your system.

导入world_x-db.zip示例库

1
mysqlsh -u root --sql --recreate-schema world_x < /tmp/world_x.sql

进入world_x示例库

1
mysqlsh -u root -p world_x

查看数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
mysql-js> db
<Schema:world_x>
mysql-js> db.collections;
{
"CountryInfo": <Collection:CountryInfo>
}
mysql-js> db.CountryInfo.find().limit(1);
[
{
"GNP": 828,
"IndepYear": null,
"Name": "Aruba",
"_id": "ABW",
"demographics": {
"LifeExpectancy": 78.4000015258789,
"Population": 103000
},
"geography": {
"Continent": "North America",
"Region": "Caribbean",
"SurfaceArea": 193
},
"government": {
"GovernmentForm": "Nonmetropolitan Territory of The Netherlands",
"HeadOfState": "Beatrix"
}
}
]
1 document in set (0.00 sec)

创建schema，可以看到在js模式创建会报错

1
2
3
4
5
6
7
8
9
10
11
12
mysql-js> CREATE SCHEMA testx;
SyntaxError: Unexpected identifier at (shell):1:7
in CREATE SCHEMA testx;
^^^^^^
SyntaxError: Unexpected identifier
 
mysql-js> \sql
The active session is an X Session.
SQL mode is not supported on X Sessions: command ignored.
To switch to SQL mode reconnect with a Node Session by either:
* Using the \connect_node shell command.
* Using --session-type=node when calling the MySQL Shell on the command line.

需要切换到sql模式，不过sql模式需要运行在Node session，因此mysqlsh –session-type=node -u root -p world_x重新连接并进入sql模式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[root@mysql5 world_x-db]# mysqlsh --session-type=node -u root -p world_x
Creating a Node Session to root@localhost:33060/world_x
Enter password:
Default schema `world_x` accessible through db.
 
Welcome to MySQL Shell 1.0.3 Development Preview
 
Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type '\help', '\h' or '\?' for help.
 
Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> \sql
Switching to SQL mode... Commands end with ;
mysql-sql> create schema testx;
Query OK, 1 row affected (0.00 sec)

切换到testx

1
2
3
4
mysql-js> db;
<Schema:world_x>
mysql-js> db= session.getSchema("testx");
<Schema:testx>

创建collection

1
2
3
4
5
6
7
8
9
mysql-js> db.createCollection("id");
<Collection:id>
mysql-js> db.createCollection("name");
<Collection:name>
mysql-js> db.collections;
{
"id": <Collection:id>,
"name": <Collection:name>
}

 

切换回world_x示例库，这里数据多点，接下来在world_x内操作

1
2
3
mysql-js> db = session.getSchema("world_x");
<Schema:world_x>
mysql-js>

db.CountryInfo.add()添加数据，要注意，输入完成后需要回车两次 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql-js> db.CountryInfo.add(
{
    GNP: .6,
    IndepYear: 1967,
    Name: "Sealand",
    _id: "SEA",
    demographics: {
        LifeExpectancy: 79,
        Population: 27
    },
    geography: {
        Continent: "Europe",
        Region: "British Islands",
        SurfaceArea: 193
    },
    government: {
        GovernmentForm: "Monarchy",
        HeadOfState: "Michael Bates"
    }
  }
)
Query OK, 1 item affected (0.02 sec)

db.CountryInfo.find()查看

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql-js> db.CountryInfo.find()
[
     {
          "GNP": 828,
          "IndepYear": null,
          "Name": "Aruba",
          "_id": "ABW",
          "demographics": {
              "LifeExpectancy": 78.4000015258789,
              "Population": 103000
          },
          "geography": {
              "Continent": "North America",
              "Region": "Caribbean",
              "SurfaceArea": 193
          },
          "government": {
              "GovernmentForm": "Nonmetropolitan Territory of The Netherlands",
              "HeadOfState": "Beatrix"
          }
          ...
      }
]
240 documents in set (0.00 sec)

查找符合条件的，引号内还可以使用算术符号等，如db.CountryInfo.find(“GNP*1000000/demographics.Population > 30000”)

1
2
3
mysql-js> db.CountryInfo.find("GNP > 500000")
...[output removed]
10 documents in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql-js> db.CountryInfo.find("Name = :country").bind("country", "Italy")
[
{
"GNP": 1161755,
"IndepYear": 1861,
"Name": "Italy",
"_id": "ITA",
"demographics": {
"LifeExpectancy": 79,
"Population": 57680000
},
"geography": {
"Continent": "Europe",
"Region": "Southern Europe",
"SurfaceArea": 301316
},
"government": {
"GovernmentForm": "Republic",
"HeadOfState": "Carlo Azeglio Ciampi"
}
}
]
1 document in set (0.01 sec)

.bind关联多个字段条件，.fields([“GNP”, “Name”])显示选择字段，.limit(N)返回N行结果，.sort([“IndepYear desc”]) 排序 等，

更多增删改查可以参阅官方文档Documents and Collections，文档还有介绍Relational Tables，接近Mysql中的表操作。

感兴趣的同学可以切换到sql模式，查看下数据库testx下id的表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
mysql-js> \sql
Switching to SQL mode... Commands end with ;
mysql-sql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| testx |
| world_x |
+--------------------+
7 rows in set (0.00 sec)
 
mysql-sql> use testx;
Query OK, 0 rows affected (0.00 sec)
 
mysql-sql> show tables;
+-----------------+
| Tables_in_testx |
+-----------------+
| id |
| name |
+-----------------+
2 rows in set (0.00 sec)
 
mysql-sql> show create table id;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | CREATE TABLE `id` (
`doc` json DEFAULT NULL,
`_id` varchar(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,'$._id'))) STORED NOT NULL,
UNIQUE KEY `_id` (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看到doc是json字段存储，id列索引是利用了5.7新特性虚拟列，为_id创建了一个唯一约束并存储到磁盘，利用的innodb引擎，这就可以让collection支持事物行锁等innodb引擎的特性。X plugin利用独有的协议可以模拟类似mongodb等No-sql的操作，很强大的功能。

欢迎转载，转载请注明出处！Tim » (二) Mysql5.7 原生JSON格式支持 新增X plugin

点赞 (1)or拍砖 (0)
分享到：更多 ()

相关推荐