MySQL 5: CHAR and VARCHAR types

<meta charset="utf-8">
http://dev.mysql.com/doc/refman/5.0/en/char.html
CHAR-shaped, fixed, VARCHAR is a variable degree of knowledge of the database with speaking for those users who deal with South Korea, MySQL reference to the contents of the document was translated.

Fixed CHAR type. The maximum length is 255.
VARCHAR type variable. The maximum length of 255, MySQL 5.0.3 65,535 possible thereafter.
VARCHAR type in 255 characters or less 1 byte, 2 bytes, the addition of more space is needed.
Starting with version 4.1 and later CHAR (n), VARCHAR (n) where n is a byte, but refers to the number of letters. In other words, the character sets are affected.
Spaces that follow the form of a string column / versions / SQL can be handled differently depending on the mode.
When an arbitrary array of bytes to store a BLOB or worship.
CHAR / VARCHAR type, when compared or sorted, followed by white space is ignored. Similarly, the index also.
VARCHAR type column that contains the deleted / updated if the debris is not used in the table can occur. Such a variable-length rows, sometimes in the form of tables OPTIMIZE TABLE [tablename] to save disk space by removing the debris through (if you do not tablespace) and has been devoted to the improvement of reading speed.


01/04/10 CHAR and VARCHAR types
CHAR and VARCHAR types are similar, but the plethora of stores and different. Maximum length of MySQL 5.0.3, and the remaining points on whether the storage space has changed.

CHAR and VARCHAR type that you want to keep, including the maximum length of a string is declared. For example, CHAR (30) can be stored up to 30 characters.

CHAR column length specified when creating the table is fixed in length, the length is 0-255. When you save a CHAR value to put a space on the right side of the string is set for a specified length. When reading the value read by removing the spaces.

VARCHAR columns are variable-length string value. You can specify a length from 0 to 255, MySQL 5.0.3 after you can specify from 0 to 65535. Effective in future versions of MySQL 5.0.3 The maximum length of VARCHAR the maximum row size (65,535 bytes) and the use of character sets are affected.

Unlike his brother, CHAR, VARCHAR type 1 byte or 2-byte value is stored in the front, which is the value represents a few bytes in size. When less than 255 characters in a byte, 2 bytes, if it takes longer.

Strict SQL mode (strict SQL mode) has not been activated, the stored value in excess of the maximum length of the column, the value generates a warning to keep cutting. If strict SQL mode is enabled, a warning when a non-blank characters string raises an error instead added value, you can prevent. For more information, please see the following links. Section 5.1.6, "Server SQL Modes".

VARCHAR column that exceeds the length of the column space and the SQL mode, regardless of the cut will be saved, generates a warning. If you behave like CHAR column, but the warning does not occur.

VARCHAR values ​​are not saved when you add a space. Followed by the processing of space depends on the version. Starting with MySQL 5.0.3 for compatibility with standard SQL, without having to remove trailing whitespace storage and preservation makes even read. Before MySQL 5.0.3, trailing spaces will be saved when they were removed, even when it is removed while reading the value represents ilhimeul.

Before MySQL 5.0.3, trailing spaces, in order to maintain a BLOB or TEXT types had to choose. In addition, the encrypted values ​​obtained through compression of arbitrary byte values ​​of type CHAR or VARCHAR type rather than when you store it to select a BLOB, the transformation of the data imported from a space after removal to avoid potential problems allows.

The following table lists the string CHAR (4) and VARCHAR (4) through the column to hold the result shows the difference between CHAR and VARCHAR types. (All 1-byte character set such as latin1 column are assumed to use.)

Value CHAR (4) the required storage size VARCHAR (4) the required storage size
'' '' 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 value of the last row when you are not using strict SQL mode is applied. If strict SQL mode, MySQL, if you are working, are not stored value that exceeds the length will cause errors.

CHAR columns when reading a value, because it removes the trailing spaces, CHAR (4) and VARCHAR (4) When I read the value given in the value obtained is not always the same. The following is an example of the difference.



CHAR and VARCHAR columns, sorting or comparing values ​​when the column is written with three characters - Collet Communications (collation) are affected. Communication is the cholesterol of all MySQL PADSPACE. This is followed by the MySQL CHAR and VARCHAR values ​​can be compared without the influence of space means that.

VARCHAR column to store the value to remove the spaces before and even if using a version of MySQL, SQL mode is enabled or disabled, even though not related, all MySQL versions are available.

Notification
Collet Communications MySQL character set are referred to the next. Section 9.1, "Character Set Support".

Followed by a space, in such cases may be removed or be ignored during the comparison, so that column unique index (unique index) if required, followed by tens of thousands of different content, such as spaces, if you enter a string of errors jungbokki causes. For example, the table 'a' in the state with an 'a' jungbokki error occurs if you try to store.