谈MySQL中char varchar区别

偶尔间，发现一处bug：程序要求将系统当前时间及相关配置信息按照指定帧结构，发给远端socket，但有时总会少1个字节。由于处理流程是在存储过程中完成的，遂定位到错误，归结为对char varchar区别不够了解的原因。

MySQL中CHAR VARCHAR的区别（为了不影响大家的理解，特将手册相关段落附下）。

---

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 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 column 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 and the character set used. 

In contrast to CHAR, VARCHAR values are stored as a one-byte or two-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 and suppress insertion of the value by using strict SQL mode.

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.

---

由此可见，在CHAR字段中如果结尾是空格（CHAR(0x20)），在获取其值时，将被自动截断，这也是bug存在的地方。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25432352/viewspace-736130/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25432352/viewspace-736130/