BLOB and binary types in MySQL

Introduction

BLOB and binary types let you store data like pictures, music, or any files in a database.

When you want to save a photo uploaded by a user.

When storing audio or video files inside the database.

When you need to keep encrypted or compressed data.

When saving any file or data that is not plain text.

When you want to keep small files directly in your database for easy access.

Syntax

MySQL

CREATE TABLE table_name (
  column_name BLOB,
  column_name BINARY(n),
  column_name VARBINARY(n)
);

BLOB stores variable-length binary data up to 65,535 bytes.

BINARY(n) stores fixed-length binary data of size n bytes.

VARBINARY(n) stores variable-length binary data up to n bytes.

Examples

Creates a table to store photos as BLOBs, which can hold variable-length binary data.

MySQL

CREATE TABLE images (
  id INT PRIMARY KEY,
  photo BLOB
);

Stores files up to 255 bytes in size using VARBINARY.

MySQL

CREATE TABLE files (
  id INT PRIMARY KEY,
  file_data VARBINARY(255)
);

Stores fixed-length binary data of exactly 16 bytes, useful for things like encryption keys.

MySQL

CREATE TABLE keys (
  id INT PRIMARY KEY,
  key_data BINARY(16)
);

Sample Program

This creates a table to store documents with their content as BLOB. It inserts a file from the server and then shows the document info with content size.

MySQL

CREATE TABLE documents (
  id INT PRIMARY KEY,
  doc_name VARCHAR(50),
  doc_content BLOB
);

INSERT INTO documents (id, doc_name, doc_content) VALUES
(1, 'example.txt', LOAD_FILE('/var/lib/mysql-files/example.txt'));

SELECT id, doc_name, LENGTH(doc_content) AS content_size FROM documents;

Important Notes

BLOB types are good for storing large binary data but can slow down queries if overused.

LOAD_FILE() reads a file from the server's file system into a BLOB column (requires file permissions).

Use BINARY for fixed-size data to save space and VARBINARY for variable-size binary data.

Summary

BLOB and binary types store non-text data like images or files.

BLOB is variable-length and can hold large data, BINARY is fixed-length, VARBINARY is variable-length.

Use these types when you want to keep files or binary info inside your database.