✏️ Explanatory Question

What is the difference between BLOB and TEXT?

👁 8 Views
📘 Detailed Answer
🟢 Easy
← Previous What is the difference between ENUM and SET data types?
💡

Answer with Explanation

18

What is the difference between BLOB and TEXT?

Level: Basic — Tests your knowledge of MySQL's large-object types for storing big data.

Both BLOB and TEXT are designed to store large amounts of data that exceed the limits of CHAR/VARCHAR. The core difference is binary vs character data.

  • BLOB (Binary Large Object): Stores binary data — images, audio, video, PDFs, encrypted content. It has no character set and sorts/compares by byte values.
  • TEXT: Stores large text (character) data — articles, descriptions, comments. It has a character set and sorts/compares based on collation.
Key point: BLOB is case-sensitive in comparisons (binary), while TEXT is case-insensitive by default (uses collation). Choose BLOB for non-text files and TEXT for readable strings.

Size Variants of Each Type

BLOB Type TEXT Type Max Size
TINYBLOB TINYTEXT 255 bytes
BLOB TEXT 65,535 bytes (~64 KB)
MEDIUMBLOB MEDIUMTEXT 16,777,215 bytes (~16 MB)
LONGBLOB LONGTEXT 4,294,967,295 bytes (~4 GB)

Key Differences at a Glance

Feature BLOB TEXT
Data type Binary Character
Character set / collation None Yes
Comparison / sorting Case-sensitive (byte-wise) Case-insensitive (collation)
Best for Images, files, media Articles, descriptions, comments
Best practice: For large files, many teams store the file on disk/cloud storage and keep only the file path in the database — this keeps the table lighter and backups faster.

Quick Example

CREATE TABLE documents (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    title        VARCHAR(200),
    content      TEXT,            -- large readable text
    profile_pic  BLOB,           -- binary image data
    attachment   LONGBLOB        -- large binary file up to 4 GB
);

-- Store readable text
INSERT INTO documents (title, content)
VALUES ('Guide', 'This is a long article body...');

-- TEXT comparison is case-insensitive by default
SELECT * FROM documents WHERE content LIKE '%article%';
Interviewer tip: The one-liner they want — "BLOB stores binary data (images/files) with no character set and case-sensitive comparison, while TEXT stores character data with a collation and case-insensitive comparison."
← Previous What is the difference between ENUM and SET data types?