Learning Focus

Use this lesson to understand Binary, Var Binary, Blob with practical syntax and examples.

MySQL Binary Data Types: Complete Guide

(BINARY, VARBINARY, BLOB Types)

---

Binary Data Types Overview

Type	Storage	Max Size	Purpose
BINARY	Fixed-length	255 bytes	Small, fixed-length binary data
VARBINARY	Variable-length	65,535 bytes	Variable-length binary data
TINYBLOB	Variable-length	255 bytes	Tiny binary objects
BLOB	Variable-length	64KB	Standard binary objects
MEDIUMBLOB	Variable-length	16MB	Large binary objects
LONGBLOB	Variable-length	4GB	Extremely large binary objects

---

BINARY vs. VARBINARY

BINARY(M)

• Fixed-length byte string (padded with 0x00 bytes if shorter than M).

• Example:

  CREATE TABLE example (
      api_key BINARY(16)  -- Always stores 16 bytes
  );
  

VARBINARY(M)

• Variable-length byte string (stores only needed bytes + 1-2 length bytes).

• Example:

  CREATE TABLE example (
      hash VARBINARY(64)  -- SHA-256 hash (exactly 32 bytes)
  );
  

Comparison

Feature	BINARY	VARBINARY
Length	Fixed	Variable
Padding	Yes	No
Storage	Fixed M bytes	1-2 bytes + data
Use Case	UUIDs, fixed hashes	Variable hashes, short binary data

---

BLOB Types

TINYBLOB

• Up to 255 bytes (for very small files).

• Example:

  thumbnail TINYBLOB  -- Tiny icons or thumbnails
  

BLOB

• Up to 64KB (for images, small PDFs).

• Example:

  contract BLOB  -- Store scanned PDFs
  

MEDIUMBLOB

• Up to 16MB (for high-res images, audio files).

• Example:

  song MEDIUMBLOB  -- MP3 files
  

LONGBLOB

• Up to 4GB (for videos, large backups).

• Example:

  video LONGBLOB  -- Short video clips
  

---

Key Differences from TEXT Types

Feature	BLOB	TEXT
Data	Binary bytes	Character strings
Collation	No	Yes
Indexing	Byte-based	Character-based
Comparison	Case-sensitive	Case-insensitive (default)

---

Practical Examples

Example 1: Storing a File

CREATE TABLE documents (
    id INT PRIMARY KEY,
    file_name VARCHAR(255),
    file_data MEDIUMBLOB  -- PDFs, images
);

Example 2: Binary Hashes

CREATE TABLE users (
    id INT PRIMARY KEY,
    password_hash VARBINARY(64)  -- bcrypt hash
);

Example 3: Fixed-Length UUIDs

CREATE TABLE devices (
    uuid BINARY(16) PRIMARY KEY  -- UUIDs as 16-byte binary
);

---

Common Operations

Inserting Binary Data

-- Hex literals:
INSERT INTO files (data) VALUES (0x89504E470D0A1A0A);  -- PNG header

-- From file:
LOAD_FILE('/path/to/image.jpg');  -- Requires FILE privilege

Retrieving Data

-- Get hex string:
SELECT HEX(data) FROM files WHERE id = 1;

-- Save to file:
SELECT data INTO DUMPFILE '/tmp/image.jpg' FROM files WHERE id = 1;

---

Best Practices

1. Avoid storing large files in BLOB:

   • Use file paths or cloud storage (S3) instead.

2. Compress data:

   INSERT INTO files (data) VALUES (COMPRESS(large_data));
   

3. Use VARBINARY for hashes/IDs: More efficient than CHAR.

4. Set max lengths: Prevent accidental oversized inserts.

---

Performance Considerations

• BLOBs are stored off-page (separate from table data) by default in InnoDB.

• Large BLOBs slow down queries and backups.

• Index prefixes allowed:

  CREATE INDEX idx ON files (file_data(100));  -- First 100 bytes
  

---

Common Issues

Truncation

INSERT INTO tiny_blobs (data) VALUES (RANDOM_BYTES(300));  -- Fails for TINYBLOB

Character Set Confusion

-- BINARY vs CHAR comparison:
SELECT * FROM table WHERE BINARY 'abc' = 'ABC';  -- Returns false

Overhead

• Each BLOB adds 36 bytes of overhead in InnoDB.

---

Summary

• BINARY/VARBINARY: For small, structured binary data.
• BLOB: For large, unstructured binary content.
• Always prefer file storage for >1MB data.

---

Next Topics

1. Full-Text Search
2. Data Compression Functions
3. InnoDB Storage Architecture

Concept Map

flowchart LR
    A[Schema Context] --> B[Binary, Var Binary, Blob]
    B --> C[Query Pattern]
    C --> D[Validation]
    D --> E[Production Use]

Common Pitfalls

Pitfall	Consequence	Prevention
Executing queries without validating sample rows	Logic errors reach production data or reports	Start with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behavior	Aggregations and filters return misleading results	Test with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schema	Queries fail due to missing columns/indexes	Verify structure with DESCRIBE table_name; and adapt query design

Quick Reference

CREATE TABLE sample_binary_var_binary_blob (id INT PRIMARY KEY, value BINARY_VAR_BINARY_BLOB);
DESCRIBE sample_table;
SHOW CREATE TABLE sample_table;

What's Next

• Previous: Text Types - Review the previous lesson to reinforce context.
• Module Overview - Return to this module index and choose another related lesson.