What is the datatype for creating a column in a table storing images?

Question

You might have seen the application of this on the online buying sites which show the pics of the item we are buying. So how do they store the image in the database?

Answer 1

AgentSmith I agree with you that using BLOB in MySQL could become problematic for performance. However, file based storage with applications that work with massive amounts of media files become problematic with more complex systems requiring some sort of pipeline async processing. We ended up migrating from a file based solution to implementing MongoDB GridFS for all media storage.

It's like having your own lightweight Amazon S3 infrastructure for local hosting. 🤙

If the images will only be served AS-IS requiring no automated manipulation, then storing paths would be sufficient. 👌

Answer 2

AgentSmith Storing relative file paths is certainly one way of handling it. It will likely only require a VARCHAR(100) datatype, as TEXT is a bit of an overkill for that string value.

That said, BLOB is the datatype used to actually store binary data like images, videos, etc in most modern relational databases.

MongoDB GridFS is a brilliant option if you are able to work with MongoDB.

akshay patil You'll need to provide more details like error messages you might have received, which client language and MySQL Data Provider are you using to connect to MySQL, which MySQL Engine are you running?

Context is everything for technical questions like this. 😉

Answer 3

That's why I said typically. :D You CAN store the image files in the database as a BLOB, but for most projects, it's not really good practice for the performance of your database for various reasons. It's more efficient to let your server handle the image files and its storage, and then simply reference the location in your database. This way you have a simple text record that's small instead of having to store a vast amount of images that potentially are big files also. Makes handling the images a lot easier also.

Answer 4

Typically, you have a folder on your server that stores the image files. Then in your database, you're simply storing the LOCATION of them, not the actual image files. You can use simple TEXT datatype for it and then in your code you use it as a means of filling in the location of the image.

Hope that helps!

Answer 5

@David

Agreed completely, thanks for elaborating upon that further for him. As you mentioned, we also ended up switching to MongoDB and Node for the stuff we do at this company for pretty much the same reasoning. Really love working with those better overall than when we were stuck with MySQL and PHP. Each has its respective places though so never hurts to know both for whatever your situation calls for.

Answer 6

Is MongoDB a RDBMS or not?
And what is the pipeline async processing of the images?

(Edit)
How MongoDB performs better, what is the secret behind their great performance?

Answer 7

Thank you David for an in dept information 😊
And yes I will remember your advice
Indept information for a technical question 😅😉

Answer 8

Jibin Philipose
Thank you for an intro and details, that explains in a sensible way of how it was able to perform better.

I thought the term pipeline asynchronous operation was referring to on-the-fly image editing feature, where edited image can be edited and saved and reviewed while at the same time client can continue on with their work, looks like I was mistaken 😁

Answer 9

In Oracle, BLOB and CLOB. Advice - do not use. Store images on the CLOUD. 🤔

Answer 10

Well that helped a lot
Thank you 😊😊😊
But then one question
Why do they have datatype blob.
And I tried using it on MySQL
I don't why it isn't accepting it
Is there an issue of MySQL engine?

Answer 11

Well yes you are right
Thanks a lot😊✌️👍

Answer 12

Though answers to this question about saving just a path to the location of an image are best practice, there is on occasion the need to store an actual file. In the event that you want to store a physical file in your database you'll want to use varbinary, blob, longblob, mediumblob... there are probably others that are escaping me at the moment ... depends what you are storing, the db you're using, the app environment itself etc.

Answer 13

Ipang MongoDB is a NoSQL DBMS which stores data in JSON format.

Asynchronous operations are needed when we want to process things in an irregular fashion, because sometimes it can take a huge amount of time to process a single task and due to this other tasks are not executed in their desired time.(I hope someone writes this answer in a more understanding way for a beginner)

MongoDB can handle large unstructured data and it also doesn't enforce schema rules and can be easily modifiable, due to this it performs better than mysql. There are lots of factors but these are some of the highlight features for performance.