I have plenty of base64_encoded strings which I save in my MySQL DB. However, since base64_encoded strings are 33% larger, I would like to know how I can optimize my DB storage (Now I store my strings in a LONGTEXT field, but what about LONGBLOB or something similar?). So, how I can save my data (which is now base64 encoded) more optimized so I save storage space... (When selecting the value, I still have to be able to encode it base64 safely again).
Thanks
解决方案
If you have 5.6.1 or later, FROM/TO_BASE64() are available in MySQL.
Base64 is plain ascii text. Storing base64 in a TEXT is no problem. No escaping is necessary since (I think) there are no naughty characters. But storing the result of FROM_BASE64 needs a BLOB (of some size).
So...
INSERT INTO t (myblob) VALUES ( FROM_BASE64('UmljayBKYW1lcw==') );
SELECT TO_BASE64(myblob) FROM t;
The FROM_BASE64 will reconstruct the original (pre-base64) data.
Even better would be to compress instead:
INSERT INTO t (myblob) VALUES ( COMPRESS('UmljayBKYW1lcw==') );
SELECT UNCOMPRESS(myblob) FROM t;
That way, you will (probably) get more than the 33% _if the original (pre-base64) data was compressible. If the original was a .jpg or some other already-compressed data, then you may as well use FROM_BASE64 instead of COMPRESS.