MySQL8文档存储攻略

前言

今天我们来聊一下MySQL当中提供的文档功能，简单来说我们可以把MySQL当作像MongoDB这样的文档数据库来使用。不仅仅如此，MySQL提供文档操作接口和SQL操作接口他们之间是互通的，我们即可以用整个接口来操作文档数据库同时也可以利用文档API来操作SQL。

MySQL文档存储

上图是官网文档介绍MySQL文档存储，我们可以看到左边是SQL关系型的表。右边是NoSQL的JSON文档集合，我们知道在NOSQL中集合就是相当于表的概念，文档就相当于我们表的一行行数据，字段就对应着表的字段，也就是在底层MySQL已经这两种格式了。而在这个存储格式之上它提供两种接口。一种是我们所属性的SQL API，也就是我们平时写的SQL输入，另一种是他提供新的协议叫X Protocol，这个协议即支持我们对文档操作，也可以通过SQL执行文档操作。在这之上就是应用层，比如MySQL的连接驱动有Python，Java，C，等等。同时在MySQL8提供一个客户端工具叫MySQL Shell。

MySQL Shell

我们可以简单理解为它是MySQL命令行客户端增强版。我们可以直接在客户端上写JavaScript，Python，SQL，这些都可以作为客户端的脚本语言进行连接我们MySQL服务器，进行命令行的操作，同时他也支持X Protocal协议的API去来做文档操作。同时它还有InnoDB集群管理功能。接下来我们就会以MySQL Shell这个工具来实战操作一下

环境准备

准备一台虚拟机安装MySQL8.0.35

下载安装

在官网下载。Select Version:选择8.0.35，Select Operating System:选择Linux - Generic，Select OS Version:选择Linux - Generic (glibc 2.17) (x86, 64-bit)。下载mysql-shell-8.0.35-linux-glibc2.17-x86-64bit.tar.gz文件，上传到服务器上解压并安装

[mysql@mysql8 app]$ tar -xf mysql-shell-8.0.35-linux-glibc2.17-x86-64bit.tar.gz
[mysql@mysql8 app]$ mv mysql-shell-8.0.35-linux-glibc2.17-x86-64bit mysql-shell8.0.35
[mysql@mysql8 app]$ cat >> ~/.bash_profile << "EOF"
> export MYSQL_SHELL_HOME=/app/mysql-shell8.0.35
> export PATH=$PATH:$MYSQL_SHELL_HOME/bin
> EOF
[mysql@mysql8 app]$ source ~/.bash_profile
[mysql@mysql8 app]$ which mysqlsh
/app/mysql-shell8.0.35/bin/mysqlsh
[mysql@mysql8 app]$ mysqlsh --version
mysqlsh   Ver 8.0.35 for Linux on x86_64 - for MySQL 8.0.35 (MySQL Community Server (GPL))
[mysql@mysql8 app]$

连接数据库

通过\c来连接数据库，不同端口代表操作不同的API。端口3306就是操作关系类型，端口33060就是操作文档类型

[mysql@mysql8 app]$ mysqlsh
MySQL Shell 8.0.35

Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS > \c root@localhost:3306
Creating a session to 'root@localhost:3306'
Please provide the password for 'root@localhost:3306': ****
Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 17
Server version: 8.0.35 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:3306 ssl  JS > \c root@localhost:33060
Creating a session to 'root@localhost:33060'
Please provide the password for 'root@localhost:33060': ****
Save password for 'root@localhost:33060'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for auto-completion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 18 (X protocol)
Server version: 8.0.35 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:33060+ ssl  JS >

切换脚本语言

我们可以看到>前有个JS单词代表JavaScript(Py代表Python，SQL代表SQL)。我们可以通过\py，\sql，\py切换三种模式

 MySQL  localhost:33060+ ssl  JS > \py
Switching to Python mode...
 MySQL  localhost:33060+ ssl  Py > \sql
Switching to SQL mode... Commands end with ;
Fetching global names for auto-completion... Press ^C to stop.
 MySQL  localhost:33060+ ssl  SQL > \py
Switching to Python mode...
 MySQL  localhost:33060+ ssl  Py >

