优化Base64 MySQL存储空间 [英] Base64 MySQL storage space optimalisation

问题描述

我有很多base64_encoded字符串，保存在我的MySQL数据库中.但是，由于base64_encoded字符串大了33％，所以我想知道如何优化数据库存储(现在我将字符串存储在LONGTEXT字段中，但是LONGBLOB或类似的东西呢?).因此，如何更优化地保存我的数据(现在已由base64编码)，以便节省存储空间...(选择该值时，我仍然必须能够再次安全地对其进行编码).

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).

谢谢

推荐答案

如果您拥有5.6.1或更高版本，则MySQL中可以使用FROM/TO_BASE64().

If you have 5.6.1 or later, FROM/TO_BASE64() are available in MySQL.

Base64是纯ascii文本.将base64存储在TEXT中是没有问题的.由于(我认为)没有顽皮的角色，因此无需转义.但是存储FROM_BASE64的结果需要BLOB(一定大小).

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).

所以...

INSERT INTO t (myblob) VALUES ( FROM_BASE64('UmljayBKYW1lcw==') );

SELECT TO_BASE64(myblob) FROM t;

FROM_BASE64将重建原始(base64之前的)数据.

The FROM_BASE64 will reconstruct the original (pre-base64) data.

最好是压缩:

INSERT INTO t (myblob) VALUES ( COMPRESS('UmljayBKYW1lcw==') );

SELECT UNCOMPRESS(myblob) FROM t;

这样，如果原始(base64之前的)数据是可压缩的，您将(可能)获得超过33％的收益.如果原始文件是.jpg或其他一些已经压缩的数据，则最好使用FROM_BASE64而不是COMPRESS.

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.

这篇关于优化Base64 MySQL存储空间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！