what data type to store a json array in mysql (5.6) database

mysql json sqldatatypes
Stallion picture Stallion · Nov 10, 2016 · Viewed 20.4k times · Source

What is the data type I should use to store a json encoded array in MySQL version 5.6 where json data type is not available? So far I'm thinking to store it as a TEXT or VARCHAR. Is it how we have to store it?

Answer

Eduardo Yáñez Parareda picture Eduardo Yáñez Parareda · Nov 10, 2016

It depends on the length of the JSON data your are going to store. If it's not too long you can use VARCHAR, but with this you have the limit of 64K:

Manual says: The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

So if you expect to have huge objects, use any of the TEXT types:

TEXT: 65,535 characters - 64 KB 
MEDIUMTEXT: 16,777,215 - 16 MB 
LONGTEXT: 4,294,967,295 characters - 4 GB

Since Mysql 5.7.8 you're able to use the native JSON data type, though.

Related questions

JSON encode MySQL results

How do I use the json_encode() function with MySQL query results? Do I need to iterate through the rows or can I just apply it to the entire results object?

Read More
Can JavaScript connect with MySQL?

Can JavaScript connect with MySQL? If so, how?

Read More
How to test if a string is JSON or not?

I have a simple AJAX call, and the server will return either a JSON string with useful data or an error message string produced by the PHP function mysql_error(). How can I test whether this data is a JSON …

Read More