[mysql] Storing image in database directly or as base64 data?

The common method to store images in a database is to convert the image to base64 data before storing the data. This process will increase the size by 33%. Alternatively it is possible to directly store the image as a BLOB; for example:

$image = new Imagick("image.jpg");
$data = $image->getImageBlob();
$data = $mysqli->real_escape_string($data);
$mysqli->query("INSERT INTO images (data) VALUES ('$data')");

and then display the image with

<img src="data:image/jpeg;base64,' .  base64_encode($data)  . '" />

With the latter method, we save 1/3 storage space. Why is it more common to store images as base64 in MySQL databases?

UPDATE: There are many debates about advantages and disadvantages of storing images in databases, and most people believe it is not a practical approach. Anyway, here I assume we store image in database, and discussing the best method to do so.

This question is related to mysql database image imagemagick base64

The answer is


I came across this really great talk by Facebook engineers about the Efficient Storage of Billions of Photos in a database


Just want to give one example why we decided to store image in DB not files or CDN, it is storing images of signatures.

We have tried to do so via CDN, cloud storage, files, and finally decided to store in DB and happy about the decision as it was proven us right in our subsequent events when we moved, upgraded our scripts and migrated the sites serveral times.

For my case, we wanted the signatures to be with the records that belong to the author of documents.

Storing in files format risks missing them or deleted by accident.

We store it as a blob binary format in MySQL, and later as based64 encoded image in a text field. The decision to change to based64 was due to smaller size as result for some reason, and faster loading. Blob was slowing down the page load for some reason.

In our case, this solution to store signature images in DB, (whether as blob or based64), was driven by:

  1. Most signature images are very small.
  2. We don't need to index the signature images stored in DB.
  3. Index is done on the primary key.
  4. We may have to move or switch servers, moving physical images files to different servers, may cause the images not found due to links change.
  5. it is embarrassed to ask the author to re-sign their signatures.
  6. it is more secured saving in the DB as compared to exposing it as files which can be downloaded if security is compromised. Storing in DB allows us better control over its access.
  7. any future migrations, change of web design, hosting, servers, we have zero worries about reconcilating the signature file names against the physical files, it is all in the DB!

AC


I contend that images (files) are NOT usually stored in a database base64 encoded. Instead, they are stored in their raw binary form in a binary (blob) column (or file).

Base64 is only used as a transport mechanism, not for storage. For example, you can embed a base64 encoded image into an XML document or an email message.

Base64 is also stream friendly. You can encode and decode on the fly (without knowing the total size of the data).

While base64 is fine for transport, do not store your images base64 encoded.

Base64 provides no checksum or anything of any value for storage.

Base64 encoding increases the storage requirement by 33% over a raw binary format. It also increases the amount of data that must be read from persistent storage, which is still generally the largest bottleneck in computing. It's generally faster to read less bytes and encode them on the fly. Only if your system is CPU bound instead of IO bound, and you're regularly outputting the image in base64, then consider storing in base64.

Inline images (base64 encoded images embedded in HTML) are a bottleneck themselves--you're sending 33% more data over the wire, and doing it serially (the web browser has to wait on the inline images before it can finish downloading the page HTML).

If you still wish to store images base64 encoded, please, whatever you do, make sure you don't store base64 encoded data in a UTF8 column then index it.


I recommend looking at modern databases like NoSQL and also I agree with user1252434's post. For instance I am storing a few < 500kb PNGs as base64 on my Mongo db with binary set to true with no performance hit at all. Mongo can be used to store large files like 10MB videos and that can offer huge time saving advantages in metadata searches for those videos, see storing large objects and files in mongodb.


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to database

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str

Examples related to image

Reading images in python Numpy Resize/Rescale Image Convert np.array of type float64 to type uint8 scaling values Extract a page from a pdf as a jpeg How do I stretch an image to fit the whole background (100% height x 100% width) in Flutter? Angular 4 img src is not found How to make a movie out of images in python Load local images in React.js How to install "ifconfig" command in my ubuntu docker image? How do I display local image in markdown?

Examples related to imagemagick

ImageMagick security policy 'PDF' blocking conversion convert:not authorized `aaaa` @ error/constitute.c/ReadImage/453 How to convert a SVG to a PNG with ImageMagick? Storing image in database directly or as base64 data? Set transparent background using ImageMagick and commandline prompt How to convert a .eps file to a high quality 1024x1024 .jpg? Recommendation for compressing JPG files with ImageMagick Convert PDF to image with high resolution rmagick gem install "Can't find Magick-config" Convert SVG image to PNG with PHP

Examples related to base64

How to convert an Image to base64 string in java? How to convert file to base64 in JavaScript? How to convert Base64 String to javascript file object like as from file input form? How can I encode a string to Base64 in Swift? ReadFile in Base64 Nodejs Base64: java.lang.IllegalArgumentException: Illegal character Converting file into Base64String and back again Convert base64 string to image How to encode text to base64 in python Convert base64 string to ArrayBuffer