存储大型JSON-二进制或文本 [英] Storing large JSON - binary or text

问题描述

我阅读了很多文章，并且发现了很多不同的答案.

I have a read a lot of posts and I'm finding a lot of different responses.

运行MYSQL 5.7，我有一个不需要存储的大型JSON对象，只需要存储即可.使用JSON似乎效率很低-我不需要验证它.在这种情况下最好的解决方案是什么? BLOB或TEXT是显而易见的选择，但是两者之中最好的选择是什么?

Running MYSQL 5.7, I have a large JSON object that does not need querying, just storing. Using JSON seems inefficient - I don't need to validate it. What's the best solution in this case? BLOB or TEXT are the obvious choices, but what's the best choice out of the two?

二进制需要转换回文本(您最好如何做到这一点?PHP还是在SQL查询期间?)仅需要返回文本.我非常感谢您的澄清...

Binary needs to be converted back to text (how do you best do this? PHP or during the SQL query?) wilst text just needs to be returned. I'd really appreciate any clarification...

推荐答案

What does the manual say about the JSON type?

优化的存储格式.存储在JSON列中的JSON文档是 转换为允许快速读取访问权限 文档元素.当服务器稍后必须读取存储的JSON值时 采用这种 binary 格式，则无需从文本中解析出该值 表示.二进制格式的结构旨在启用服务器 通过键或数组索引直接查找子对象或嵌套值 而不读取文档中它们之前或之后的所有值.

Optimized storage format. JSON documents stored in JSON columns are converted to an internal format that permits quick read access to document elements. When the server later must read a JSON value stored in this binary format, the value need not be parsed from a text representation. The binary format is structured to enable the server to look up subobjects or nested values directly by key or array index without reading all values before or after them in the document.

强调我的.您显然正在保存此JSON对象，因为您打算在其他时间读取它.如果您在每个查询中都读取它，则将它存储为BLOB或TEXT可能是从数据库中检索它的过程要快几微秒，但是您将花费更多的时间将TEXT对象转换为JSON文档.

Emphasis mine. You are obviously saving this JSON object because you intend to read it at some other time. If you read it in every query, storing it as BLOB or TEXT might been the part where you retrieve it from the database is a few microseconds faster, but you will spend that and more converting the TEXT object to a JSON document.

也不要忘记JSON字段会剥离所有不需要的空格等，因此它比TEXT和BLOB都更紧凑，这可能会抵消这两种类型将提供的任何检索速度.

Also let's not forget that the JSON field strips all unwanted whitespace etc so it will be more compact than both TEXT and BLOB which would possibly negate any retrieval speed ups that those two types will give.

还可以使用JSON使您的系统永不过时.总有一天，您可能需要在JSON字段中搜索特定项目，但是在BLOB字段中有350万条记录，那又会怎样呢?

Also Using JSON makes your system future proof. Someday you may need to search for a particular item in your JSON field as a one of thing but you have 3.5 million records in BLOB field, what then?

这篇关于存储大型JSON-二进制或文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！