MySQL文档操作

我们用JavaScript来操作文档数据

#首先我们来创建一个Schema，也就是数据库名字叫nosql
 MySQL  localhost:33060+ ssl  JS > session.createSchema('nosql')
<Schema:nosql>
 MySQL  localhost:33060+ ssl  JS > 
#连接这个Schema，nosql
 MySQL  localhost:33060+ ssl  JS > \use nosql
Default schema `nosql` accessible through db.
 MySQL  localhost:33060+ ssl  nosql  JS >
#创建集合emp，也就是创建一个名叫emp表
 MySQL  localhost:33060+ ssl  nosql  JS > db.createCollection('emp')
<Collection:emp>
 MySQL  localhost:33060+ ssl  nosql  JS >
#查询nosql这个Schema中的所有集合
 MySQL  localhost:33060+ ssl  nosql  JS > db.getCollections()
[
    <Collection:emp>
]
 MySQL  localhost:33060+ ssl  nosql  JS >

#向集合emp里增加一条文档，也就是向emp表里插入一条记录数据
 MySQL  localhost:33060+ ssl  nosql  JS > db.emp.add({"id":1,"name":"ZhangShan","age":36})
Query OK, 1 item affected (0.0127 sec)
 MySQL  localhost:33060+ ssl  nosql  JS >
#查询emp集合里的所以文档，我们会发现它和MongoDB一样创建隐含的_id主键
 MySQL  localhost:33060+ ssl  nosql  JS > db.emp.find()
{
    "id": 1,
    "_id": "0000658825fc0000000000000001",
    "age": 36,
    "name": "ZhangShan"
}
1 document in set (0.0004 sec)
 MySQL  localhost:33060+ ssl  nosql  JS >
#修改文档中的年龄字段
 MySQL  localhost:33060+ ssl  nosql  JS > db.emp.modify("_id='0000658825fc0000000000000001'").set("age",31)
Query OK, 1 item affected (0.0018 sec)

Rows matched: 1  Changed: 1  Warnings: 0
 MySQL  localhost:33060+ ssl  nosql  JS >
#查询修改后的emp集合
 MySQL  localhost:33060+ ssl  nosql  JS > db.emp.find("_id='0000658825fc0000000000000001'")
{
    "id": 1,
    "_id": "0000658825fc0000000000000001",
    "age": 31,
    "name": "ZhangShan"
}
1 document in set (0.0006 sec)
 MySQL  localhost:33060+ ssl  nosql  JS >

文档接口操作关系表

通过上面的例子会发现可以把MySQL当作MongoDB一样操作，不仅如此我们可以把一个普通的关系表也作为文档来操作，下面我们来操作一下

#通过sql来创建一个表t_user
 MySQL  localhost:33060+ ssl  nosql  JS > \sql create table t_user(id int, name varchar(20), age int)
Query OK, 0 rows affected (0.0069 sec)
 MySQL  localhost:33060+ ssl  nosql  JS > 
#查询表，注意这里不是集合
 MySQL  localhost:33060+ ssl  nosql  JS > db.getTables()
[
    <Table:t_user>
]
 MySQL  localhost:33060+ ssl  nosql  JS >
#我们用操作文档的方式来操作这个表，插入一条记录
 MySQL  localhost:33060+ ssl  nosql  JS > db.t_user.insert("id", "name", "age").values(2, "LiSi", 51)
Query OK, 1 item affected (0.0020 sec)
 MySQL  localhost:33060+ ssl  nosql  JS >
#查询这个表的记录
 MySQL  localhost:33060+ ssl  nosql  JS > db.t_user.select().where("id = 2")
+----+------+-----+
| id | name | age |
+----+------+-----+
|  2 | LiSi |  51 |
+----+------+-----+
1 row in set (0.0004 sec)
 MySQL  localhost:33060+ ssl  nosql  JS >

使用SQL来操作文档

反过来我们也可以用SQL接口来操作文档

#使用session.sql查询文档emp
 MySQL  localhost:33060+ ssl  nosql  JS > session.sql("select * from emp")
