談MySQL中char varchar區別

MagicProgram發表於2012-07-20
MySql
偶爾間，發現一處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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章