一个关于mysql中varchar的问题

Question

为什么很多人喜欢在 MySQL 中使用 VARCHAR(255) ？

Answer 1

1、历史原因

varchar在MySQL 5.0.3之前只支持0-255byte，在MySQL 5.0.3之后才支持到0-65535byte

这里的255是字符的长度

2、varchar的最大长度

看一段代码

mysql> show variables like '%col%';
+---------------------------+-----------------+
| Variable_name             | Value           |
+---------------------------+-----------------+
| collation_connection      | utf8_general_ci |
| collation_database        | utf8_general_ci |
| collation_server          | utf8_general_ci |
| protocol_version          | 10              |
| slave_compressed_protocol | OFF             |
+---------------------------+-----------------+
5 rows in set (0.00 sec)

mysql> show char set;  
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode            | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
+----------+-----------------------------+---------------------+--------+
40 rows in set (0.00 sec)

mysql> create database testdbx DEFAULT CHARACTER SET latin1;

mysql> use testdb;
Database changed

mysql> use testdbx;
Database changed

mysql> drop table  if exists testa;create table testa (name varchar(65532));

mysql> drop table  if exists testa;create table testa (name varchar(65533));
Query OK, 0 rows affected (0.01 sec)
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, 
not counting BLOBs, is 65535. This includes storage overhead, check the manual. 
You have to change some columns to TEXT or BLOBs

mysql> drop table  if exists testa;create table testa (name varchar(65533) not null);

总结如下

name varchar(100) not null will be 1 byte (length) + up to 100 chars (latin1) 
name varchar(500) not null will be 2 bytes (length) + up to 500 chars (latin1) 
name varchar(65533) not null will be 2 bytes (length) + up to 65533 chars (latin1) 
name varchar(65532) will be 2 bytes (length) + up to 65532 chars (latin1) + 1 null byte 

mysql的vachar字段的类型虽然最大长度是65535，但是并不是能存这么多数据，最大可以到65533（不允许非空字段的时候），当允许非空字段的时候只能到65532

3、varchar物理存储

在物理存储上，varchar使用1到2个额外的字节表示实际存储的字符串长度（bytes）。如果列的最大长度小于256个字节，用一个字节表示（标识）。如果最大长度大于等于256，使用两个字节。

当选择的字符集为latin1，一个字符占用一个byte

varchar(255)存储一个字符，使用2bytes物理空间存储数据实际数据长度和数据值。

varchar(256)存储一个字符，使用2bytes表示实际数据长度，一共需要3bytes物理存储空间。

varchar对于不同的RDBMS引擎，有不通的物理存储方式，虽然有统一的逻辑意义。对于mysql的不同存储引擎，其实现方法与数据的物理存放方式也不同。

4、InnoDB中的varchar

InnoDB中varchar的物理存储方式与InnoDB使用的innodb_file_format有关。

• 早期的innodb_file_forma = Antelope；支持redundant和compact两种row_format

• 5.5开始或者InnoDB1.1，可以使用一种新的file format = Barracuda；Barracuda兼容Redundant，另外还支持dynamic和compressed两种row_format

当innodb_file_format=Antelope，ROW_FORMAT=REDUNDANT 或者COMPACT。innodb的聚集索引（cluster index）仅仅存储varchar、text、blob字段的前768个字节，多余的字节存储在一个独立的overflow page中，这个列也被称作off-page。768个字节前缀后面紧跟着20字节指针，指向overflow pages的位置。

另外，在innodb_file_format=Antelope情况下，InnoDB中最多能存储10个大字段（需要使用off-page存储）。innodbd的默认page size为16KB，InnoDB单行的长度不能超过16k/2=8k个字节，（768+20）*10 < 8k。

当innodb_file_format=Barracuda, ROW_FORMAT=DYNAMIC 或者 COMPRESSED

innodb中所有的varchar、text、blob字段数据是否完全off-page存储，根据该字段的长度和整行的总长度而定。对off-page存储的列，cluster index中仅仅存储20字节的指针，指向实际的overflow page存储位置。如果单行的长度太大而不能完全适配cluster index page，innodb将会选择最长的列作为off-page存储，直到行的长度能够适配cluster index page。

5、MyISAM中的varchar

对于MyISAM引擎，varchar字段所有数据存储在数据行内（in-line）。MyISAM表的row_format也影响到varchar的物理存储行为。

MyISAM的row_format可以通过create或者alter sql语句设为fixed和dynamic。另外可以通过myisampack生成row_format=compresse的存储格式。

当MyISAM表中不存在text或者blob类型的字段，那么可以把row_format设置为fixed(也可以为dynamic)，否则只能为dynamic。

当表中存在varchar字段的时候，row_format可以设定为fixed或者dynamic。使用row_format=fixed存储varchar字段数据，浪费存储空间，varchar此时会定长存储。row_format为fixed和dynamic，varchar的物理实现方式也不同（可以查看源代码文件field.h和field.cc），因而myisam的row_format在fixed和dynamic之间发生转换的时候，varchar字段的物理存储方式也将会发生变化。

总结 ：

1、存储2^8 = 256 / overflow pages / 历史原因

3、varchar不一定比char慢

3、如果有大字段使用text，请拆表

4、varchar建立索引的时候，前面20个字符以内即可

5、其实该怎么用还要怎么用 varchar(30) 、 vachar(300)等

参考资料：
http://dev.mysql.com/doc/refman/5.5/en/column-count-limit.html

Answer 2

反正varchar是可变长度的，占用的空间比char()小，而且最大也就255字符（英文或中文具体看字符集而定）

Answer 3

习惯了吧，以前旧系统有个256字节的上限，很多人养成了这个习惯。新系统上限远大于这个，所以建议在新系统中就老老实实按整数算就好了，你最多希望用户输入的字符不超过300就写300，200就写200，完全没有必要弄个255，搞得用户很茫然。