我应该在 MySQL 中为 JSON 使用 blob 还是文本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10201957/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Should I use blob or text for JSON in MySQL?
提问by Leandro Garcia
I am planning to store a json_encodedstring on my database. I can't precisely tell the length its going to be, but I'm pretty sure it will be long. My concern is which field type I am going to use for this, is it blobor text?
我打算json_encoded在我的数据库中存储一个字符串。我不能准确地说出它的长度，但我很确定它会很长。我关心的是我将为此使用哪种字段类型，是它blob还是text？
I prefer the one where I can save space as much as possible over fast searching, in any case I have other column where I should just index.
我更喜欢可以通过快速搜索尽可能节省空间的那一列，无论如何我还有其他列应该索引。
采纳答案by Andreas Wong
blobis usually for things like images, binaries etc. textshould be good enough for your case, or you can use longtextwhich has even bigger space capacity if that's really a concern.
blob通常用于图像、二进制文件等内容。text应该足够适合您的情况，或者longtext如果确实需要担心，您可以使用具有更大空间容量的内容。
Searching-wise, since you are storing json_encode'd stuff, you'll still need to call json_decodeon it anyway for it to be useful in your application, I don't think choice of datatype matters in this case.
搜索方面，由于您正在存储json_encode'd 的东西，json_decode无论如何您仍然需要调用它才能在您的应用程序中使用它，我认为在这种情况下选择数据类型并不重要。
A better way is to normalize your database design instead of storing related stuff in one big string of json.
更好的方法是规范您的数据库设计，而不是将相关内容存储在一大串 json 中。
回答by cs04iz1
As stated in the documentation of MySQL, since 5.7.8 a native JSON data type is supported.
正如MySQL文档中所述，从 5.7.8 开始支持原生 JSON 数据类型。
The JSON data type provides these advantages over storing JSON-format strings in a string column:
与将 JSON 格式的字符串存储在字符串列中相比，JSON 数据类型具有以下优势：
- Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error.
- 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 列中的 JSON 文档。无效的文档会产生错误。
- 优化的存储格式。存储在 JSON 列中的 JSON 文档被转换为允许对文档元素进行快速读取访问的内部格式。当服务器稍后必须读取以这种二进制格式存储的 JSON 值时，不需要从文本表示中解析该值。二进制格式的结构使服务器能够直接通过键或数组索引查找子对象或嵌套值，而无需读取文档中它们之前或之后的所有值。
So, as the MySQL documentation states, the JSON data type should be used and not the text.
因此，正如 MySQL 文档所述，应该使用 JSON 数据类型而不是文本。
