What is the best way to store Binary data (images, pdf's, mp3's, videos ect) in mysql and why? What i would love to know is how you do it(as developers) and why? Also how the big sites do it?
Thank you in advance ;-)
views:
116answers:
5
+5
Q:
What is the best way to store Binary data (images, pdf's, mp3's, videos ect) in mysql and why?
+4
A:
To store Binary data in MySQL Blob can be used: MySQL Manuel: 10.4.3. The BLOB and TEXT Types
Although if it isn't really necessary I wouldn't store binary data in a database, because the central reasons for storing data in MySQL etc. (like Join, Index, ...) can hardly be used with this data types. Blob also is degrading the overall database performance.
Perhaps you build a separate table for files where you store several aspects and a filesystem path...
FloE
2010-06-22 11:29:46
I don't know, I've seen terabytes of binary data stored in Oracle databases and then streamed from it. It's not necessarily a bad idea.
Artefacto
2010-06-22 12:07:17
I don't wrote that it is generally a bad idea - there are examples and use cases for and against Blobs. Nevertheless there should - in my opinion - be good reasons and the right tuning (f.e. separated tablespaces, ...) when someone wants to use such techniques.
FloE
2010-06-22 12:40:06
A BLOB is the way to go. Storing the path has some definite problems. If files get moved, your pointers are corrupt. Backing up the DB becomes relatively pointless. Available operations on data aren't deciding characteristics for storing it.
SnOrfus
2010-06-22 15:47:03
+3
A:
In Oracle I would use a BLOB column to store such objects. That way the data integrity and security is assured in the same way as all the other data. The alternative is to use a BFILE which is a pointer to a file on the external file system - a file that may have been moved, deleted, amended or even lost when you next try to access it.
Tony Andrews
2010-06-22 11:30:58
+1 Having had to deal with both BLOB stores and file system pointers when building websites, I much prefer BLOB storage fronted with a filesystem cache prior to serving.
Colonel Sponsz
2010-06-22 11:39:06
A:
I have never used PBMS storage engine, but it looks like a way to go:
Naktibalda
2010-06-22 12:12:54
+1
A:
Well, I keep images on disk, I have lots and lots of them and I don't want to imagine the maintenance nightmare this could be. My database (mysql) so far is smaller than 5GB, and I'm fine with the ~50GB of jpeg files (each image has several sizes saved). Storing images in a separate database (with separate raid controller maybe) might be a good thing if done right, but I believe the system is faster storing them on disk.
ceteras
2010-06-22 12:21:01
+1
A:
I would use the database only for storing files metadata (size, MIME type, tags, etc). For the sake of data integrity and security I'd better offload handling the binary data to a specialized service such as Amazon's S3.
Concerning the data integrity: I'd avoid overloading the DB server by performing transactions that involve large blobs. Instead, I'd suggest to store the files first and perform some periodical asynchronous 'garbage collection'.
koles
2010-06-22 15:33:54