MySQL 5.7-11.3.2 The CHAR and VARCHAR Types

The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved. They also differ in maximum length and in whether trailing spaces are retained.

CHAR和VARCHAR类型类似，但在存储和检索方式上有所不同。它们在最大长度和是否保留尾随空格方面也有所不同。

The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.

CHAR和VARCHAR类型是用一个长度声明的，该长度指示希望存储的最大字符数。例如，CHAR(30)最多可以容纳30个字符。

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.

CHAR列的长度固定为创建表时声明的长度。长度可以是0到255之间的任意值。当CHAR值被存储时，它们是用指定长度的空格右填充的。当检索CHAR值时，将删除尾随空格，除非启用PAD_CHAR_TO_FULL_LENGTH SQL模式。

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. See Section 8.4.7, “Limits on Table Column Count and Row Size”.

VARCHAR列中的值是变长字符串。长度可以指定为0到65,535之间的值。VARCHAR的有效最大长度取决于最大行大小(65,535字节，由所有列共享)和所使用的字符集。

In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

与CHAR不同，VARCHAR值存储为1字节或2字节长度的前缀加上数据。长度前缀表示值的字节数。如果值要求不超过255字节，则列使用一个长度字节;如果值要求超过255字节，则使用两个长度字节。

If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR 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模式，并且将值分配给CHAR或VARCHAR列，该列的长度超过了列的最大长度，则将截断该值以适应列的长度，并生成一个警告。对于截断非空格字符，可以使用严格的SQL模式导致错误(而不是警告)并禁止插入值。

For VARCHAR columns, trailing spaces in excess of the column length are truncated prior to insertion and a warning is generated, regardless of the SQL mode in use. For CHAR columns, truncation of excess trailing spaces from inserted values is performed silently regardless of the SQL mode.

对于VARCHAR列，不论使用的SQL模式是什么，超过列长度的尾随空格都会在插入之前被截断，并生成一个警告。对于CHAR列，无论SQL模式如何，插入值的多余尾随空格的截断都会静默执行。

VARCHAR values are not padded when they are stored. Trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL.

VARCHAR值在存储时不会被填充。在存储和检索值时保留尾随空格，这符合标准SQL。

The following table illustrates the differences between CHAR and VARCHAR by showing the result of storing various string values into CHAR(4) and VARCHAR(4) columns (assuming that the column uses a single-byte character set such as latin1).

下表展示了将各种字符串值存储到CHAR(4)和VARCHAR(4)列中的结果(假设列使用一个单字节字符集，如latin1)，从而说明了CHAR和VARCHAR之间的区别。

Value	CHAR(4)	Storage Required	VARCHAR(4)	Storage Required
''	'    '	4 bytes	''	1 byte
'ab'	'ab  '	4 bytes	'ab'	3 bytes
'abcd'	'abcd'	4 bytes	'abcd'	5 bytes
'abcdefgh'	'abcd'	4 bytes	'abcd'	5 bytes

The values shown as stored in the last row of the table apply only when not using strict SQL mode; if strict mode is enabled, values that exceed the column length are not stored, and an error results.

仅当不使用严格的SQL模式时，存储在表最后一行中的值才适用;如果启用了严格模式，则不会存储超过列长度的值，并导致错误。

InnoDB encodes fixed-length fields greater than or equal to 768 bytes in length as variable-length fields, which can be stored off-page. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4.

InnoDB将长度大于等于768字节的固定长度字段编码为变长字段，可以在页外存储。例如，如果字符集的最大字节长度大于3，那么CHAR(255)列可以超过768字节，utf8mb4也是如此。

If a given value is stored into the CHAR(4) and VARCHAR(4) columns, the values retrieved from the columns are not always the same because trailing spaces are removed from CHAR columns upon retrieval. The following example illustrates this difference:

如果一个给定的值存储在CHAR(4)和VARCHAR(4)列中，那么从这些列中检索的值并不总是相同的，因为检索时从CHAR列中删除了尾随空格。下面的例子说明了这种差异:

mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO vc VALUES ('ab  ', 'ab  ');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;
+---------------------+---------------------+
| CONCAT('(', v, ')') | CONCAT('(', c, ')') |
+---------------------+---------------------+
| (ab  )              | (ab)                |
+---------------------+---------------------+
1 row in set (0.06 sec)

Values in CHAR, VARCHAR, and TEXT columns are sorted and compared according to the character set collation assigned to the column.

CHAR、VARCHAR和TEXT列中的值将根据分配给列的字符集排序规则进行排序和比较。

All MySQL collations are of type PAD SPACE. This means that all CHAR, VARCHAR, and TEXT values are compared without regard to any trailing spaces. “Comparison” in this context does not include the LIKE pattern-matching operator, for which trailing spaces are significant. For example:

所有的MySQL排序是PAD SPACE类型。这意味着将比较所有CHAR、VARCHAR和TEXT值，而不考虑任何尾随空格。在这个上下文中，“Comparison”不包括LIKE模式匹配操作符，因为后面的空格很重要。例如:

mysql> CREATE TABLE names (myname CHAR(10)); Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO names VALUES ('Jones'); Query OK, 1 row affected (0.00 sec) mysql> SELECT myname = 'Jones', myname = 'Jones ' FROM names; +------------------+--------------------+ | myname = 'Jones' | myname = 'Jones ' | +------------------+--------------------+ | 1 | 1 | +------------------+--------------------+ 1 row in set (0.00 sec) mysql> SELECT myname LIKE 'Jones', myname LIKE 'Jones ' FROM names; +---------------------+-----------------------+ | myname LIKE 'Jones' | myname LIKE 'Jones ' | +---------------------+-----------------------+ | 1 | 0 | +---------------------+-----------------------+ 1 row in set (0.00 sec)

This is not affected by the server SQL mode.

这并不受服务器SQL模式的影响。

Note

For more information about MySQL character sets and collations, see Chapter 10, Character Sets, Collations, Unicode. For additional information about storage requirements, see Section 11.7, “Data Type Storage Requirements”.

For those cases where trailing pad characters are stripped or comparisons ignore them, if a column has an index that requires unique values, inserting into the column values that differ only in number of trailing pad characters results in a duplicate-key error. For example, if a table contains 'a', an attempt to store 'a ' causes a duplicate-key error.

对于删除了尾随空格字符或比较忽略它们的情况，如果列的索引需要唯一的值，则插入仅在尾随空格字符数量上不同的列值将导致重复键错误。例如，如果一个表包含'a'，那么试图存储'a'会导致重复键错误。