+----------------------------------------------------------------------------------+------------------------------------------------------------+--------------------+
| doc                                                                              | _id                                                        | _json_schema       |
+----------------------------------------------------------------------------------+------------------------------------------------------------+--------------------+
| {"id": 1, "_id": "0000658825fc0000000000000001", "age": 31, "name": "ZhangShan"} | 0x30303030363538383235666330303030303030303030303030303031 | {"type": "object"} |
+----------------------------------------------------------------------------------+------------------------------------------------------------+--------------------+
1 row in set (0.0005 sec)
 MySQL  localhost:33060+ ssl  nosql  JS >
#切换到SQL模式
 MySQL  localhost:33060+ ssl  nosql  JS > \sql
Switching to SQL mode... Commands end with ;
 MySQL  localhost:33060+ ssl  nosql  SQL > 
#用SQL模式查询之前建的集合emp，其实MySQL就是利用了json数据类型加上一些后面的隐藏约束来帮助我们模拟一个文档数据库的效果
 MySQL  localhost:33060+ ssl  nosql  SQL > show create table emp\G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `doc` json DEFAULT NULL,
  `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
  `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
  PRIMARY KEY (`_id`),
  CONSTRAINT `$val_strict_8531F7C0C119E82E3764B969DA287264F47C36FD` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0011 sec)
 MySQL  localhost:33060+ ssl  nosql  SQL >
#所以那我们SQL模式接口也可以操作json字段查询的
 MySQL  localhost:33060+ ssl  nosql  SQL > select doc->>'$.id', doc->>'$.name', doc->>'$.age' from emp;
+--------------+----------------+---------------+
| doc->>'$.id' | doc->>'$.name' | doc->>'$.age' |
+--------------+----------------+---------------+
| 1            | ZhangShan      | 31            |
+--------------+----------------+---------------+
1 row in set (0.0005 sec)
 MySQL  localhost:33060+ ssl  nosql  SQL >

导入JSON文档

我们先来创建一个json文件

[mysql@mysql8 app]$ cat > test.json << "EOF"
> {"first_name":"Zhang","last_name":"San","age":"57"}
> {"first_name":"Li","last_name":"Si","age":"48"}
> {"first_name":"Wang","last_name":"Wu","age":"23"}
> EOF
[mysql@mysql8 app]$ cat test.json
{"first_name":"Zhang","last_name":"San","age":"57"}
{"first_name":"Li","last_name":"Si","age":"48"}
{"first_name":"Wang","last_name":"Wu","age":"23"}
[mysql@mysql8 app]$

然后通过MySQL Shell将他导入到集合

#用util.importJson将json文件导入
 MySQL  localhost:33060+ ssl  nosql  JS > util.importJson('test.json')
Importing from file "test.json" to collection `nosql`.`test` in MySQL Server at localhost:33060

.. 3.. 3
Processed 150 bytes in 3 documents in 0.0019 sec (3.00 documents/s)
Total successfully imported documents 3 (3.00 documents/s)
 MySQL  localhost:33060+ ssl  nosql  JS >
#查询已经导入的集合
 MySQL  localhost:33060+ ssl  nosql  JS > db.getCollections()
[
    <Collection:emp>,
    <Collection:test>
]
 MySQL  localhost:33060+ ssl  nosql  JS >
#查询新导入test集合的文档
 MySQL  localhost:33060+ ssl  nosql  JS > db.test.find()
{
    "_id": "0000658825fc0000000000000002",
    "age": "57",
    "last_name": "San",
    "first_name": "Zhang"
}
{
    "_id": "0000658825fc0000000000000003",
    "age": "48",
    "last_name": "Si",
    "first_name": "Li"
}
{
    "_id": "0000658825fc0000000000000004",
    "age": "23",
    "last_name": "Wu",
    "first_name": "Wang"
}
3 documents in set (0.0006 sec)
 MySQL  localhost:33060+ ssl  nosql  JS >

总结

无论是开发人员，还是DBA运维人员，都可以使用MySQL Shell对文档进行操作。而且可以不同的语言脚本进行SQL和NoSQL的操作。非常的方便😃