如何处理BLOB？

Question

What I know, in Database Context, BLOB or Binary Large OBject is nothing but actually a stored binary code for a given data. Can Reserves spaces in GBs and can be used to store virtually any data type. But What's actually a use of it?

My major is Computer Vision and I'm fairly novice at databases and web development. Currently, I'm working on a sentiment analysis project and want to collect a large dataset for this purpose i.e. huge number of images and also want to keep record of whether a image has been used for the analysis purpose or not. I thought storing images in database with separate column for access record is the best thing I can do to have an organized and systematic approach. But Everyone I talked with recommends not to store image as a blob in database but just have its URL or name there and should have images in a dedicated folder.

Moreover, since BLOB is just binary encoding of a file how would we decode it into an image file? I found codes like following to convert a BLOB value into an image:

echo '<img src="data:image/png;base64,' .  base64_encode($image->getimageblob())  . '" />';

echo '<img src="data:image/jpg;base64,' .  base64_encode($image->getimageblob())  . '" />';

But these codes are specific to the extension (And personally I haven't been successful with any such codes). As all extensions for sure have some different schemes and thus a code cannot be used for image of all those extensions. My dataset targets visuals of an image and not on extension thus contains images of various extensions so how can one deal with them using a BLOB?

So the approach of storing just names in database and and images in a dedicated folder sounds good but then what is the use of database itself? Can not we have some renaming mechanism for images via PHP and store them directly into that folder. Why use database when we can rename images like img_1_accesses_5.png and split image name to get the ID and number of times it accessed?

If BLOB can store virtually every type of data, why the use of BLOB is such horrible and everyone recommends not to use it? And what is the problem if we directly inject images into database as BLOB? And finally If its suitable for images then how to deal with it?

So my question is How to effectively use BLOB and for which purposes it is suitable?

</div>

Answer 1

So my question is How to effectively use BLOB and for which purposes it is suitable?

Quick and dirty answer

The simple answer is: BLOBs smaller than 256KB are more efficiently handled by a database, while a filesystem is more efficient for those greater than 1MB. Of course, this will vary between different databases and filesystems

There is a microsoft technical report here : Compare blob and ntfs filesystem . The report is quite old (2006) but i think there isn't any much change from there.

Imaging when you want to read file which stored in blob. you have send request to your database software, then the software controller will read blob data which is stored in filesystem. Instead of directly read from file-system, you have to go through 2 steps processes. So when the size of your file become bigger, blob will slow down your database a lot. And we all know that speed is the main key for database.

Hope that help