MYSQL 5.7 中的原生 JSON 支持：MYSQL 中 JSON 数据类型的优点和缺点是什么？

MySQL 5.7 中用于存储的新数据类型MySQL 中的 JSON 数据 http://dev.mysql.com/doc/refman/5.7/en/json.html表已 添加。显然这对MySQL来说是一个巨大的改变。他们列出了一些好处

文件验证- 只有有效的 JSON 文档才能存储在 JSON 列，以便您自动验证数据。

高效接入- 更重要的是，当您将 JSON 文档存储在 JSON 列中时，它不会存储为纯文本值。相反，它被存储 采用优化的二进制格式，可以更快地访问对象 成员和数组元素。

表现- 改进您的查询 通过对 JSON 列中的值创建索引来提高性能。 这可以通过虚拟列上的“功能索引”来实现。

方便- JSON 列的附加内联语法使其 将文档查询集成到 SQL 中是非常自然的。为了 示例（features.feature 是 JSON 列）：SELECT feature->"$.properties.STREET" AS property_street FROM features WHERE id = 121254;

哇 ！它们包含一些很棒的功能。现在操作数据变得更加容易。现在可以在列中存储更复杂的数据。 所以 MySQL 现在加入了 NoSQL。

现在我可以想象对 JSON 数据的查询类似于

SELECT * FROM t1
WHERE JSON_EXTRACT(data,"$.series") IN 
( 
SELECT JSON_EXTRACT(data,"$.inverted") 
FROM t1 | {"series": 3, "inverted": 8} 
WHERE JSON_EXTRACT(data,"$.inverted")<4 );

那么我可以在几个 json 列中存储巨大的小关系吗？好吗？它是否打破了常态化。如果这是可能的，那么我想它会像 MySQL 列中的 NoSQL 一样。我真的很想了解更多有关此功能的信息。 MySQL JSON 数据类型的优缺点。

SELECT * FROM t1
WHERE JSON_EXTRACT(data,"$.series") IN ...

在这样的表达式或函数中使用列会破坏查询使用索引来帮助优化查询的任何机会。上面显示的查询被迫进行表扫描。

关于“高效访问”的说法具有误导性。这意味着在查询检查包含 JSON 文档的行后，它可以提取字段，而无需解析 JSON 语法的文本。但仍然需要表扫描来搜索行。换句话说，查询必须检查每一行。

打个比方，如果我在电话簿中搜索名字为“Bill”的人，我仍然需要阅读电话簿中的每一页，即使名字已突出显示，以便更快地找到他们。

MySQL 5.7允许您在表中定义虚拟列，然后在虚拟列上创建索引。

ALTER TABLE t1
  ADD COLUMN series AS (JSON_EXTRACT(data, '$.series')),
  ADD INDEX (series);

然后，如果您查询虚拟列，它可以使用索引并避免表扫描。

SELECT * FROM t1
WHERE series IN ...

这很好，但它有点忽略了使用 JSON 的意义。使用 JSON 的吸引力在于它允许您添加新属性而无需执行 ALTER TABLE。但事实证明，如果您想借助索引搜索 JSON 字段，则无论如何都必须定义一个额外的（虚拟）列。

但您不必为其定义虚拟列和索引everyJSON 文档中的字段 - 仅那些您想要搜索或排序的字段。 JSON 中可能还有其他属性，您只需在选择列表中提取这些属性，如下所示：

SELECT JSON_EXTRACT(data, '$.series') AS series FROM t1
WHERE <other conditions>

我通常会说这是在 MySQL 中使用 JSON 的最佳方式。仅在选择列表中。

当您引用其他子句（JOIN、WHERE、GROUP BY、HAVING、ORDER BY）中的列时，使用常规列（而不是 JSON 文档中的字段）会更有效。

我做了一个演讲，叫做如何在 MySQL 中错误地使用 JSON https://www.slideshare.net/billkarwin/how-to-use-json-in-mysql-wrong在 2018 年 4 月的 Percona Live 会议上。我将在秋季更新并重复在 Oracle Code One 上的演讲。

JSON 还有其他问题。例如，在我的测试中，与存储相同数据的传统列相比，JSON 文档需要的存储空间是传统列的 2-3 倍。

MySQL 正在积极推广其新的 JSON 功能，主要是为了阻止人们迁移到 MongoDB。但像 MongoDB 这样的面向文档的数据存储从根本上来说是一种非关系型数据组织方式。它与关系型不同。我并不是说一个比另一个更好，它只是一种不同的技术，适合不同类型的查询。

当 JSON 使您的查询更加高效时，您应该选择使用 JSON。

不要仅仅因为一项技术新颖或为了时尚而选择它。

编辑：如果您的 WHERE 子句使用与虚拟列定义完全相同的表达式，则 MySQL 中的虚拟列实现应该使用索引。也就是说，下面的should使用虚拟列上的索引，因为虚拟列已定义AS (JSON_EXTRACT(data,"$.series"))

SELECT * FROM t1
WHERE JSON_EXTRACT(data,"$.series") IN ...

但我通过测试此功能发现，如果表达式是 JSON 提取函数，则由于某种原因它不起作用。它适用于其他类型的表达式，但不适用于 JSON 函数。更新：据报道，这最终在 MySQL 5.7.33 中有效。