need mysql compatible compress()/decompress() for Java

Question

I'm thinking of applying the MySql compress() function to a field that is varchar and tends to run from a few thousand characters to more than a million, per column. The text is almost normal English, so I get a 8-to-1 or ever better compression. Since I have millions of records and rarely ever want to actually look at the data, compression seems to be a good engineering tradeoff.

I need to do most of the processing in Java, and there are nice implementations of zip, gzip and bzip2. So that is cool.

But I'd really love to be able to use the standard MySql client to do queries such as

select decompress(longcolumn) where ...

so I'd like my java code to use the same, or a compatible compression algorithm as the built in function. The documentation I find says "compiled with a compression library such as zlib"

this is a bit vague, how can I know exactly what to use?

=== edited == to be clear, I want to be able to use "mysql" the client program to do debugging, so things like:

select decompress(longcolumn) where ...

don't use Java at all. But I want to do the updates and inserts using JDBC. And the mainline usage, has to get the compressed blog, and then decompress it. Some sort of wrapper or ZipInputStream is fine.

Answer 1

I'm not certain, but I'd try just wrapping the output with an InflaterInputStream():

ResultSet resultSet = statement.executeQuery("SELECT blobfield FROM table");
InputStream stream = new InflaterInputStream(resultSet.getBlob(1).getBinaryStream());

http://docs.oracle.com/javase/1.4.2/docs/api/java/util/zip/InflaterInputStream.html was moved to https://docs.oracle.com/javase/7/docs/api/java/util/zip/InflaterInputStream.html

This blogpost might be interesting to you as well: http://www.mysqlperformanceblog.com/2012/05/30/data-compression-in-innodb-for-text-and-blob-fields/