mysql char and varchar

http://dev.mysql.com/doc/refman/5.7/en/char.html

char

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 (2^8). 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 列的长度是固定的，按照表创建时指定的长度。存储时，将会按照左对齐的方式填充 space。选取时，尾部 space 将删除。

存储超过定长的会报错。

varchar

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535 (2^16). 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 C.10.4, “Limits on Table Column Count and Row Size”.

varchar 类型的值是变长的，只要不超过指定的长度即可。varchar 保留字符串中的空格，无论是存还是取。

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

varchar 存储时会多一个字节到2个字节，用以保存字符的长度。

存储超过定长的会报错。

how to choice

char 用作固定长度的字符，varchar 用作变长的


mysql> create table t (v varchar(4), c char(4));
Query OK, 0 rows affected (0.12 sec)

mysql> insert into t values ('ab  ', 'ab  ');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+------+------+
| v    | c    |
+------+------+
| ab   | ab   |
+------+------+
1 row in set (0.00 sec)

mysql> select concat('(', v, ')'), concat('(', c, ')') from t;
+---------------------+---------------------+
| concat('(', v, ')') | concat('(', c, ')') |
+---------------------+---------------------+
| (ab  )              | (ab)                |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> insert into t values ('abcde', 'abcde');
ERROR 1406 (22001): Data too long for column 'v' at row 1
mysql> insert into t values ('ab', 'ab  ');
Query OK, 1 row affected (0.00 sec)

zhyq0826 / zhyq0826.github.io