MySQL 中的 Varbinary 与 Blob
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8476968/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Varbinary vs Blob in MySQL
提问by Jimmyb
I have about 2k of raw binary data that I need to store in a table, but don't know whether to choose the Varbinary or Blob type. I have read through the descriptions in the MySQL docs but didn't find any contract and compare descriptions. I also read that varbinary only supports up to 255 characters, but I successfully created a varbinary(2048) field, so I'm a bit confused.
我有大约 2k 的原始二进制数据需要存储在表中，但不知道是选择 Varbinary 还是 Blob 类型。我已经通读了 MySQL 文档中的描述，但没有找到任何合同和比较描述。我还读到 varbinary 最多只支持 255 个字符，但是我成功创建了一个 varbinary(2048) 字段，所以我有点困惑。
The binary data does not need to be indexed, nor will I need to query on it. Is there an advantage to using one type over the other from PHP?
二进制数据不需要被索引，我也不需要查询它。在 PHP 中使用一种类型比另一种有优势吗？
Thanks!
谢谢！
采纳答案by Romain
VARBINARYis bound to 255 bytes on MySQL 5.0.2 and below, to 65kB on 5.0.3 and above.
VARBINARY在 MySQL 5.0.2 及更低版本上绑定到 255 字节，在 5.0.3 及更高版本上绑定到 65kB。
BLOBis bound to 65kB.
BLOB绑定到 65kB。
Ultimately, VARBINARYis virtually the same as BLOB(from the perspective of what can be stored in it), unless you want to preserve compatibility with "old" versions of MySQL. The MySQL Documentationsays:
最终，VARBINARY实际上与BLOB（从可以存储的内容的角度来看）相同，除非您想保持与 MySQL 的“旧”版本的兼容性。在MySQL文档说：
In most respects, you can regard a
BLOBcolumn as aVARBINARYcolumn that can be as large as you like.
在大多数方面，您可以将
BLOB列视为可以随心所欲地大的列VARBINARY。
回答by Fernando Gonzalez Sanchez
Actually blob can be bigger (there are tinyblob, blob, mediumblob & longblob http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html) with up to 2^32 -1 on size limit.
实际上 blob 可以更大（有 tinyblob、blob、mediumblob 和 longblob http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html），大小限制高达 2^32 -1 .
Also blob storage grows "outside" of the row, while max varbinary size is tied by amount of free row size available (so it can actually be less than 64Kb).
此外，blob 存储在行的“外部”增长，而最大 varbinary 大小受可用行大小的限制（因此它实际上可以小于 64Kb）。
There are some minor differences between both
两者之间有一些细微的差别
1) With Index scripting (blob needs a prefix size on indexes, varbinary doesn't) http:/en/column-indexes.html
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));2) As already mentioned there are trailling space issues managed differently between varbinary & blob at MySql 5.0.x or earlier versions: http:///en/blob.html http:///en/binary-varbinary.html
1）使用索引脚本（blob需要索引的前缀大小，varbinary不需要）http:/en/column-indexes.html
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));2) 正如已经提到的，在 MySql 5.0.x 或更早版本的 varbinary 和 blob 之间存在不同管理的尾随空间问题：http:///en/blob.html http:///en/binary-varbinary.html
(truncating the links, since stackoverflow thinks too many links are spam)
（截断链接，因为 stackoverflow 认为太多链接是垃圾邮件）
回答by Foobarista
One significant difference is blob types are stored in secondary storage, while varbinaries are stored inline in the row in the same way as varchars and other "simple" types.
一个显着的区别是 blob 类型存储在二级存储中，而 varbinaries 以与 varchars 和其他“简单”类型相同的方式内联存储在行中。
This can have an impact on performance in a busy system, where the additional lookup to fetch and manipulate the blob data can be expensive.
这可能会对繁忙系统中的性能产生影响，在这种情况下，用于获取和操作 blob 数据的额外查找可能会很昂贵。
回答by matt
It is worth to point that Memory storage engine does not support BLOB/TEXT but it works with VARBINARY.
值得指出的是，内存存储引擎不支持 BLOB/TEXT，但它适用于 VARBINARY。
回答by akostadinov
I am just looking at a test app that stores around 5k binary data in a column. It initially used varbinary but since it is so slow I decided to try blob. Well I'm looking at disk write speed with atop and can't see any difference.
我只是在看一个测试应用程序，它在一列中存储大约 5k 二进制数据。它最初使用 varbinary 但由于它太慢我决定尝试 blob。好吧，我正在查看 atop 的磁盘写入速度，但看不出任何区别。
The only significant difference I read in mysql manual is that blobs are unsupported by the memory engine so any temporary tables you create with queries (see when mysql uses temp tables) will be created on-disk and that is much slower. So you better bet on varbinary/binary if it is a short enough to fit into a row (at the moment 64k total for all columns).
我在 mysql 手册中读到的唯一显着区别是内存引擎不支持 blob，因此您使用查询创建的任何临时表（查看mysql何时使用临时表）都将在磁盘上创建，而且速度要慢得多。因此，如果 varbinary/binary 足够短以适合一行（目前所有列的总数为 64k），您最好押注 varbinary/binary。
