问题:

I have a table with large amounts of BLOB data in a column. I am writing a utility to dump the data to file system. But before dumping, I need to check if necessary space is available on the disk to export all the blob fields throughout the table.

Please suggest an efficient approach to get size of all the blob fields in the table.

回答1:

You can use the MySQL function OCTET_LENGTH(your_column_name). See here for more details.

回答2:

select sum(length(blob_column)) as total_size 
from your_table

回答3:

select sum(length(blob_column_name)) from desired_tablename;

回答4:

Sadly this is DB specific at best.

To get the total size of a table with blobs in Oracle I use the following: https://blog.voina.org/?p=374

Sadly this does not work in DB2 I still have to find an alternative.

The simple

select sum(length(blob_column)) as total_size 
from your_table

is not a correct query as is not going to estimate correctly the blob size based on the reference to the blob that is stored in your blob column. You have to get the actual allocated size on disk for the blobs from the blob repository.