I want to compress TEXT for storing in MySQL. So I would just do gzcompress() in php and then send to mysql, but I am also setting up Sphinx full text search, and it would be nice if it can populate its index with a simple query i.e.
select uncompress(thing) from table
However I would still like to do compression and decompression for the application in php rather than mysql, and only use the mysql uncompress() function for sphinx indexing.
The mysql documentation says the following about its compress function:
Nonempty strings are stored as a four-byte length of the uncompressed string (low byte first), followed by the compressed string.
So my question is... how do I construct this four-byte length of the uncompressed string? After that, the compressed BLOB looks just like the results of the php gzcompress() function.
haven't ever done this, but here are some thoughts:
1) find the length of the uncompressed string... strlen() function ought to work
2) compress the string... you've already done this part
3) pack both together for storage in mysql, formatting the number as mysql wants it:
php's pack function: sounds like you need to use format value "V" for the length (unsigned long... 32 bit, little endian byte order)