0

I am not a strong expert in databases at this level, so I was wondering the following.

A database I am currently working on has a large TEXT field where potentially hundreds of megabytes are stored in each row. I understand this is not optimal design but it predates me and I can't change it now.

The insertion of the data happens with a single SQL query INSERT INTO, which of course results in a gigantic query. I was wondering the following:

  • Is INSERT the only option in this case when submitting large blob of data? maybe there's a SQL extension to handle such large transfers that is better optimized.
  • Having a single query of this size obviously requires that the whole transfer of these hundreds of megabytes goes smooth without any network issue for the whole duration of the transfer. How resilient is this to network issues, and can it be restarted in case of failure without having to reissue the whole query again (and therefore restart the whole transfer from scratch)?

I am using the latest MySQL, but I wonder also about other databases for personal awareness.

1

I recommend avoiding putting more than 16MB (perhaps less) in a column in MySQL. There are many limitations in MySQL and possible in the companion tools you are using.

For storing Text (as opposed to blobs) in a column, I would strongly consider compressing it. This is likely to shrink the data by 3x. Do the compression in the Client, not the Server.

What is the Text for? How will be it be used eventually? An analogy: with images (which must be stored in BLOB/MEDIUMBLOB/LONGBLOB), it can be better to store them as files when the use is HTML's <img src=file...>. This makes the fetching trivial.

I once had the task of storing large data, even multi-GB movies. Obviously, I could not even use LONGBLOB (4GB limit). I chose to break the stream into 50KB (pick a number) chunks and store them in a BLOB in rows of a table. Together with it was a sequence number to allow reconstructing the result. This allowed me to "stream" things rather than "store (or fetch) it all at once". Hence I was not straining the limits of the client software (which was PHP and Apache).

As for inserting blob (or compressed text), it may be optimal to convert it to hex, then use UNHEX("...") in the INSERT statement. This completely avoids any issue of escaping strings, etc. (But does not avoid SQL Injection problems.) It does, however, cut in half some of the limits you may encounter.

0

The only way to get data into the database is with an INSERT INTO statement.

Additionally, you should be using BINDs to prevent SQL Injection. Some databases will require it if you are transferring "megabytes of data" for a single column. Example: Oracle static text strings (like 'this') have a maximum length of 32767 bytes where as max size for a CLOB is in the Gigabytes.

example statement using BINDs

INSERT INTO some_table ( text_file_name, text_file_data )
  VALUES (?, ?)

If there is a problem with the transfer, the database will rollback that statement and raise an error to the application.

No harm (done to the data), No foul (data in the database)

This is true for any RDBMS system.

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.