MySQL 5.7-11.3.4 The BLOB and TEXT Types_longvarbinary mysql5.-CSDN博客

A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. These differ only in the maximum length of the values they can hold. The four TEXT types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to the four BLOB types and have the same maximum lengths and storage requirements. See Section 11.7, “Data Type Storage Requirements”.

BLOB是一个二进制大对象，可以保存可变数量的数据。这四种BLOB类型是TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们仅在可保存的值的最大长度上有所不同。四种TEXT 类型是TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。它们对应于四种BLOB类型，具有相同的最大长度和存储需求。

BLOB values are treated as binary strings (byte strings). They have the binary character set and collation, and comparison and sorting are based on the numeric values of the bytes in column values. TEXT values are treated as nonbinary strings (character strings). They have a character set other than binary, and values are sorted and compared based on the collation of the character set.

BLOB值被视为二进制字符串(字节字符串)。它们具有binary 字符集和排序规则，比较和排序基于列值中字节的数值。TEXT值被视为非二进制字符串(字符串)。它们有一个非二进制的字符集，并根据字符集的排序规则对值进行排序和比较。

If strict SQL mode is not enabled and you assign a value to a BLOB or TEXT column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See Section 5.1.10, “Server SQL Modes”.

如果没有启用严格的SQL模式，并且为BLOB或TEXT列分配的值超过了列的最大长度，则会截断该值以适应该列，并生成一个警告。对于截断非空格字符，可以使用严格的SQL模式导致错误(而不是警告)并禁止插入值。

Truncation of excess trailing spaces from values to be inserted into TEXT columns always generates a warning, regardless of the SQL mode.

无论SQL模式如何，截断要插入到TEXT列的值的多余尾随空格总是会生成一个警告。

For TEXT and BLOB columns, there is no padding on insert and no bytes are stripped on select.

对于TEXT和BLOB列，插入时没有填充，选择时没有剥离字节。

If a TEXT column is indexed, index entry comparisons are space-padded at the end. This means that, if the index requires unique values, duplicate-key errors occur for values that differ only in the number of trailing spaces. For example, if a table contains 'a', an attempt to store 'a ' causes a duplicate-key error. This is not true for BLOB columns.

如果为TEXT列建立了索引，则在索引项比较的末尾填充空格。这意味着，如果索引需要唯一的值，只在尾随空格的数量上不同的值就会发生重复键错误。例如，如果一个表包含'a'，那么试图存储'a'会导致重复键错误。对于BLOB列则不是这样。

In most respects, you can regard a BLOB column as a VARBINARY column that can be as large as you like. Similarly, you can regard a TEXT column as a VARCHAR column. BLOB and TEXT differ from VARBINARY and VARCHAR in the following ways:

在大多数情况下，您可以将BLOB列视为VARBINARY列，它可以任意大。类似地，您可以将TEXT列视为VARCHAR列。BLOB和TEXT与VARBINARY和VARCHAR的区别如下:

For indexes on BLOB and TEXT columns, you must specify an index prefix length. For CHAR and VARCHAR, a prefix length is optional. See Section 8.3.4, “Column Indexes”.
对于BLOB和TEXT列上的索引，必须指定索引前缀长度。对于CHAR和VARCHAR，前缀长度是可选的。
BLOB and TEXT columns cannot have DEFAULT values.
BLOB和TEXT列不能有DEFAULT值。

If you use the BINARY attribute with a TEXT data type, the column is assigned the binary (_bin) collation of the column character set.

如果将BINARY属性与TEXT数据类型一起使用，则将为列分配列字符集的二进制(_bin)排序规则。

LONG and LONG VARCHAR map to the MEDIUMTEXT data type. This is a compatibility feature.

LONG和LONG VARCHAR映射到MEDIUMTEXT数据类型。这是一个兼容性特性。

MySQL Connector/ODBC defines BLOB values as LONGVARBINARY and TEXT values as LONGVARCHAR.

MySQL Connector/ODBC将BLOB值定义为LONGVARBINARY，将TEXT值定义为LONGVARCHAR。

