无论开发人员、运维人员还是 DBA,使用 MySQL 文档存储最简单的方式就是利用 MySQL Shell 工具。MySQL Shell 是为 MySQL Server 管理和开发提供的一个高级客户端和代码编辑器,除了提供类似
mysql
客户端的 SQL 功能之外,还支持 JavaScript 和 Python 接口和脚本。MySQL Shell 支持 X 协议,通过 X DevAPI 可以同时操作关系数据和文档数据。另外,MySQL Shell 还支持使用 AdminAPI 管理 InnoDB 集群。
安装 MySQL Shell
MySQL 官方网站提供了 MySQL Shell 的下载,点击
链接
进入下载页面。选择相应的操作系统版本安装文件即可,Windows 系统也可以通过 MySQL Installer 工具进行安装。
MySQL Shell 支持 Microsoft Windows、Linux 以及 macOS 64 位操作系统。
连接 MySQL 服务器
MySQL Shell 支持两种连接方式:传统连接(默认 3306 端口)和 X 协议连接(默认 33060 端口)。首先,在命令行输入
mysqlsh
:
然后使用
\connect
或者
\c
命令连接到 MySQL 服务器:
MySQL JS> \connect root@192.168.56.104:33060
Creating a session to 'root@192.168.56.104:33060'
Please provide the password for'root@192.168.56.104:33060':*********
Save password for'root@192.168.56.104:33060'?[Y]es/[N]o/Ne[v]er(default No):
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 19(X protocol)
Server version:8.0.18 MySQL Community Server -GPL
No default schema selected; type \use <schema> to set one.
MySQL 192.168.56.104:33060+ ssl JS>
1
2
3
4
5
6
7
8
9
其中,
\connect root@192.168.56.104:33060
表示使用 root 用户连接到主机 192.168.56.104 的 33060 端口上的 MySQL 服务器。此时,MySQL Shell 使用 X 协议会话连接,等价以下命令:
\connect --mysqlx root@192.168.56.104:33060
1
MySQL 8.0 服务器默认安装了 X 插件,可以使用
SHOW plugins
命令查看;X 协议的配置信息可以使用
SHOW variables LIKE '%mysqlx%'
命令查看。
我们也可以使用传统的 MySQL 协议会话方式连接到 MySQL 服务器:
\connect root@192.168.56.104:3306
1
或者:
\connect --mysql root@192.168.56.104:3306
1
通过 session 对象可以查看当前连接使用的协议:
MySQL 192.168.56.104:33060+ ssl JS> session
<Session:root@192.168.56.104:33060>
1
2
如果是传统的 MySQL 协议会话则会显示以下内容:
MySQL 192.168.56.104:3306 ssl JS> session
<ClassicSession:root@192.168.56.104:3306>
1
2
利用\status命令可以查看当前全局会话的状态信息:
MySQL 192.168.56.104:33060+ ssl JS> \status
MySQL Shell version 8.0.18
Session type:X
Connection Id:19
Default schema:
Current schema:
Current user: root@dongx.mycomputer.com
SSL: Cipher in use:DHE-RSA-AES128-GCM-SHA256 TLSv1.2
Using delimiter:;
Server version:8.0.18 MySQL Community Server -GPL
Protocol version:X protocol
Client library:8.0.18
Connection:192.168.56.104 via TCP/IPTCP port:33060
Server characterset: utf8mb4
Schema characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
Uptime:1 day 23 hours 46 min 38.0000 sec
MySQL 192.168.56.104:33060+ ssl JS> \sql
Switching to SQL mode... Commands end with;
MySQL 192.168.56.104:33060+ ssl SQL> \py
Switching to Python mode...
MySQL 192.168.56.104:33060+ ssl Py > \js
Switching to JavaScript mode...
MySQL 192.168.56.104:33060+ ssl JS>
1
2
3
4
5
6
7
从提示符可以知道当前使用的语言,我们首先创建一个新的模式 ds:
MySQL 192.168.56.104:33060+ ssl JS> session.createSchema('ds')<Schema:ds>
MySQL 192.168.56.104:33060+ ssl JS> \use ds
Default schema `ds` accessible through db.
MySQL 192.168.56.104:33060+ ssl ds JS>
MySQL 192.168.56.104:33060+ ssl ds JS> db.countryinfo.add({"GNP":828,"_id":"ABW","Name":"Aruba","IndepYear":null,"geography":{"Region":"Caribbean","Continent":"North America","SurfaceArea":193},"government":{"HeadOfState":"Beatrix","GovernmentForm":"Nonmetropolitan Territory of The Netherlands"},"demographics":{"Population":103000,"LifeExpectancy":78.4000015258789}})
Query OK,1 item affected(0.0262 sec)
MySQL 192.168.56.104:33060+ ssl ds JS> db.countryinfo.add({"GNP":5976,"_id":"AFG","Name":"Afghanistan","IndepYear":1919,"geography":{"Region":"Southern and Central Asia","Continent":"Asia","SurfaceArea":652090},"government":{"HeadOfState":"Mohammad Omar","GovernmentForm":"Islamic Emirate"},"demographics":{"Population":22720000,"LifeExpectancy":45.900001525878906}})
Query OK,1 item affected(0.0087 sec)
1
2
3
4
查找文档
利用集合的 find() 方法可以查找文档:
MySQL 192.168.56.104:33060+ ssl ds JS> db.countryinfo.find(){"GNP":828,"_id":"ABW","Name":"Aruba","IndepYear":null,"geography":{"Region":"Caribbean","Continent":"North America","SurfaceArea":193},"government":{"HeadOfState":"Beatrix","GovernmentForm":"Nonmetropolitan Territory of The Netherlands"},"demographics":{"Population":103000,"LifeExpectancy":78.4000015258789}}{"GNP":5976,"_id":"AFG","Name":"Afghanistan","IndepYear":1919,"geography":{"Region":"Southern and Central Asia","Continent":"Asia","SurfaceArea":652090},"government":{"HeadOfState":"Mohammad Omar","GovernmentForm":"Islamic Emirate"},"demographics":{"Population":22720000,"LifeExpectancy":45.900001525878906}}2 documents inset(0.0011 sec)
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
40
默认返回所有的文档,find() 方法也可以指定查询条件:
MySQL 192.168.56.104:33060+ ssl ds JS> db.countryinfo.find("Name = 'Aruba'"){"GNP":828,"_id":"ABW","Name":"Aruba","IndepYear":null,"geography":{"Region":"Caribbean","Continent":"North America","SurfaceArea":193},"government":{"HeadOfState":"Beatrix","GovernmentForm":"Nonmetropolitan Territory of The Netherlands"},"demographics":{"Population":103000,"LifeExpectancy":78.4000015258789}}1 document inset(0.0014 sec)
MySQL 192.168.56.104:33060+ ssl ds JS> db.t_user.select()+----+-------+--------+| id | name | status |+----+-------+--------+|1| user1 |0||2| user2 |0|+----+-------+--------+2 rows inset(0.0007 sec)
MySQL 192.168.56.104:33060+ ssl ds JS> db.t_user.select(["name","status"]).where("name='user2'")+-------+--------+| name | status |+-------+--------+| user2 |0|+-------+--------+1 row inset(0.0008 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
第一个查询返回了所有数据,第二个查询只返回了 user2 的 name 和 status 字段。与 SQL 功能类似的其他方法包括 limit()、orderBy() 以及 offSet()。
修改记录
我们可以使用 update() 方法更新表中的记录,以下示例将 user2 的状态修改为 1:
MySQL 192.168.56.104:33060+ ssl ds JS> db.t_user.update().set("status",1).where("name = 'user2'")
Query OK,1 item affected(0.0078 sec)
Rows matched:1 Changed:1 Warnings:0
MySQL 192.168.56.104:33060+ ssl ds JS> db.t_user.select(["name","status"]).where("name='user2'")+-------+--------+| name | status |+-------+--------+| user2 |1|+-------+--------+1 row inset(0.0013 sec)
MySQL 192.168.56.104:33060+ ssl ds JS> \sql
Switching to SQL mode... Commands end with;
Fetching table and column names from`ds`for auto-completion... Press ^C to stop.
MySQL 192.168.56.104:33060+ ssl ds SQL>CREATETABLE`city`(->`ID` int NOTNULLAUTO_INCREMENT,->`Name`char(35)NOTNULLDEFAULT'',->`CountryCode`char(3)NOTNULLDEFAULT'',->`District`char(20)NOTNULLDEFAULT'',->`Info` json DEFAULTNULL,->PRIMARYKEY(`ID`)->);
Query OK,0 rows affected(0.0431 sec)
1
2
3
4
5
6
7
8
9
10
11
12
首先,使用
\sql
命令切换到 SQL 模式;然后创建 city 表,其中 Info 字段是 JSON 类型。我们可以像其他数据类型一样使用 SQL 语句操作 JSON 数据:
MySQL 192.168.56.104:33060+ ssl ds SQL> insert into city(name, countrycode, district, info)->values('San Francisco','USA','California','{"Population":830000}');
Query OK,1 row affected(0.0182 sec)
MySQL 192.168.56.104:33060+ ssl ds SQL> select *from city where info->>'$.Population'=830000;+----+---------------+-------------+------------+------------------------+|ID| Name | CountryCode | District | Info |+----+---------------+-------------+------------+------------------------+|1| San Francisco |USA| California |{"Population":830000}|+----+---------------+-------------+------------+------------------------+1 row inset(0.0012 sec)