content_image/714984.jpg
## 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. 

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. 

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. 

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

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. 

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.11, “Server SQL Modes” . 

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  values 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 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 ). 

<img src='content_image/714984.jpg'>

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. 

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 . 

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: 

