Store audio files in MySQL BLOB

by

Posted on by

Audio (MP3, WAV, Ogg, WMA, M4A, AAC, etc.) files can be stored as MySQL BLOB data.

1. Key Overview

MySQL offers multi-level BLOB types (TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB) for storing mp3/wav/ogg audio files (up to 4 GB max with LONGBLOB). Storing audio as BLOB integrates audio data with business records (e.g., voice messages linked to user accounts) and leverages MySQL’s lightweight architecture and ease of integration with web applications.

2. Prerequisites

  • MySQL (5.7+ recommended for better BLOB performance)
  • MySQL Workbench/CLI or DBBlobEditor (simplifies BLOB import/export)
  • CREATE TABLE, INSERT, SELECT privileges on target database

3. Implementation Steps

3.1 Create Audio Table with BLOB Column

CREATE TABLE audio_mysql_blob (
    audio_id INT AUTO_INCREMENT PRIMARY KEY,
    audio_name VARCHAR(255) NOT NULL,
    audio_format VARCHAR(10) CHECK (audio_format IN ('mp3', 'wav', 'ogg')),
    file_size BIGINT NOT NULL,
    audio_blob MEDIUMBLOB NOT NULL, -- MEDIUMBLOB for audio up to 16MB (most common)
    upload_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);

3.2 Insert Audio

3.2.1 Use MySQL Workbench/CLI (SQL-based) to Insert Audio

-- Insert audio file to BLOB via MySQL CLI (using LOAD_FILE)
INSERT INTO audio_mysql_blob (audio_name, audio_format, file_size, audio_blob)
VALUES (
    'customer_voice_001',
    'mp3',
    200000,
    LOAD_FILE('/path/to/customer_voice.mp3')
);

Note: Ensure the file path is accessible by MySQL server, and adjust secure_file_priv in my.cnf if needed.

3.2.2 Use DBBlobEditor (GUI/CLI) to Insert Audio

3.3 Retrieve Audio

3.3.1 Use MySQL Workbench/CLI (SQL-based) to Retrieve Audio

-- Export BLOB to audio file via MySQL CLI (requires SELECT INTO OUTFILE)
SELECT audio_blob INTO OUTFILE '/path/exported_audio.bin'
FROM audio_mysql_blob 
WHERE audio_id = 1;

Note: Rename the exported .bin file to .mp3/.wav/.ogg (matching stored format) for playback. Ensure secure_file_priv allows writing to the target directory.

3.3.2 Use DBBlobEditor (GUI/CLI) to Retrieve Audio

4. Key Tips

  • Choose BLOB type by size: TINYBLOB (255B), BLOB (64KB), MEDIUMBLOB (16MB), LONGBLOB (4GB) – use MEDIUMBLOB for most audio files.
  • Index metadata (audio_name/audio_format), never index BLOB columns.
  • Avoid audio files >100MB in MySQL BLOB (use AWS S3/MinIO for large files).
  • DBBlobEditor CLI is suitable for automated scripts (Bash/Shell) to import/export audio files in MySQL.
  • Adjust max_allowed_packet in my.cnf to support large audio file insertion (default 4MB).

Related Guides