2

I'm using sqlalchemy to store objects in a database. The kind of object I'm creating is unknown until run time. I could be storing something as simple as a string. I could be storing a jpg. I might create my own file format and store that. The point is it's flexible.

The table I envision contains at least two columns. One describes the file type (.jpg, .txt, .py, etc), and the other is a blob with the file data. What I want to know is whether I need to define the blob size when I create the table. Is it possible to let mysql dynamically set the size of blob? This would simplify my design nicely.

I'm aware there are other ways to do it, but I have good reasons to do it this way. I don't mind going down this path and doubling back later if it turns into a dead end.

2

No, as defined, a Blob is "a binary large object that can hold a variable amount of data." It can hold between 0 and 2^16 - 1 bytes (65535). A LONGBLOB can hold up to 4,294,967,295 bytes (or 4GB).

In most engines, blobs are implemented dynamically, meaning that it will only take its actual size plus some extra bytes for lenght/addressing, etc. In fact, fixed-row size formats are normally not compatible with blobs (MyISAM fixed, Memory storage engine).

However, there are some reasons to avoid storing "files" on a relational database (and MySQL and InnoDB in particular, mainly due to the overhead on the transaction and binary logs, plus multiversioning and its incompatibilities with temporary tables on memory). The actual decision varies depending on the needs -the most common alternative is to store those on the filesystem and just add a URL on the database.

If you store those objects inside the database, the size will not be your biggest problem (unless they are over 4GB), but the performance on complex queries and writes. However, a pure key-value store may work well with MySQL model. There is also specialised document-based databases.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.