直接在数据库中存储图像还是作为 Base64数据？

在数据库中存储图像的常用方法是在存储数据之前将图像转换为 base64数据。这个过程将使尺寸增加33% 。或者，也可以将图像直接存储为 BLOB; 例如:

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

然后用

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

使用后一种方法，我们节省了1/3的存储空间。为什么在 MySQL 数据库中以 base64的形式存储图像更为常见？

更新: 关于在数据库中存储图像的优缺点有很多争论，大多数人认为这不是一种实用的方法。无论如何，这里我假设我们在数据库中存储图像，并讨论这样做的最佳方法。

227161 次浏览
小开
最佳答案

  • Pro base64: the encoded representation you handle is a pretty safe string. It contains neither control chars nor quotes. The latter point helps against SQL injection attempts. I wouldn't expect any problem to just add the value to a "hand coded" SQL query string.

  • Pro BLOB: the database manager software knows what type of data it has to expect. It can optimize for that. If you'd store base64 in a TEXT field it might try to build some index or other data structure for it, which would be really nice and useful for "real" text data but pointless and a waste of time and space for image data. And it is the smaller, as in number of bytes, representation.

小开

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 column, 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).

On MySQL, and perhaps similar databases, for performance reasons, you might wish to store very small images in binary format in BINARY or VARBINARY columns so that they are on the same page as the primary key, as opposed to BLOB columns, which are always stored on a separate page and sometimes force the use of temporary tables.

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.

小开

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


提交答案