我家小花儿

这个屌丝很懒，什么也没留下！

热门标签

原生sql_SQL + NoSQL = MySQL，MySQL 文档存储攻略

作者：我家小花儿 | 2024-04-22 00:18:10

踩

sql+nosql=mysql

一方面，关系型数据库可以通过规范化技术（Normalization）和完整性约束（主键约束、唯一约束、检查约束以及外键约束等）保证数据的完整性和一致性；同时还能够提供数据库事务的 ACID 支持和强大的 SQL 查询语言。

另一方面，NoSQL 文档数据库（例如 MongoDB）提供了便于快速开发和维护的灵活模式（schema free），同时可扩展性更好。

一直以来，这两种类型的数据库都被认为具有各自的使用场景。例如，许多大型互联网公司都会使用多种数据库组合的方式；既采用关系数据库（MySQL 等）存储注册账户、交易订单等信息，同时使用文档数据库（MongoDB 等）存储商品、评论等信息。

随着 MySQL 5.7 开始支持原生的 JSON 数据类型、JSON 函数和 SQL 查询接口，最新的 MySQL 8.0 文档存储支持使用 NoSQL 方式进行集合的 CRUD 操作。现在，我们完全可以在 MySQL 中实现 NoSQL 文档数据库的功能；而且可以同时获得两者的优点，鱼和熊掌可以兼得！

MySQL 文档存储体系结构

MySQL 的文档存储体系结构如下图所示：

MySQL 文档存储体系结构包括了以下组件：

原生 JSON 文档存储。MySQL 提供了原生的 JSON 数据类型，使用高效的二进制存储格式，同时能够创建虚拟列并进行索引。JSON 文档支持自动的格式验证。
X 插件。X 插件使得 MySQL 可以支持 X 协议，支持使用应用程序驱动和 MySQL Shell 工具作为客户端连接到服务器。
X 协议。X 协议是一种基于 Protobuf 格式的新型客户端协议，可以同时支持 CRUD 和 SQL 操作。
X DevAPI 。X DevAPI 是一种基于 X 协议之上新型现代化的异步 API，用于 CRUD 和 SQL 操作。它引入了一种新的模式对象：集合（Collection）。文档存储在集合中（类似于行存储在表中），并且提供了专用的 CRUD 操作命令集。
MySQL Shell。MySQL Shell 是一个交互式的 Javascript、Python 以及 SQL 命令接口，支持 MySQL Server 的开发与管理。用户可以使用 MySQL Shell 执行数据查询和更新，也可以执行各种管理操作。
MySQL 驱动。以下 MySQL 连接器都支持 X 协议，可以在应用程序中使用 X DevAPI 操作 JSON 文档：
- MySQL Connector/Node.js
- MySQL Connector/PHP
- MySQL Connector/Python
- MySQL Connector/J
- MySQL Connector/NET
- MySQL Connector/C++

使用 MySQL Shell 操作文档

无论开发人员、运维人员还是 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 >

其中，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

MySQL 8.0 服务器默认安装了 X 插件，可以使用 SHOW plugins命令查看；X 协议的配置信息可以使用 SHOW variables LIKE '%mysqlx%'命令查看。

我们也可以使用传统的 MySQL 协议会话方式连接到 MySQL 服务器：

connect root@192.168.56.104:3306

或者：

connect --mysql root@192.168.56.104:3306

通过 session 对象可以查看当前连接使用的协议：


MySQL  192.168.56.104:33060+ ssl  JS > session
<Session:root@192.168.56.104:33060>

如果是传统的 MySQL 协议会话则会显示以下内容：


MySQL  192.168.56.104:3306 ssl  JS > session
<ClassicSession:root@192.168.56.104:3306>

利用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/IP
TCP 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 Shell 命令可以参考官方文档。

执行文档 CRUD 操作

MySQL 支持 SQL、JavaScript 以及 Python 三种语言模式，默认为 JavaScript。我们可以分别使用sql、js以及py命令进行切换：


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 >

从提示符可以知道当前使用的语言，我们首先创建一个新的模式 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 >

我们使用 JavaScript 演示文档操作，MySQL Shell 同样支持使用 Python 执行这些命令。

MySQL Shell 支持代码补全功能，输入 db.，然后输入两次 TAB 键：


MySQL  192.168.56.104:33060+ ssl  ds  JS > db.
createCollection()      getCollection()         getName()               getTable()              name
dropCollection()        getCollectionAsTable()  getSchema()             getTables()             schema
existsInDatabase()      getCollections()        getSession()            help()                  session
 MySQL  192.168.56.104:33060+ ssl  ds  JS > db.

