MySQL BLOB data types

1. Overview

BLOB stands for Binary Large Object that can hold a variable amount of data. It’s used for storing binary data and for the columns of high-capacity binary storage. You can store any file type including PDF documents, MP3 Files and Video Files.

BLOB values are treated as binary strings (byte strings). They have binary character set and collation, sorting and comparison are based on the numeric values of the bytes in column values.

There are four types of BLOB: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. The maximum size of a BLOB object is determined by its type:

TINYBLOB: Maximum length of 255 (2^8 - 1) bytes. Each TINYBLOB value is stored using a one-byte length prefix that indicates the number of bytes in the value.
BLOB: Maximum length of 65535 (2^16 - 1) bytes, or 64KB in storage. Each BLOB value is stored using a two-byte length prefix that indicates the number of bytes in the value.
MEDIUMBLOB: Maximum length of 16777215 (2^24 - 1) bytes, or 16MB in storage. Each MEDIUMBLOB value is stored using a three-byte length prefix that indicates the number of bytes in the value.
LONGBLOB: Maximum length of 4294967295 (2^32 - 1) bytes, or 4GB in storage. Each LONGBLOB value is stored using a four-byte length prefix that indicates the number of bytes in the value.

Some other notes BLOB datatypes:

You do not have to specify the length of any BLOB columns
BLOB columns cannot have DEFAULT value

2. How to use BLOB type

We will use LOAD_FILE(path of file) function to load file content as BLOB content in MySQL. It requires a full path of the file as parameter to read the data from the file.

Let’s demonstrate this via an example.

We create btable with a BLOB column:

CREATE TABLE b_table (
	id bigint (20) PRIMARY KEY,
	file_data blob
);

Now insert values into b_table using the LOAD_FILE() function:

INSERT INTO b_table
		VALUES(1, LOAD_FILE('d:/sample.txt'))

Or insert the HEX string:

INSERT INTO b_table
		VALUES(1, X'848016608498FE15C6050001000006020007002E000005E84501960844CFF15C6050001000006020008002E000405E84D002608498FE15C6050001000006350800020000002E0001000000FF0000002000200005E8440026084C0FD15C60500014002');

Update a BLOB field using LOAD_FILE() function:

UPDATE
	b_table
SET
	file_data = LOAD_FILE('d:/sample.txt')
WHERE
	id = 1;

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.