TheCHARandVARCHARtypes 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.

TheCHARandVARCHARtypes 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.

The length of aCHARcolumn is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. WhenCHARvalues are stored, they are right-padded with spaces to the specified length. WhenCHARvalues are retrieved, trailing spaces are removed unless thePAD_CHAR_TO_FULL_LENGTHSQL mode is enabled.

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

In contrast toCHAR,VARCHARvalues 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.

If strict SQL mode is not enabled and you assign a value to aCHARorVARCHARcolumn 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. See5.1.11部分,“服务器QL Modes”.

ForVARCHARcolumns, 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. ForCHARcolumns, truncation of excess trailing spaces from inserted values is performed silently regardless of the SQL mode.

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

The following table illustrates the differences betweenCHARandVARCHARby showing the result of storing various string values intoCHAR(4)andVARCHAR(4)columns (assuming that the column uses a single-byte character set such aslatin1).

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

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

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

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 inCHAR,VARCHAR, andTEXTcolumns are sorted and compared according to the character set collation assigned to the column.

MySQL collations have a pad attribute ofPAD SPACE, other than Unicode collations based on UCA 9.0.0 and higher, which have a pad attribute ofNO PAD. (seeSection 10.10.1, “Unicode Character Sets”).

To determine the pad attribute for a collation, use theINFORMATION_SCHEMACOLLATIONStable, which has aPAD_ATTRIBUTEcolumn.

For nonbinary strings (CHAR,VARCHAR, andTEXTvalues), the string collation pad attribute determines treatment in comparisons of trailing spaces at the end of strings.NO PADcollations treat trailing spaces as significant in comparisons, like any other character.PAD SPACEcollations treat trailing spaces as insignificant in comparisons; strings are compared without regard to trailing spaces. SeeTrailing Space Handling in Comparisons. The server SQL mode has no effect on comparison behavior with respect to trailing spaces.

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.