创建文档

集合（Collection）相当于关系数据库中的表，文档（Document）相当于表中的记录，在 MySQL 中使用 JSON 对象表示。我们可以使用createCollection()命令创建一个集合对象 countryinfo：


MySQL  192.168.56.104:33060+ ssl  ds  JS > db.createCollection('countryinfo')
<Collection:countryinfo>
 MySQL  192.168.56.104:33060+ ssl  ds  JS > db.getCollections()
[
    <Collection:countryinfo>
]

示例数据来自 MySQL 官方提供的 world_x 数据库。

然后使用集合的 add() 方法新增一个文档：


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)

查找文档

利用集合的 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 in set (0.0011 sec)

默认返回所有的文档，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 in set (0.0014 sec)

以上命令只返回名称为“Aruba”的国家。

find() 方法还支持类似 SQL 的其他操作，例如 limit()、sort() 以及 skip() 等，具体可以参考 X DevAPI 用户指南。

修改文档

利用 modify() 方法可以修改集合中的文档：


MySQL  192.168.56.104:33060+ ssl  ds  JS > db.countryinfo.modify("_id = 'ABW'").set("demographics", {LifeExpectancy: 78.5, Population: 104000})
Query OK, 1 item affected (0.0079 sec)
 
Rows matched: 1  Changed: 1  Warnings: 0
 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": 104000,
        "LifeExpectancy": 78.5
    }
}
1 document in set (0.0013 sec)

首先， modify() 通过 _id 查找文档，不指定搜索条件会修改所有文档；然后使用 set() 方法修改 demographics 对象中的人口和预期寿命；最后再次查询该文档确认。

利用 modify() 和 unset() 可以删除文档中的字段，以下示例删除了“Aruba”的“GNP”信息：


MySQL  192.168.56.104:33060+ ssl  ds  JS > db.countryinfo.modify("Name = 'Aruba'").unset("GNP")
Query OK, 1 item affected (0.0068 sec)
 
Rows matched: 1  Changed: 1  Warnings: 0
 MySQL  192.168.56.104:33060+ ssl  ds  JS > db.countryinfo.find("Name = 'Aruba'")
{
    "_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": 104000,
        "LifeExpectancy": 78.5
    }
}
1 document in set (0.0013 sec)

modify() 方法还可以用于追加、插入、删除文档中的数组等，具体可以参考 X DevAPI 用户指南。

删除文档

如果想要删除一个文档，可以使用集合的 remove() 方法：


MySQL  192.168.56.104:33060+ ssl  ds  JS > db.countryinfo.remove("_id = 'ABW'")
Query OK, 1 item affected (0.0205 sec)
 MySQL  192.168.56.104:33060+ ssl  ds  JS > db.countryinfo.remove("true")
Query OK, 1 item affected (0.0075 sec)
 MySQL  192.168.56.104:33060+ ssl  ds  JS > db.countryinfo.find()
Empty set (0.0011 sec)

第一个命令删除了指定 _id 的文档，第二个命令删除了集合 countryinfo 中所有的文档。

最后，我们可以使用 dropCollection() 方法删除集合：


MySQL  192.168.56.104:33060+ ssl  ds  JS > db.dropCollection("countryinfo")
 MySQL  192.168.56.104:33060+ ssl  ds  JS > db.getCollections()
[]

MySQL 文档存储支持基于文档中的字段创建索引，从而提高查询性能；具体参考 X DevAPI 用户指南。 X DevAPI 支持多文档事务、锁定操作以及预编译语句，具体参考 X DevAPI 用户指南。

使用 MySQL Shell 操作关系表

在 MySQL 中，X DevAPI 也可以通过类似于文档的 CRUD 函数操作传统的关系表，相当于将关系表看作文档集合进行操作。我们首先创建一个关系表：


MySQL  192.168.56.104:33060+ ssl  ds  JS > sql create table t_user(id int primary key, name varchar(50), status tinyint default 0);
Query OK, 0 rows affected (0.6562 sec)

MySQL Shell 支持在 JavaScript 和 Python 模式中直接执行 SQL 语句，上面的命令创建了一个 t_user 表。

MySQL Shell 也支持使用传统的 SQL 语句操作关系表，使用单独的 sql命令可以切换到 SQL 命令模式。

使用 getTables() 函数查看当前数据库中的表：


MySQL  192.168.56.104:33060+ ssl  ds  JS > db.getTables()
[
    <Table:t_user>
]

插入记录

使用 insert() 和 values() 方法插入一个新的记录：


