使用 MySQL 的 NoSQL 的七大理由，java 基础入门第二版第三章答案

MySQL [localhost+ ssl/docstore] JS> session.rollback()Query OK, 0 rows affected (0.1201 sec)

MySQL [localhost+ ssl/docstore] JS> db.users.find().fields('name')[{"name": "lefred"}]1 document in set (0.0004 sec)

正如您所看到的，是不是在使用上和 mongodb 很像了，而且 MySQL 使用简单的语义支持事务，

CRUD 操作（SQL 不再是强制性的）

通过新的 MySQL 文档存储和 X 协议，我们引入了新的操作来管理集合和/或关系表。 这些操作称为[CRUD] （创建，读取，更新，删除）操作，它们允许您在不编写单行 SQL 的情况下管理数据。

我们当然将 CRUD 操作区分为两组，一组用于集合 ，另一组用于表 ：

您已经在上面的示例中看到了如何使用 find()和 add() ，这个时候不使用 sql 语句也能操作数据了。

无框架

MySQL Document Store 为您带来了新的无模式数据。 实际上，在存储文档时，您不需要事先知道所有属性，如果需要，您可以随时修改文档。 由于您不需要专注于表设计，因此您无需处理规范化，外键和约束甚至数据类型。 这使您可以进行非常快速的初始开发。 由于文档是动态的，因此模式迁移也不再是问题，并且可以忘记大的 ALTER 语句。

例如，让我们使用以下数据：

MySQL [localhost+ ssl/docstore] JS> db.users.find()[{"_id": "00005ad754c90000000000000001","name": "lefred"},{"_id": "00005ad754c90000000000000003","name": "dim0"},{"_id": "00005ad754c90000000000000004","name": "Dave"},{"_id": "00005ad754c90000000000000005","name": "Luis"},{"_id": "00005ad754c90000000000000006","name": "Sunny"}]5 documents in set (0.0007 sec)

现在让我们假设我们想为所有用户添加一个新属性，您可以在不运行 ALTER 语句的情况下执行此操作：

MySQL [localhost+ ssl/docstore] JS> db.users.modify('1').set('department', 'development')Query OK, 5 items affected (0.1910 sec)

让我们验证其中一条记录：

MySQL [localhost+ ssl/docstore] JS> db.users.find("name = 'Sunny'")[{"_id": "00005ad754c90000000000000006","department": "development","name": "Sunny"}]1 document in set (0.0005 sec)

因此，具有这种可能性，为开发人员提供了更多的自由来维护他们编写文档的方式，并且他们不依赖于需要运行大型 alter 语句的运营团队

文档可以提供数据完整性

即使无模式非常重要，有时人们也希望强制数据完整性。 使用 MySQL 文档存储，也可以为文档创建和维护约束和外键。

这是一个我们有两个集合的例子： 用户和部门 ，我们创建了一个 GENERATED STORED 列作为外键：

MySQL [localhost+ ssl/docstore] SQL> alter table departments add column dept varchar(20) generated always as (doc->>"$.name") STORED ;Query OK, 2 rows affected (0.3633 sec)

MySQL [localhost+ ssl/docstore] SQL> alter table users add column dept varchar(20) generated always as (doc->>"$.department") STORED ;Query OK, 5 rows affected (0.1302 sec)

MySQL [localhost+ ssl/docstore] SQL> select * from users;+---------------------------------------------------------------------------------------+------------------------------+-------------+| doc | _id | dept |+---------------------------------------------------------------------------------------+------------------------------+-------------+| {"_id": "00005ad754c90000000000000001", "name": "lefred", "department": "community"} | 00005ad754c90000000000000001 | community || {"_id": "00005ad754c90000000000000003", "name": "dim0", "department": "community"} | 00005ad754c90000000000000003 | community |

【一线大厂Java面试题解析+核心总结学习笔记+最新架构讲解视频+实战项目源码讲义】
浏览器打开：qq.cn.hn/FTf 免费领取

| {"_id": "00005ad754c90000000000000004", "name": "Dave", "department": "community"} | 00005ad754c90000000000000004 | community || {"_id": "00005ad754c90000000000000005", "name": "Luis", "department": "development"} | 00005ad754c90000000000000005 | development || {"_id": "00005ad754c90000000000000006", "name": "Sunny", "department": "development"} | 00005ad754c90000000000000006 | development |+---------------------------------------------------------------------------------------+------------------------------+-------------+5 rows in set (0.0010 sec)

MySQL [localhost+ ssl/docstore] SQL> select * from departments;+------------------------------------------------------------------------------------+------------------------------+-------------+| doc | _id | dept |+------------------------------------------------------------------------------------+------------------------------+-------------+| {"_id": "00005ad754c90000000000000007", "name": "development", "manager": "Tomas"} | 00005ad754c90000000000000007 | development || {"_id": "00005ad754c90000000000000008", "name": "community", "manager": "Andrew"} | 00005ad754c90000000000000008 | community |+------------------------------------------------------------------------------------+------------------------------+-------------+2 rows in set (0.0004 sec)

