What are the differences between the BLOB and TEXT datatypes in MySQL?

BLOB stands for Binary Large Objects and as its name suggests, it can be used for storing binary data while TEXT is used for storing large number of strings. BLOB can be used to store binary data that means we can store pictures, videos, sounds and programs also.

For example, the following image can be stored into BLOB because the image has binary data.

BLOB values behave like byte string and BLOB does not have a character set. Therefore, comparison and sorting is fully dependent upon numeric values of bytes.

TEXT values behave like non-binary string or character string. TEXT has a character set and the comparison/ sorting fully depends upon the collection of character set.

Creating a table with TEXT data type

mysql> create table TextTableDemo
   -> (
   -> Address TEXT
   -> );
Query OK, 0 rows affected (0.58 sec)

To describe tables with the help of DESC command.

mysql> DESC TextTableDemo;

The following is the output.

+---------+------+------+-----+---------+-------+
| Field   | Type | Null | Key | Default | Extra |
+---------+------+------+-----+---------+-------+
| Address | TEXT | YES  |     | NULL    |       |
+---------+------+------+-----+---------+-------+
1 row in set (0.08 sec)

In the above output, “Type” is telling the data type, which is TEXT.

Creating a table with BLOB type

mysql> create table BlobTableDemo
   -> (
   -> Images BLOB
   -> );
Query OK, 0 rows affected (0.51 sec)

Let us get the description of the table with the help of DESC command.

mysql> desc BlobTableDemo;

The following is the output.

+--------+------+------+-----+---------+-------+
| Field  | Type | Null | Key | Default | Extra |
+--------+------+------+-----+---------+-------+
| Images | BLOB | YES  |     | NULL    |       |
+--------+------+------+-----+---------+-------+
1 row in set (0.04 sec)

In the sample output, “Type” is telling the data type which is BLOB.