{"id":27572,"date":"2025-04-26T03:40:31","date_gmt":"2025-04-26T03:40:31","guid":{"rendered":"http:\/\/www.withdata.com\/blog\/?p=27572"},"modified":"2026-03-04T05:24:23","modified_gmt":"2026-03-04T05:24:23","slug":"store-images-in-mysql-blob","status":"publish","type":"post","link":"https:\/\/www.withdata.com\/blog\/blob\/store-images-in-mysql-blob.html","title":{"rendered":"Store images in MySQL BLOB"},"content":{"rendered":"<h2>MySQL BLOB Type Features<\/h2>\n<ul>\n<li>MySQL provides 4 graded BLOB types for different image sizes (all store binary data)<\/li>\n<li>TINYBLOB: Up to 255 bytes (ideal for small icons\/thumbnails)<\/li>\n<li>BLOB: Up to 65,535 bytes (~64KB, for small images)<\/li>\n<li>MEDIUMBLOB: Up to 16,777,215 bytes (~16MB, for medium-resolution photos)<\/li>\n<li>LONGBLOB: Up to 4,294,967,295 bytes (~4GB, for high-resolution\/large images)<\/li>\n<li>All BLOB types are binary-safe (no character set conversion)<\/li>\n<li>Supports partial BLOB reading (via SUBSTRING()) to optimize performance<\/li>\n<\/ul>\n<h2>Step 1: Create a Table with BLOB Column<\/h2>\n<h3>Recommended Table (LONGBLOB for Universal Use)<\/h3>\n<pre>CREATE TABLE image_storage (\r\n    image_id INT PRIMARY KEY AUTO_INCREMENT, -- Auto-increment primary key\r\n    image_name VARCHAR(100) NOT NULL,\r\n    image_blob LONGBLOB NOT NULL, -- Use LONGBLOB for most image scenarios\r\n    upload_time DATETIME DEFAULT CURRENT_TIMESTAMP\r\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;\r\n<\/pre>\n<h3>Optimized Table (For Specific Image Sizes)<\/h3>\n<pre>-- For thumbnails only (TINYBLOB)\r\nCREATE TABLE image_thumbnails (\r\n    thumbnail_id INT PRIMARY KEY AUTO_INCREMENT,\r\n    image_id INT,\r\n    thumbnail_blob TINYBLOB NOT NULL,\r\n    FOREIGN KEY (image_id) REFERENCES image_storage(image_id)\r\n);\r\n<\/pre>\n<h2>Step 2: Insert Images into MySQL BLOB<\/h2>\n<h3>Method 1: Using MySQL Client\/Workbench (Local File)<\/h3>\n<pre>-- Prerequisite: Enable local_infile (run first in MySQL client)\r\nSET GLOBAL local_infile = 1;\r\n\r\n-- Insert image from local file (using LOAD_FILE)\r\nINSERT INTO image_storage (image_name, image_blob)\r\nVALUES (\r\n    'product_photo.jpg',\r\n    LOAD_FILE('\/path\/to\/product_photo.jpg')\r\n);\r\n\r\n-- Alternative: Insert with HEX\/UNHEX (for application integration)\r\nINSERT INTO image_storage (image_name, image_blob)\r\nVALUES ('product_photo.jpg', UNHEX('your_image_hex_string'));\r\nCOMMIT;\r\n<\/pre>\n<p><strong>Note:<\/strong><\/p>\n<p>1. Replace &#8216;\/path\/to\/product_photo.jpg&#8217; with absolute file path (MySQL requires absolute paths for LOAD_FILE)<\/p>\n<p>2. For Windows paths: Use double backslashes (C:\\\\images\\\\product_photo.jpg)<\/p>\n<p>3. Ensure MySQL server has read permission to the file path<\/p>\n<h3>Method 2: Using DBBlobEditor<\/h3>\n<h4>Insert Single Image to MySQL BLOB<\/h4>\n<ol>\n<li>Connect to your MySQL database in <a href=\"\/dbblobeditor\/\">DBBlobEditor<\/a><\/li>\n<li>Navigate to the \u201cimage_storage\u201d table in the target database<\/li>\n<li>Click \u201c+\u201d button to add a record, and then <a href=\"\/blog\/mylobeditor\/edit-mysql-image-data.html\">Edit image stored in MySQL BLOB field<\/a><\/li>\n<\/ol>\n<h4>Batch Insert Images to\u00a0MySQL BLOB<\/h4>\n<ul>\n<li><a href=\"\/blog\/mylobeditor\/import-images-to-mysql-blob.html\">Batch import image files into MySQL BLOB<\/a><\/li>\n<\/ul>\n<h2>Step 3: Retrieve and Restore MySQL BLOB to Image<\/h2>\n<h3>Method 1: Using MySQL Query + External Script (Python Example)<\/h3>\n<pre>-- Step 1: Get BLOB data (MySQL query)\r\nSELECT image_blob FROM image_storage WHERE image_id = 1;\r\n\r\n-- Step 2: Restore to file (Python script)\r\nimport mysql.connector\r\n\r\n# Connect to MySQL\r\ndb = mysql.connector.connect(\r\n    host=\"localhost\",\r\n    user=\"root\",\r\n    password=\"your_password\",\r\n    database=\"your_database\"\r\n)\r\n\r\ncursor = db.cursor()\r\ncursor.execute(\"SELECT image_blob FROM image_storage WHERE image_id = 1\")\r\nblob_data = cursor.fetchone()[0]\r\n\r\n# Write to file\r\nwith open(\"exported_photo.jpg\", \"wb\") as f:\r\n    f.write(blob_data)\r\n\r\ncursor.close()\r\ndb.close()\r\n<\/pre>\n<h3>Method 2: DBBlobEditor Export<\/h3>\n<h4>Retrieve and Restore Single Image<\/h4>\n<ol>\n<li>Connect to MySQL database in DBBlobEditor<\/li>\n<li>Query the \u201cimage_storage\u201d table and locate the target image_blob column<\/li>\n<li>Double click to <a href=\"\/blog\/mylobeditor\/edit-mysql-image-data.html\">View image stored in MySQL BLOB field<\/a><\/li>\n<li>Click menu \u201cFile\u201d -&gt; \u201cSave\u201d to save BLOB data as a local image file<\/li>\n<\/ol>\n<h4>Batch Export MySQL BLOB to Images<\/h4>\n<ul>\n<li><a href=\"\/blog\/mylobeditor\/export-images-from-mysql-to-files.html\">Export images from MySQL to files<\/a><\/li>\n<\/ul>\n<h2>MySQL Specific Notes<\/h2>\n<ul>\n<li>Adjust <strong>max_allowed_packet<\/strong> in my.cnf\/my.ini (default 4MB) to support large LONGBLOB:\n<p>max_allowed_packet = 64M (restart MySQL after change)<\/li>\n<li>Avoid indexing BLOB columns (use image_id\/image_name for filtering)<\/li>\n<li>Use InnoDB engine (better transaction support for BLOB operations)<\/li>\n<li>For high concurrency: Enable innodb_file_per_table to isolate BLOB storage<\/li>\n<li>LOAD_FILE() requires FILE privilege for the MySQL user<\/li>\n<li>Compress BLOB data (e.g., with zlib) before storage to reduce size<\/li>\n<\/ul>\n<hr \/>\n<h3>Related Guides<\/h3>\n<ul>\n<li><a href=\"\/blog\/blob\/images-in-blob.html\">Store images in BLOB: A Comprehensive Guide<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-images-in-db2-blob.html\">Store images in DB2 BLOB<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-images-in-oracle-blob.html\">Store images in Oracle BLOB<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-images-in-sql-server-varbinary.html\">Store images in SQL Server VARBINARY<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-images-in-postgresql-bytea.html\">Store images in PostgreSQL BYTEA<\/a><\/li>\n<li><a href=\"\/blog\/blob\/store-images-in-sqlite-blob.html\">Store images in SQLite BLOB<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4524],"tags":[97,2065,1323,4494],"class_list":["post-27572","post","type-post","status-publish","format-standard","hentry","category-blob","tag-blob","tag-image","tag-mysql","tag-photo"],"_links":{"self":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/27572","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/comments?post=27572"}],"version-history":[{"count":7,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/27572\/revisions"}],"predecessor-version":[{"id":45323,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/posts\/27572\/revisions\/45323"}],"wp:attachment":[{"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/media?parent=27572"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/categories?post=27572"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.withdata.com\/blog\/wp-json\/wp\/v2\/tags?post=27572"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}