Because BLOB and TEXT values can be extremely long, you might encounter some constraints in using them:

因为BLOB和TEXT值可能非常长，所以在使用它们时可能会遇到一些限制:

Only the first max_sort_length bytes of the column are used when sorting. The default value of max_sort_length is 1024. You can make more bytes significant in sorting or grouping by increasing the value of max_sort_length at server startup or runtime. Any client can change the value of its session max_sort_length variable:排序时只使用列的第一个max_sort_length字节。max_sort_length的默认值是1024。通过在服务器启动或运行时增加max_sort_length的值，可以在排序或分组时增加更多的字节。任何客户端都可以更改其会话max_sort_length变量的值:
```
mysql> SET max_sort_length = 2000;
mysql> SELECT id, comment FROM t
    -> ORDER BY comment;
```
Instances of BLOB or TEXT columns in the result of a query that is processed using a temporary table causes the server to use a table on disk rather than in memory because the MEMORY storage engine does not support those data types (see Section 8.4.4, “Internal Temporary Table Use in MySQL”). Use of disk incurs a performance penalty, so include BLOB or TEXT columns in the query result only if they are really needed. For example, avoid using SELECT *, which selects all columns. BLOB或文本列的实例在处理一个查询的结果,使用临时表导致服务器使用一个表在磁盘上,而不是在内存中因为内存存储引擎不支持这些数据类型(参见8.4.4内部临时表中使用MySQL)。使用磁盘会带来性能损失，因此只有在确实需要BLOB或TEXT列时才在查询结果中包含它们。例如，避免使用SELECT *，因为它选择所有列。
The maximum size of a BLOB or TEXT object is determined by its type, but the largest value you actually can transmit between the client and server is determined by the amount of available memory and the size of the communications buffers. You can change the message buffer size by changing the value of the max_allowed_packet variable, but you must do so for both the server and your client program. For example, both mysql and mysqldump enable you to change the client-side max_allowed_packet value. See Section 5.1.1, “Configuring the Server”, Section 4.5.1, “mysql — The MySQL Command-Line Client”, and Section 4.5.4, “mysqldump — A Database Backup Program”. You may also want to compare the packet sizes and the size of the data objects you are storing with the storage requirements, see Section 11.7, “Data Type Storage Requirements”
BLOB或TEXT对象的最大大小是由其类型决定的，但是您实际上可以在客户机和服务器之间传输的最大值是由可用内存的数量和通信缓冲区的大小决定的。您可以通过更改max_allowed_packet变量的值来更改消息缓冲区的大小，但是您必须对服务器和客户机程序都这样做。例如，mysql和mysqldump都允许您更改客户端max_allowed_packet值。

Each BLOB or TEXT value is represented internally by a separately allocated object. This is in contrast to all other data types, for which storage is allocated once per column when the table is opened.

每个BLOB或TEXT值在内部由一个单独分配的对象表示。这与所有其他数据类型相反，在打开表时，每个列分配一次存储空间。

In some cases, it may be desirable to store binary data such as media files in BLOB or TEXT columns. You may find MySQL's string handling functions useful for working with such data. See Section 12.8, “String Functions and Operators”. For security and other reasons, it is usually preferable to do so using application code rather than giving application users the FILE privilege. You can discuss specifics for various languages and platforms in the MySQL Forums (MySQL :: MySQL Forums).

在某些情况下，可能希望在BLOB或TEXT列中存储二进制数据(如媒体文件)。您可能会发现MySQL的字符串处理函数对处理这些数据很有用。参见12.8节“字符串函数和操作符”。出于安全性和其他原因，使用应用程序代码而不是给予应用程序用户FILE特权通常是更好的选择。您可以在MySQL论坛中讨论各种语言和平台的细节

Note

Within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex. For more information about that option, see Section 4.5.1, “mysql — The MySQL Command-Line Client”.

在mysql客户端中，二进制字符串使用十六进制表示法显示，这取决于——binary-as-hex的值。