MySQL  192.168.56.104:33060+ ssl  ds  JS > db.t_user.insert("id", "name").values(1, "user1").values(2, "user2")
Query OK, 2 items affected (0.1343 sec)
 
Records: 2  Duplicates: 0  Warnings: 0

其中，insert() 方法用于指定字段名，values() 方法用于指定字段的值。以上示例插入了两条记录。

查询记录

select() 方法可以用于查询表，where() 方法用于指定查询条件：


MySQL  192.168.56.104:33060+ ssl  ds  JS > db.t_user.select()
+----+-------+--------+
| id | name  | status |
+----+-------+--------+
|  1 | user1 |      0 |
|  2 | user2 |      0 |
+----+-------+--------+
2 rows in set (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 in set (0.0008 sec)

第一个查询返回了所有数据，第二个查询只返回了 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 in set (0.0013 sec)

其中，set() 方法用于设置字段的值； where() 方法用于指定需要更新的记录。

删除记录

使用 delete() 方法删除表中的记录：


MySQL  192.168.56.104:33060+ ssl  ds  JS > db.t_user.delete().where("name='user2'")
Query OK, 1 item affected (0.0074 sec)
 MySQL  192.168.56.104:33060+ ssl  ds  JS > db.t_user.delete()
Query OK, 1 item affected (0.0190 sec)
 MySQL  192.168.56.104:33060+ ssl  ds  JS > db.t_user.select()
Empty set (0.0010 sec)

其中，where() 方法用于选择要删除的记录；不指定删除条件的 delete() 将会删除表中所有的数据。

最后，我们可以像删除集合一样使用 dropCollection() 方法删除关系表：


MySQL  192.168.56.104:33060+ ssl  ds  JS > db.dropCollection("t_user")
 MySQL  192.168.56.104:33060+ ssl  ds  JS > db.getCollections()
[]

更多关于表的 CRUD 函数信息，可以参考X DevAPI 用户指南。

使用 SQL 操作 JSON 文档

X DevAPI 可以同时支持文档的 CRUD 操作和关系表的 SQL CRUD 操作，传统的 SQL 查询接口也是如此。自从 MySQL 5.7 开始引入了原生的 JSON 数据类型，我们可以在关系表中创建 JSON 字段；同时在 SQL 语句中利用 JSON 函数操作文档数据。

首先创建一个包含 JSON 字段的关系表：


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 > CREATE TABLE `city` (
                                          ->   `ID` int NOT NULL AUTO_INCREMENT,
                                          ->   `Name` char(35) NOT NULL DEFAULT '',
                                          ->   `CountryCode` char(3) NOT NULL DEFAULT '',
                                          ->   `District` char(20) NOT NULL DEFAULT '',
                                          ->   `Info` json DEFAULT NULL,
                                          ->   PRIMARY KEY (`ID`)
                                          -> );
Query OK, 0 rows affected (0.0431 sec)

首先，使用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 in set (0.0012 sec)

其中，->> 使用 JSON/PATH 表达式查找 JSON 文档中的字段信息，等价于 JSON_UNQUOTE(JSON_EXTRACT()) 函数。

更多信息可以参考官方文档中的 JSON 数据类型和 JSON 函数。

实际上，MySQL 文档存储中的集合也是一个关系表：


MySQL  192.168.56.104:33060+ ssl  ds  SQL > show create table countryinfoG
*************************** 1. row ***************************
       Table: countryinfo
Create Table: CREATE TABLE `countryinfo` (
  `doc` json DEFAULT NULL,
  `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
  PRIMARY KEY (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0013 sec)

使用 X DevAPI 开发应用程序

与 MySQL Shell 类似，MySQL 8.0 驱动程序也可以通过 X 协议连接到 MySQL 服务器，从而利用 X DevAPI 接口开发应用程序。具体可以参考以下驱动文档：

MySQL Connector/Node.js 参考文档；
MySQL Connector/PHP 参考文档；
MySQL Connector/Python 参考文档；
MySQL Connector/J 参考文档；
MySQL Connector/NET 参考文档；
MySQL Connector/C++ 参考文档；

关于 X DevAPI，可以参考官方 X DevAPI 用户指南。

总结

MySQL文档存储使其可以同时支持传统的 SQL 应用程序和模式自由的文档数据库应用程序，从而消除了对单独 NoSQL 文档数据库的需求。开发人员可以在同一数据库和同一应用程序中混合使用关系数据和 JSON 文档。

欢迎关注❤️、点赞、转发！更多数据库领域优质文章，欢迎来我的博客！

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家小花儿/article/detail/465867