让我们在这些新列上添加一些索引：

MySQL [localhost+ ssl/docstore] SQL> alter table users add index dept_idx(dept);Query OK, 0 rows affected (0.0537 sec)

MySQL [localhost+ ssl/docstore] SQL> alter table departments add index dept_idx(dept);Query OK, 0 rows affected (0.1278 sec)

现在，让我们创建约束，我希望如果我删除部门，那些部门的所有用户都会被删除：

MySQL [localhost+ ssl/docstore] SQL> alter table users add foreign key (dept) references departments(dept) on delete cascade;Query OK, 5 rows affected (0.2401 sec)

MySQL [localhost+ ssl/docstore] SQL> delete from departments where doc->>"$.manager" like 'Andrew';Query OK, 1 row affected (0.1301 sec)

MySQL [localhost+ ssl/docstore] SQL> select * from departments;+------------------------------------------------------------------------------------+------------------------------+-------------+| doc | _id | dept |+------------------------------------------------------------------------------------+------------------------------+-------------+| {"_id": "00005ad754c90000000000000007", "name": "development", "manager": "Tomas"} | 00005ad754c90000000000000007 | development |+------------------------------------------------------------------------------------+------------------------------+-------------+1 row in set (0.0007 sec)

MySQL [localhost+ ssl/docstore] SQL> select * from users;+---------------------------------------------------------------------------------------+------------------------------+-------------+| doc | _id | dept |+---------------------------------------------------------------------------------------+------------------------------+-------------+| {"_id": "00005ad754c90000000000000005", "name": "Luis", "department": "development"} | 00005ad754c90000000000000005 | development || {"_id": "00005ad754c90000000000000006", "name": "Sunny", "department": "development"} | 00005ad754c90000000000000006 | development |+---------------------------------------------------------------------------------------+------------------------------+-------------+2 rows in set (0.0006 sec)

正如您所看到的，实现外键约束以增强数据完整性是可能且非常容易的。

允许 SQL（对分析非常重要）

正如您在前面的观点中看到的那样，可以将 SQL 和 NoSQL 混合使用，事实上，我认为前端的开发人员只会使用 NoSQL 来创建和使用看作对象的数据，而在后台工作更多的人将继续使用 SQL 来创建报表和分析。

为了说明使用 Documents 的 MySQL 的强大功能，我将使用另一种流行的 NoSQL 解决方案中受欢迎的餐厅示例集合。

这是存储在集合中的文档示例：

MySQL [localhost+ ssl/docstore] JS> db.restaurants.find().limit(1)[{"_id": "5ad5b645f88c5bb8fe3fd337","address": {"building": "1007","coord": [-73.856077,40.848447],"street": "Morris Park Ave","zipcode": "10462"},"borough": "Bronx","cuisine": "Bakery","grades": [{"date": "2014-03-03T00:00:00Z","grade": "A","score": 2},{"date": "2013-09-11T00:00:00Z","grade": "A","score": 6},{"date": "2013-01-24T00:00:00Z","grade": "A","score": 10},{"date": "2011-11-23T00:00:00Z","grade": "A","score": 9},{"date": "2011-03-10T00:00:00Z","grade": "B","score": 14}],"name": "Morris Park Bake Shop","restaurant_id": "30075445"}]

可以看到餐厅可以评级，当然还有一种美食风格。

我们可以尝试找到结果的一个简单查询是：每家餐厅的平均成绩是多少？ （并将结果限制为 10）

MySQL [localhost+ ssl/docstore] SQL> SELECT name, cuisine, avg(rating) FROM restaurants,-> JSON_TABLE(doc, "$"columns(namevarchar(100)path"$.name",-> cuisine varchar(100) path "$.cuisine",->nestedpath"$.grades[*]"-> columns (rating int path "$.score")))-> AS jt GROUP BY name, cuisine LIMIT 10;+--------------------------------+---------------------------------+-------------+| name | cuisine | avg(rating) |+--------------------------------+---------------------------------+-------------+| Morris Park Bake Shop | Bakery | 8.2000 || Wendy'S | Hamburgers | 9.4404 || Dj Reynolds Pub And Restaurant | Irish | 9.2500 || Riviera Caterer | American | 9.0000 || Tov Kosher Kitchen | Jewish/Kosher | 17.7500 || Brunos On The Boulevard | American | 17.0000 || Kosher Island | Jewish/Kosher | 10.5000 || Wilken'S Fine Food | Delicatessen | 10.0000 || Regina Caterers | American | 9.6000 || Taste The Tropics Ice Cream | Ice Cream, Gelato, Yogurt, Ices | 8.2500 |+--------------------------------+---------------------------------+-------------+10 rows in set, 13 warnings (1.5114 sec)