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

Question

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 1

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.