MySQL :: MySQL 8.4 参考手册 :: 13.3.4 BLOB 和 TEXT 类型

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 13 种数据类型 / 13.3 字符串数据类型 / 13.3.4 BLOB 和 TEXT 类型

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

13.3.4 BLOB 和 TEXT 类型13.3.4 The BLOB and TEXT Types

BLOB 是可以存储可变数量数据的二进制大对象。四种 BLOB 类型是 TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们的区别仅在于可以存储值的最大长度。四种 TEXT 类型是 TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。它们对应于四种 BLOB 类型，并且具有相同的最大长度和存储要求。请参阅第 13.7 节，“数据类型存储要求”。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 13.7, “Data Type Storage Requirements”.

BLOB 值被视为二进制字符串（字节字符串）。它们具有 binary 字符集和校对规则，比较和排序基于列值中字节的数值。TEXT 值被视为非二进制字符串（字符字符串）。它们具有非 binary 的字符集，值的排序和比较基于字符集的校对规则。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.

如果未启用严格 SQL 模式，并且你向 BLOB 或 TEXT 列分配超过该列最大长度的值，该值将被截断以适应列长度，并生成一个警告。对于非空格字符的截断，可以使用严格 SQL 模式导致错误发生（而不是警告）并阻止插入该值。请参见第 7.1.11 节，“服务器 SQL 模式”。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 7.1.11, “Server SQL Modes”.

无论 SQL 模式如何，对于插入到 TEXT 列的值，超出尾随空格的截断总会生成警告。Truncation of excess trailing spaces from values to be inserted into TEXT columns always generates a warning, regardless of the SQL mode.

对于 TEXT 和 BLOB 列，插入时不会进行填充，查询时也不会剥离任何字节。For TEXT and BLOB columns, there is no padding on insert and no bytes are stripped on select.

如果对一个 TEXT 列建立了索引，索引条目的比较会在末尾进行空格填充。这意味着，如果索引要求唯一值，对于那些仅在尾随空格数量上不同的值，会发生重复键错误。例如，如果表中包含 'a'，尝试存储 'a ' 会导致重复键错误。对于 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.

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以及TEXT differ from VARBINARY以及VARCHAR in the following ways: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 和 TEXT 列的索引，必须指定索引前缀长度。对于 CHAR 和 VARCHAR，前缀长度是可选的。参见第 10.3.5 节，“列索引”。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 10.3.5, “Column Indexes”.
BLOB 和 TEXT 列不能有 DEFAULT 值。 BLOB and TEXT columns cannot have DEFAULT values.

如果你在 TEXT 数据类型上使用 BINARY 属性，该列将被分配其字符集的二进制 (_bin) 排序规则。If you use the BINARY attribute with a TEXT data type, the column is assigned the binary (_bin) collation of the column character set.

LONG 和 LONG VARCHAR 映射到 MEDIUMTEXT 数据类型。这是一个兼容性功能。LONG and LONG VARCHAR map to the MEDIUMTEXT data type. This is a compatibility feature.

MySQL Connector/ODBC 将 BLOB 值定义为 LONGVARBINARY，将 TEXT 值定义为 LONGVARCHAR。MySQL Connector/ODBC defines BLOB values as LONGVARBINARY and TEXT values as LONGVARCHAR.

由于 BLOB 和 TEXT 值可能非常长，在使用它们时你可能会遇到一些限制：Because BLOB and TEXT values can be extremely long, you might encounter some constraints in using them:

排序时只使用列的前 max_sort_length 个字节。max_sort_length 的默认值是 1024。您可以通过在服务器启动或运行时增加 max_sort_length 的值，使更多字节在排序或分组中起作用。任何客户端都可以更改其会话的 max_sort_length 变量的值：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:
```
mysql> SET max_sort_length = 2000;
mysql> SELECT id, comment FROM t
    -> ORDER BY comment;
```
在使用临时表处理查询结果时，如果结果中包含 BLOB 或 TEXT 列，会导致服务器使用磁盘上的表而不是内存中的表，因为 MEMORY 存储引擎不支持这些数据类型（参见第 10.4.4 节，“MySQL 中内部临时表的使用”）。使用磁盘会带来性能开销，因此只有在确实需要时才在查询结果中包含 BLOB 或 TEXT 列。例如，避免使用 SELECT *，因为它会选择所有列。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 10.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 或 TEXT 对象的最大大小由其类型决定，但你实际上可以在客户端和服务器之间传输的最大值取决于可用内存量和通信缓冲区的大小。你可以通过更改 max_allowed_packet 变量的值来更改消息缓冲区大小，但必须对服务器和客户端程序都进行设置。例如，mysql 和 mysqldump 都允许你更改客户端的 max_allowed_packet 值。请参阅第 7.1.1 节，“配置服务器”、第 6.5.1 节，“mysql — MySQL 命令行客户端” 和第 6.5.4 节，“mysqldump — 数据库备份程序”。你可能还希望将分组大小与所存储数据对象的大小以及存储需求进行比较，请参阅第 13.7 节，“数据类型存储需求”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 7.1.1, “Configuring the Server”, Section 6.5.1, “mysql — The MySQL Command-Line Client”, and Section 6.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 13.7, “Data Type Storage Requirements”

每个 BLOB 或 TEXT 值在内部由单独分配的对象表示。这与所有其他数据类型形成对比，其他数据类型在表打开时每列只分配一次存储空间。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 列中。您可能会发现 MySQL 的字符串处理函数对于处理此类数据很有用。请参阅第 14.8 节，“字符串函数和运算符”。出于安全和其他原因，通常更建议通过应用程序代码来实现，而不是授予应用程序用户 FILE 权限。您可以在 MySQL 论坛 (http://forums.mysql.com/) 中讨论各种语言和平台的具体情况。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 14.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 (http://forums.mysql.com/).

Note

在 mysql 客户端中，二进制字符串显示为十六进制表示，具体取决于 --binary-as-hex 的值。有关该选项的更多信息，请参阅第 6.5.1 节，“mysql — MySQL 命令行客户端”。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 6.5.1, “mysql — The MySQL Command-Line Client”.

首页上一级下一节