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

Question

What is blob and what is text? What are the differences?

When do I need to use blob and when do I need text as data type?

Because for blob and text, there are mediumblob == mediumtext, smallblob == small text. Do they even have the same meaning?

And look at this MEDIUMBLOB, MEDIUMTEXT L + 3 bytes, where L < 224.

What is L?

Solution 1

TEXT and CHAR will convert to/from the character set they have associated with time. BLOB and BINARY simply store bytes.

BLOB is used for storing binary data while Text is used to store large string.

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

TEXT values are treated as nonbinary strings (character strings). They have a character set, and values are sorted and compared based on the collation of the character set.

http://dev.mysql.com/doc/refman/5.0/en/blob.html

Solution 2

Blob datatypes stores binary objects like images while text datatypes stores text objects like articles of webpages

Solution 3

TEXT and CHAR or nchar that will typically be converted to plain text so you can only store text like strings.

BLOB and BINARY which mean you can store binary data such as images simply store bytes.

Solution 4

A BLOB is a binary string to hold a variable amount of data. For the most part BLOB's are used to hold the actual image binary instead of the path and file info. Text is for large amounts of string characters. Normally a blog or news article would constitute to a TEXT field

L in this case is used stating the storage requirement. (Length|Size + 3) as long as it is less than 224.

Reference: http://dev.mysql.com/doc/refman/5.0/en/blob.html

Solution 5

According to High-performance Mysql book:

The only difference between the BLOB and TEXT families is that BLOB types store binary data with no collation or character set, but TEXT types have a character set and collation.

View more solutions

Comments

[nencor]

so, we use text to store long text such as "wordpress post" ? and we use blob to store a very long URL addresses ? so why don't we use varchar to store a very long text instead of using blob or text ? because in memory calculating, varchar is really simple, for example create table website( website_name varchar(30) ) and then we fill the website_name "stackoverflow" so the memory needed is 13byte

[nencor]

what do you mean by store binary data such as image ? did you mean image url ?

[Darcey]

TEXT is going to be replaced with Varchar(MAX) but for now depending on your version of mysql use TEXT for large text posts such as blog body posts etc. BLOB shouldn't be used for storing very long URL addresses or large amounts of text. It's normally used to store images or other binary based objects. Personally I never use BLOB or TEXT and store large amounts of data in xml files relative to user id based folders.

[nencor]

i don't understand binary based object. you mean, we can store photo to database using Blob data type ?? just like folder ? all that i know about binary is only 1 or 0.

[Martin Thoma]

@nencor Yes, you can store images to databases. You can also store archives. But you cannot store a folder to a database, because folders are actually features of the file system. (You could eventually store a complete file system e.g. as an iso file to a database, though)

[CS Lewis]

@martin-thoma We have an application where the Server-Side has a MongoDB database. The Mobile Component of the application uses SQLLite. There needs to be a way to bridge data between the MongoDB Database and the SQLLite Database. MongoDB use ObjectIds to identify data uniquely. Since the SQLLite (RDBMS world ) does Not have any direct way of representing MongoDB ObjectIds, I was wondering if I could store ObjectIds as BLOB types. Does that sound like a reasonably solution? Would it slow performance? Would there be any other Drawbacks?

[hechen0]

BLOB values are treated as binary strings (byte strings). They have the binary character set and collation, and comparison and sorting are based on the numeric values of the bytes in column values. dev.mysql.com/doc/refman/8.0/en/blob.html