Store images in MySQL BLOB

Shiji Pan

11 months ago

MySQL BLOB Type Features

MySQL provides 4 graded BLOB types for different image sizes (all store binary data)
TINYBLOB: Up to 255 bytes (ideal for small icons/thumbnails)
BLOB: Up to 65,535 bytes (~64KB, for small images)
MEDIUMBLOB: Up to 16,777,215 bytes (~16MB, for medium-resolution photos)
LONGBLOB: Up to 4,294,967,295 bytes (~4GB, for high-resolution/large images)
All BLOB types are binary-safe (no character set conversion)
Supports partial BLOB reading (via SUBSTRING()) to optimize performance

Step 1: Create a Table with BLOB Column

Recommended Table (LONGBLOB for Universal Use)

CREATE TABLE image_storage (
    image_id INT PRIMARY KEY AUTO_INCREMENT, -- Auto-increment primary key
    image_name VARCHAR(100) NOT NULL,
    image_blob LONGBLOB NOT NULL, -- Use LONGBLOB for most image scenarios
    upload_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Optimized Table (For Specific Image Sizes)

-- For thumbnails only (TINYBLOB)
CREATE TABLE image_thumbnails (
    thumbnail_id INT PRIMARY KEY AUTO_INCREMENT,
    image_id INT,
    thumbnail_blob TINYBLOB NOT NULL,
    FOREIGN KEY (image_id) REFERENCES image_storage(image_id)
);

Step 2: Insert Images into MySQL BLOB

Method 1: Using MySQL Client/Workbench (Local File)

-- Prerequisite: Enable local_infile (run first in MySQL client)
SET GLOBAL local_infile = 1;

-- Insert image from local file (using LOAD_FILE)
INSERT INTO image_storage (image_name, image_blob)
VALUES (
    'product_photo.jpg',
    LOAD_FILE('/path/to/product_photo.jpg')
);

-- Alternative: Insert with HEX/UNHEX (for application integration)
INSERT INTO image_storage (image_name, image_blob)
VALUES ('product_photo.jpg', UNHEX('your_image_hex_string'));
COMMIT;

Note:

1. Replace ‘/path/to/product_photo.jpg’ with absolute file path (MySQL requires absolute paths for LOAD_FILE)

2. For Windows paths: Use double backslashes (C:\\images\\product_photo.jpg)

3. Ensure MySQL server has read permission to the file path

Method 2: Using DBBlobEditor

Insert Single Image to MySQL BLOB

Connect to your MySQL database in DBBlobEditor
Navigate to the “image_storage” table in the target database
Click “+” button to add a record, and then Edit image stored in MySQL BLOB field

Batch Insert Images to MySQL BLOB

Batch import image files into MySQL BLOB

Step 3: Retrieve and Restore MySQL BLOB to Image

Method 1: Using MySQL Query + External Script (Python Example)

-- Step 1: Get BLOB data (MySQL query)
SELECT image_blob FROM image_storage WHERE image_id = 1;

-- Step 2: Restore to file (Python script)
import mysql.connector

# Connect to MySQL
db = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your_password",
    database="your_database"
)

cursor = db.cursor()
cursor.execute("SELECT image_blob FROM image_storage WHERE image_id = 1")
blob_data = cursor.fetchone()[0]

# Write to file
with open("exported_photo.jpg", "wb") as f:
    f.write(blob_data)

cursor.close()
db.close()

Method 2: DBBlobEditor Export

Retrieve and Restore Single Image

Connect to MySQL database in DBBlobEditor
Query the “image_storage” table and locate the target image_blob column
Double click to View image stored in MySQL BLOB field
Click menu “File” -> “Save” to save BLOB data as a local image file

Batch Export MySQL BLOB to Images

Export images from MySQL to files

MySQL Specific Notes

Adjust max_allowed_packet in my.cnf/my.ini (default 4MB) to support large LONGBLOB:
max_allowed_packet = 64M (restart MySQL after change)
Avoid indexing BLOB columns (use image_id/image_name for filtering)
Use InnoDB engine (better transaction support for BLOB operations)
For high concurrency: Enable innodb_file_per_table to isolate BLOB storage
LOAD_FILE() requires FILE privilege for the MySQL user
Compress BLOB data (e.g., with zlib) before storage to reduce size