Store video files in MySQL BLOB

Shiji Pan

11 months ago

Video files can be stored as MySQL BLOB data.

1. Core Compatible Type & MySQL BLOB Fundamentals

MySQL supports four BLOB subtypes tailored for storing binary data of different sizes, making it flexible for video storage across various formats (AVI, MP4, WMV, MOV, MPEG, FLV, etc.). For mainstream video files, LONGBLOB (max 4GB) is the primary choice to cover most video size requirements.

Key characteristics of MySQL BLOB for video storage:

Subtype size limits:
- TINYBLOB: Max 255B (only for ultra-short video clips)
- BLOB: Max 64KB (for video thumbnails/clips)
- MEDIUMBLOB: Max 16MB (for short videos like TikTok/Shorts)
- LONGBLOB: Max 4GB (for HD/long-form videos)
max_allowed_packet dependency: MySQL’s max_allowed_packet parameter must be larger than the maximum video file size to avoid write failures.
Storage efficiency: MySQL BLOB supports compression (via COMPRESS() function) for compressed video formats like MP4.

2. Key Operational Steps for Video Storage

2.1 Table Creation

Create a dedicated table for video storage with LONGBLOB column and auxiliary metadata columns (optimize query efficiency):

Use LONGBLOB (not smaller subtypes) to support mainstream video file sizes.
Include metadata columns: file_name (VARCHAR), file_format (VARCHAR), file_size (BIGINT), upload_date (DATETIME) etc.

SQL Code for Table Creation:

-- Create video storage table with LONGBLOB column (4GB max)
CREATE TABLE video_storage (
    id INT AUTO_INCREMENT PRIMARY KEY,
    video_blob LONGBLOB NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_format VARCHAR(50) NOT NULL,
    file_size BIGINT NOT NULL,
    upload_date DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.2 Video File Storage (Write)

SQL Code to Insert Video BLOB Data:

-- Option 1: Insert via LOAD_FILE() function (local file access required)
INSERT INTO video_storage (video_blob, file_name, file_format, file_size)
VALUES (
    LOAD_FILE('/path/to/your/video.mp4'),
    'sample_video.mp4',
    'MP4',
    (SELECT LENGTH(LOAD_FILE('/path/to/your/video.mp4')))
);

-- Option 2: Parameterized insert (for application integration)
INSERT INTO video_storage (video_blob, file_name, file_format, file_size)
VALUES (?, 'sample_video.mp4', 'MP4', 104857600); -- 100MB file size

Use DBBlobEditor (GUI/CLI) to Batch import video files into MySQL BLOB.

2.3 Video File Retrieval (Read)

SQL Code to Extract BLOB Data:

-- Select BLOB data for specific video
SELECT 
    video_blob,
    file_name,
    file_format
FROM 
    video_storage
WHERE 
    id = 1; -- Target video ID

-- Export BLOB data to local file (using SELECT + INTO OUTFILE)
SELECT video_blob 
INTO OUTFILE '/path/to/exported_video.mp4'
FROM video_storage 
WHERE id = 1;

-- Alternative: Use mysqldump for batch export
mysqldump -u your_user -p --where="id=1" --tab=/path/to/export_dir your_database video_storage

Use DBBlobEditor (GUI/CLI) to Batch export MySQL BLOB to VIDEO files.

3. Key Best Practices

Parameter Tuning: Set max_allowed_packet = 4G (my.cnf/my.ini) to support the full LONGBLOB size limit for video files.
Storage Separation: Store BLOB data in a separate InnoDB tablespace (innodb_file_per_table=1) to avoid bloating the main database file.
Query Optimization: Avoid SELECT * queries—only fetch the LONGBLOB column when necessary (use metadata queries for listing videos).
Compression: Compress video files before storage (or use MySQL’s COMPRESS()) to reduce LONGBLOB storage usage.

4. Common Issues & Solutions

Issue: LOAD_FILE() returns NULL (file not found/permission denied).
- Solution: Ensure the file is readable by the MySQL server user, and add the file path to secure_file_priv (or set to empty for testing).
Issue: Insert failed due to max_allowed_packet limit.
- Solution: Increase max_allowed_packet in MySQL config and restart the service (e.g., SET GLOBAL max_allowed_packet = 4294967295;).
Issue: Slow BLOB read/write for large videos.
- Solution: Use SSD storage, enable InnoDB buffer pool caching for BLOBs, and avoid frequent small writes to the video table.