MYSQL中的本地JSON支持5.7：MYSQL中JSON数据类型的优缺点是什么？ [英] Native JSON support in MYSQL 5.7 : what are the pros and cons of JSON data type in MYSQL?

问题描述

在MySQL 5.7中，一种新的数据类型，用于在MySQL中存储 JSON数据表已添加
。这显然是MySQL的一个巨大的变化。他们列出了一些优点

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

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

效果 - 通过对JSON列中的值创建索引，提高查询
的性能。
这可以通过虚拟列上的功能索引实现。

方便 - JSON列的附加内联语法
非常自然地在您的SQL中集成文档查询。对于
示例（features.feature是一个JSON列）： SELECT feature->$。properties.STREETAS property_street FROM features WHERE id = 121254;

哇！它们包括一些伟大的功能。现在更容易操作数据。现在可以在列中存储更复杂的数据。
现在我可以想象一个JSON数据的查询，例如

  SELECT * FROM t1 
 WHERE JSON_EXTRACT（data，$。series）IN 
（
 SELECT JSON_EXTRACT（data，$。 ）
 FROM t1 | {series：3，inverted：8} 
 WHERE JSON_EXTRACT（data，$ inverted）< 4）; 
  

所以我从来没有想过MySQL中的复杂模式结构和外键。我只使用几个表来存储复杂关系。好吗？它是否破坏规范化。 如果这是可能的，那么我想它将作为NoSQL在MySQL列。我真的想知道更多关于这个功能。 MySQL JSON数据类型的优点和缺点。

解决方案

以下从对我来说听起来不错：

在MySQL中使用JSON数据类型比在文本字段中存储JSON字符串的
有两个优点：

数据验证。 JSON文档将自动验证，
无效文档将产生错误。改进的内部存储
格式。 JSON数据被转换为允许快速读取
以结构化格式访问数据的格式。服务器能够通过键或索引
查找子对象或嵌套值，允许添加
的灵活性和性能。

...

的NoSQL存储
（文档DB，键值存储和图形数据库）可能更好的
选项用于其特定的用例，但是添加这个
数据类型可能可以减少复杂性你的技术
堆栈。价格是耦合到MySQL（或兼容）数据库。

请注意有关文档验证的语言因为它是一个重要因素。我想要进行一系列测试，以比较两种方法。这两个是：

1. 具有JSON数据类型的Mysql


2. Mysql不带

网络有浅浅的幻灯片，从现在我所看到的mysql / json /性能的话题。

也许你的帖子可以作为它的枢纽。或者性能是一个后来的想法，不确定，你只是兴奋不创建一大堆表。

In MySQL 5.7 a new data type for storing JSON data in MySQL tables has been added. It will obviously be a great change in MySQL. They listed some benefits

Document Validation - Only valid JSON documents can be stored in a JSON column, so you get automatic validation of your data.

Efficient Access - More importantly, when you store a JSON document in a JSON column, it is not stored as a plain text value. Instead, it is stored in an optimized binary format that allows for quicker access to object members and array elements.

Performance - Improve your query performance by creating indexes on values within the JSON columns. This can be achieved with "functional indexes" on virtual columns.

Convenience - The additional inline syntax for JSON columns makes it very natural to integrate Document queries within your SQL. For example (features.feature is a JSON column): SELECT feature->"$.properties.STREET" AS property_street FROM features WHERE id = 121254;

WOW ! they include some great features. Now it is easier to manipulate data. Now it is possible to store more complex data in column. So MySQL is now flavored with NoSQL.

Now I can imagine a query for JSON data something like

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 );

So I never think of a complex schema structure and foreign keys in MySQL. I store complex relations using only a few tables. Is it good? Does it break normalization. If this is possible then I guess it will act like NoSQL in a MySQL column. I really want to know more about this feature. Pros and cons of MySQL JSON data type.

解决方案

The following from MySQL 5.7 brings sexy back with JSON sounds good to me:

Using the JSON Data Type in MySQL comes with two advantages over storing JSON strings in a text field:

Data validation. JSON documents will be automatically validated and invalid documents will produce an error. Improved internal storage format. The JSON data is converted to a format that allows quick read access to the data in a structured format. The server is able to lookup subobjects or nested values by key or index, allowing added flexibility and performance.

...

Specialised flavours of NoSQL stores (Document DBs, Key-value stores and Graph DBs) are probably better options for their specific use cases, but the addition of this datatype might allow you to reduce complexity of your technology stack. The price is coupling to MySQL (or compatible) databases. But that is a non-issue for many users.

Note the language about document validation as it is an important factor. I guess a battery of tests need to be performed for comparisons of the two approaches. Those two being:

1. Mysql with JSON datatypes
2. Mysql without

The net has but shallow slideshares as of now on the topic of mysql / json / performance from what I am seeing.

Perhaps your post can be a hub for it. Or perhaps performance is an after thought, not sure, and you are just excited to not create a bunch of tables.

这篇关于MYSQL中的本地JSON支持5.7：MYSQL中JSON数据类型的优缺点是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！