Should I use blob or text for JSON in MySQL? Should I use blob or text for JSON in MySQL? mysql mysql

Should I use blob or text for JSON in MySQL?


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.


blob is usually for things like images, binaries etc. text should be good enough for your case, or you can use longtext which has even bigger space capacity if that's really a concern.

Searching-wise, since you are storing json_encode'd stuff, you'll still need to call json_decode on it anyway for it to be useful in your application, I don't think choice of datatype matters in this case.

A better way is to normalize your database design instead of storing related stuff in one big string of json.