Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Max JSON column length in MySQL

Tags:

json

mysql

What's the max number of characters I can store in a JSON column in MySQL? I don't see this mentioned in the MySQL manual.

like image 849
mark Avatar asked Nov 21 '16 00:11

mark


People also ask

What is the size of JSON column in MySQL?

One of the more frequently asked questions about the native JSON data type, is what size can a JSON document be. The short answer is that the maximum size is 1GB.

How long can a JSON string be?

The maximum length of JSON strings. The default is 2097152 characters, which is equivalent to 4 MB of Unicode string data.

What is the drawback of JSON columns in MySQL?

The drawback? If your JSON has multiple fields with the same key, only one of them, the last one, will be retained. The other drawback is that MySQL doesn't support indexing JSON columns, which means that searching through your JSON documents could result in a full table scan.


1 Answers

Here's a demo of what @JorgeLondoño is talking about.

Set the server's max allowed packet size:

mysql> set global max_allowed_packet=1024*1024*1024; 

Exit and open the mysql client again, this time setting the client max packet size to match:

$ mysql --max-allowed-packet=$((1024*1024*1024)) 

Create a test table with a JSON column and fill it with the longest JSON document you can:

mysql> create table test.jtest ( j json );  mysql> insert into test.jtest    set j = concat('[', repeat('"word",', 100000000), '"word"]'); Query OK, 1 row affected (1 min 49.67 sec)  mysql> select length(j) from test.jtest; +-----------+ | length(j) | +-----------+ | 800000008 | +-----------+ 

This shows that I was able to create a single JSON document with 100 million elements, and MySQL stores this in approximately 800MB.

I didn't try a longer document. I assume it maxes out at 1 GB, which is the largest value you can set for max_allowed_packet.

like image 109
Bill Karwin Avatar answered Sep 18 '22 01:09

Bill Karwin