MySQL data type--string char (m) and varchar (m)

CHAR (m) fixed length string type non-Unicode character

varchar (m) variable length string type non-Unicode data

Description:m is the maximum number of bytes that can be stored. Man accounts for two bytes , by specifying m, to limit the maximum number of characters stored, char (20) and varchar (20) will store up to 20 characters, and more characters will be truncated. M must be less than the maximum number of characters allowed for this type.

One char (m) type

char (m) Maximum number of bytes 255 fixed length type M desirable 0-255 its right padding space to reach the specified length, when the char value is retrieved, the trailing space is removed (gender password)

The CHAR type is used for fixed-length strings and must be defined within parentheses with a size modifier . This size modifier ranges from 0-255.

Note: A value larger than the specified length will be truncated, and a value smaller than the specified length will be filled with a space. Therefore, the char type stores a string with no spaces at the end, and varchar is not bound by this restriction.

Two varchar (m) types

Varchar (m) Maximum byte 65535 variable length m take 0-65535 save requires character Count plus one byte to declare length (username Article title)

The VARCHAR type stores the value using only the length that is actually required to store the string ( an extra byte is added to store the length of the string itself ). A value larger than the specified length is truncated.

Three differences:

@1. Internal storage mechanism:　　

Char is fixed length, char (4) either a character, 2 characters, or 4 characters (in English) will take up 4 bytes, not enough space

varchar is the actual number of characters stored in + 1 bytes (n<=255) or 2 bytes (n>255), so varchar (4), deposit one character will occupy 2 bytes, 2 characters occupy 3 bytes, 4 characters occupy 5 bytes.

@2. String retrieval speed of char type is faster than varchar type

@3. Selection of types

If the length of the text field is fixed, such as: Identity card number, do not use varchar or nvarchar, should be used char or nchar.

Text field if the length is not fixed, such as: address, then the use of varchar or nvarchar. In addition to saving storage space, access to the hard disk is also more efficient .

@4 Performance Selection

(1) The system overhead of the varchar type on the update link is much larger than the char type, so char is suitable for applications where fields are frequently updated .

(2) varchar saves disk space

(3) When large data volume extraction, varchar disk IO consumption is lower, which means that the performance of varchar comprehensive query is better, so the actual application of large data volume (multi-row) query returns, varchar query performance than char to better than a lot of

(4) Select char and varchar to change the overall data structure of the algorithm and storage methods. In a MySQL application, if a varchar field already exists, all other char fields are stored in varchar mode.

Use MySQL to determine if a rule is required to convert a data column type

1, in a data table, if the length of each data column is fixed, then the length of each data row will be fixed.
2, as long as the data table has a variable length of the data column, then the length of the data rows are variable.
3. If the length of the data row in a data table is variable, MySQL converts the data column of the fixed-length type in the table to the corresponding variable-length type in order to save storage space.
Exception: A char data column that is less than 4 characters long is not converted to a varchar type

Sites that support multiple languages should consider using Unicode nchar or nvarchar data types to minimize character conversion issues

MySQL data type--string char (m) and varchar (m)