将base64编码的数据存储为BLOB或TEXT数据类型

Question

我们有一个MySQL InnoDB表，其中包含约10列由base64编码的小型javascript文件和由base64编码的png（小于2KB大小）图像。

插入的次数很少，但读取次数却很多，但是输出会在Memcached实例上缓存几分钟，以避免后续的读取。

现在，我们正在使用BLOB这些列，但是我想知道TEXT在性能或快照备份方面切换到数据类型是否有优势。

我的搜索挖掘表明，BLOB和TEXT我的情况接近相同的，因为我不知道前手什么类型的数据实际上将被存储我去了BLOB。

您是否有针对此特定案例的TEXT vs BLOB辩论的任何指针？

Answer 1

一个人不应该在自己的数据库中存储Base64编码的数据…

Base64是仅使用可打印文本字符表示任意二进制数据的一种方式：它设计用于需要跨只能处理可打印文本（例如SMTP
/电子邮件）的协议或介质传输此类二进制数据的情况。它增加了数据大小（增加了33％）并增加了编码/解码的计算成本，因此除非绝对必要，否则应避免这样做。

相反， 列
的全部要点BLOB是它们存储原始二进制字符串
。因此，只需继续将您的资料直接存储到您的BLOB列中，而无需首先对它们进行Base64编码。通常，您需要将相关的元数据存储在其他列中，例如文件版本/上次修改日期，媒体类型和（对于文本文件，例如JavaScript源）字符编码。您可能决定对TEXT文本文件使用type列，不仅使MySQL能够为您本地跟踪字符编码，而且还使MySQL可以将其转码为备用字符集和/或根据需要检查/操作文本（现在）。或将来）。

SQL数据库需要可打印文本编码（如Base64来处理任意二进制数据）的（错误）想法已被大量不了解情况的教程所延续。这个想法似乎被误认为是错误的信念，因为SQL在其他上下文中仅包含可打印文本，因此它肯定也必须对二进制数据（至少对数据传输（如果不是对数据存储）要求它）。事实并非如此：SQL可以通过多种方式传递二进制数据，包括纯字符串文字（前提是它们像其他字符串一样被正确地引号和转义）。当然，将数据（任何类型）传递到数据库的首选方法是通过参数化查询，参数可以像其他任何东西一样轻松地包含二进制数据。

出于其价值，我通常完全避免在RDBMS中存储这样的项目，而宁愿使用那些高度优化的文件存储数据库（称为 文件系统） ，但这完全是另一回事。

…除非出于性能原因将其缓存…

存储Base64编码的数据可能会带来一些好处的唯一情况是，经常从数据库中检索数据并通过需要该编码的协议进行传输-
在这种情况下，存储Base64编码的表示将不必每次获取时，对其他原始数据执行编码操作。

但是，请注意，从这种意义上讲，Base64编码的存储仅充当 缓存 ，就像出于性能原因可能存储非规范化数据一样。

......在这种情况下，它应该是TEXT不BLOB

如上面提到的，之间的差异TEXT和BLOB真的可以归结为这样一个事实TEXT列与文本特定的元数据（如存储在一起 的字符编码 和 核对
），而BLOB列不可。这个额外的元数据使MySQL可以在存储和连接字符集之间（适当时）对字符进行代码转换，并执行花式字符等效/排序。

一般而言：如果两个使用不同字符集的客户端应该看到相同的 字节 ，则需要一BLOB列；如果他们应该看到相同的 字符， 则需要一TEXT列。

使用Base64，这两个客户端必须最终发现数据解码为相同的 字节 ；但是他们应该看到编码后的数据具有相同的 字符
。例如，假设一个人希望插入的Base64编码的'Hello world!'（这是'SGVsbG8gd29ybGQh'）。如果插入的应用程序正在使用UTF-8字符集，则它将字节序列发送0x53475673624738676432397962475168到数据库。

• 如果该字节序列存储在BLOB列中，然后由运行在UTF-16 *中的应用程序检索，则将返回 相同的字节 ， 这些字节 表示'升噳扇㡧搲㥹扇全'而不是所需的Base64编码值；而

• 如果该字节序列存储在TEXT列中，然后由运行在UTF-16中的应用程序检索，则MySQL将即时对代码进行转码以返回字节序列0x0053004700560073006200470038006700640032003900790062004700510068，该字节序列表示所需的原始Base64编码值'SGVsbG8gd29ybGQh'。

当然，您仍然可以使用BLOB列并以其他方式跟踪字符编码-但这将不必要地重新发明轮子，从而增加了维护复杂性并带来了意外错误的风险。

---

*实际上，MySQL不支持使用与ASCII字节不兼容的客户端字符集（因此，Base64编码在它们的任何组合中始终保持一致），但是此示例用于说明BLOB和TEXT列类型之间的区别，因此解释了为什么TEXT从技术上讲即使在BLOB没有错误的情况下实际上也能正确工作的原因（至少在MySQL添加对非ASCII兼容客户端字符集的支持之前）。