Understanding MySQL Large Object Types: TEXT/BLOB and JSON

MySQL provides two major categories of large object (LOB) types: the traditional TEXT / BLOB family and the newer JSON type. The article first compares TEXT and BLOB, noting that TEXT stores data as readable characters with a collation, while BLOB stores raw binary data without a character set.

Example 1 – Creating and querying a table with TEXT and BLOB columns

mysql> create table c1 (f1 tinytext, f2 tinyblob);
Query OK, 0 rows affected (0.03 sec);

insert into c1 values ('a','a'),('b','b'),('B','B'),('d','d'),('F','F'),('你','你'),('我','我'),('是吧','是吧');

select * from c1 order by f1;
+--------+--------+
| f1     | f2     |
+--------+--------+
| a      | a      |
| b      | b      |
| B      | B      |
| d      | d      |
| F      | F      |
| 你     | 你     |
| 我     | 我     |
| 是吧   | 是吧   |
+--------+--------+

select * from c1 order by f2;
+--------+--------+
| f1     | f2     |
+--------+--------+
| B      | B      |
| F      | F      |
| a      | a      |
| b      | b      |
| d      | d      |
| 你     | 你     |
| 我     | 我     |
| 是吧   | 是吧   |
+--------+--------+

The ordering results differ because f1 (TEXT) uses a case‑insensitive collation, while f2 (BLOB) compares binary values.

Storage formats

redundant/compact : the first 768 bytes of a LOB are stored in the InnoDB data page; the remainder goes to overflow pages, which can bloat pages when many LOB columns exist.

dynamic/compressed : if the LOB size is ≤ 40 bytes, it stays entirely in the data page; otherwise only a 20‑byte pointer is kept, making small LOBs behave like VARCHAR(40) .

Because of these storage characteristics, large objects are often stored as file paths with the actual files on disk, especially in replication scenarios.

Engine and feature restrictions

The NDB engine does not recommend LOBs; they cannot be used in join push‑down, require extra locking, etc.

Indexes on LOB columns must use a prefix, e.g., ALTER TABLE t2 ADD KEY idx_f100(f100(10)); – attempting a full‑column index yields ERROR 1170 .

Partitioning columns cannot be LOBs; creating a partitioned table with a TEXT column results in ERROR 1502 .

The server variable mysql_allowed_packet should be increased (up to 1 GB) when transmitting large TEXT/BLOB values.

Inserting file contents into a LOB column

create table file_list_upload_dir(
  file_name varchar(100) not null primary key,
  content longtext,
  updated datetime
);

-- Load each file from /var/lib/mysql-files into the table
for i in $(ls); do
  mysql -uytt -pytt -P3305 -h127.0.0.1 \
    -e "use ytt; insert into file_list_upload_dir (file_name, content, updated) \
        values ('$i', load_file('$(pwd)/$i'), now());"
  done;

JSON type

MySQL stores JSON values in a binary format similar to PostgreSQL's JSONB . The storage size is comparable to LONGTEXT or LONGBLOB . While TEXT can hold JSON text, it lacks validation and built‑in functions.

Example 3 – Working with JSON

set @a='{"a":1,"b":2,"c":3,"d":4}';
set @b="{'a':1,'b':2,'c':3,'d':4}";  -- invalid JSON
create table json1 (str1 json, str2 longtext);
insert into json1 values (@a,@a);   -- succeeds
insert into json1 values (@b,@b);   -- fails with ERROR 3140
insert into json1 values (@a,@b);   -- succeeds (str2 accepts any text)

select json_extract(@a,'$.a') as a;
+------+
| a    |
+------+
| 1    |
+------+

JSON functions such as json_extract make querying specific fields straightforward, unlike raw text where parsing is required.

In summary, the article provides a practical guide to choosing between TEXT/BLOB and JSON for large data in MySQL, considering storage format, indexing, partitioning, and server configuration.