Should I use blob or text for JSON in MySQL?

mysql text blob mysql-json
Leandro Garcia picture Leandro Garcia · Apr 18, 2012 · Viewed 44.7k times · Source

I am planning to store a json_encoded string 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 blob or 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.

Answer

cs04iz1 picture cs04iz1 · Dec 28, 2015

As stated in the documentation of MySQL, since 5.7.8 a native JSON data type is supported.

The JSON data type provides these advantages over storing JSON-format strings in a string column:

  • 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.

So, as the MySQL documentation states, the JSON data type should be used and not the text.

Related questions

What are the differences between the BLOB and TEXT datatypes in MySQL?

What is blob and what is text? What are the differences? When do I need to use blob and when do I need text as data type? Because for blob and text, there are mediumblob == mediumtext, smallblob == small text. Do …

Read More
Storing base64 encoded data as BLOB or TEXT datatype

We have a MySQL InnoDB table holding ~10 columns of small base64 encoded javascript files and png (<2KB size) images base64 encoded as well. There are few inserts and a lot of reads comparatively, however the output is being cached …

Read More
MySQL: Large VARCHAR vs. TEXT?

I've got a messages table in MySQL which records messages between users. Apart from the typical ids and message types (all integer types) I need to save the actual message text as either VARCHAR or TEXT. I'm setting a front-end …

Read More