lesson from the size of blob

penny336

dear all,
i found that the size blob field is < 65k,
from mysql site
http://www.mysql.com/doc/en/Storage_requirements.html
blob, L+2 bytes, where L < 2¹⁶

so if u want to store file larger than that...
please use longblob
L+4 bytes, where L < 2³²
around 4294967296/1024 = 4194304kbyte= 4mbyte

but i am wonder how to store file which larger than 4mb in mysql

alex

Grisu

Hi Alex,

larger files cannot be stored directly in the database. For larger files you can use a directory to store the files in and put the filename into the database. The best choice for the filename is an index-number to prevent duplicates (like '12345.ext').

For large files this way is better in most cases because mySQL stores all data in one file. If you have 1000 records of 1 MB, mySQL has to open 1000 MB.

Grisu

penny336

hi Grisu,
both have pros and cons...

before try blob field , i do store all files to filesystem.
in that case, we need to handle unique file naming , store display filename in database and link them up.
also the folder should change mode to 777 under linux, which will encounter the security problems.

that's why we now try the blob approach, hopefully can avoid all the problems mention above.

alex

Weedpacket

You could also consider using a different DBMS than MySQL; PostgreSQL, for example, puts an upper limit of 1GB on a field.

But generally speaking, storing binaries as files and their just their names in the database is much more effective, as Grisu suggests; it depends on how often you'll be using all those files.