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

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?

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

Solution 2

You can use text datatype to store JSON data in MySQL Database.

Comments

[Wenuka]

For MySQL before version 5.7, right? Thanks Anyway.

[Rick James]

JSON came with 5.7.8 in Aug 2015.

[Leandro Curioso]

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

[Seafish]

"Have to" is too strong. OP doesn't have to; they can use VARCHAR, TEXT, MEDIUMTEXT, LONGTEXT, or even the BLOB types.