mysql中char,varchar,text

1、char

char最大长度是255字符，注意是字符数和字符集没关系。

1）可以有默认值，

2）尾部有空格会被截断

3）不管汉字、英文，还是其他编码，都可以存255字符

2、varchar

1）varchar最多能存储65535个字节的数据,varchar 的最大长度受限于最大行长度（max row size，65535bytes），65535并不是一个很精确的上限，可以继续缩小这个上限

65535个字节包括所有字段的长度，变长字段的长度标识（每个变长字段额外使用1或者2个字节记录实际数据长度）、NULL标识位的累计

NULL标识位，如果varchar字段定义中带有default null允许列空,则需要需要1bit来标识，每8个bits的标识组成一个字段

一张表中存在N个varchar字段，那么需要（N+7）/8 （取整）bytes存储所有的NULL标识位

如果数据表只有一个varchar字段且该字段DEFAULT NULL，那么该varchar字段的最大长度为65532个字节，即65535-2-1=65532 bytes

在物理存储上，varchar使用1到2个额外的字节表示实际存储的字符串长度（bytes）。如果列的最大长度小于256个字节，用一个字节表示（标识）。如果最大长度大于等于256，使用两个字节。当选择的字符集为latin1，一个字符占用一个bytevarchar(255)存储一个字符，一共使用2个bytes物理空间存储数据实际数据长度和数据值。varchar(256)存储一个字符，使用2 bytes表示实际数据长度，一共需要3 bytes物理存储空间。

2）可以有默认值

3）尾部有空格不会截断

create table test(name varchar(65533) not null)engine=innodb DEFAULT CHARSET=latin1

使用latin1编码的时候

mysql>  drop table if exists  test;create table test(name varchar(65533) not null)engine=innodb DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.18 sec)

mysql>  drop table if exists  test;create table test(name varchar(65534) not null)engine=innodb DEFAULT CHARSET=latin1; 
Query OK, 0 rows affected (0.05 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  test;create table test(name varchar(65533)  null)engine=innodb DEFAULT CHARSET=latin1;    
Query OK, 0 rows affected, 1 warning (0.00 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  test;create table test(name varchar(65532)  null)engine=innodb DEFAULT CHARSET=latin1; 
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.04 sec)

 可以看出最大可存储的为65533字节，not null 的时候，其中两个字节记录长度

使用utf8编码的时候

mysql>  drop table if exists  test;create table test(name varchar(65533) not null)engine=innodb DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

ERROR 1074 (42000): Column length too big for column 'name' (max = 21845); use BLOB or TEXT instead
mysql>  drop table if exists  test;create table test(name varchar(21845) not null)engine=innodb DEFAULT CHARSET=utf8;     
Query OK, 0 rows affected, 1 warning (0.00 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  test;create table test(name varchar(21844) not null)engine=innodb DEFAULT CHARSET=utf8; 
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.24 sec)

 可以看出最大值为(65535-2)/3=21844,(65535-2-1)/3=21844

InnoDB中的varchar

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

1)早期的innodb_file_forma使用的Antelope文件格式，支持redundant和 compact两种row_format

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

当innodb_file_format=Antelope，ROW_FORMAT=REDUNDANT或者COMPACT时

1）innodb 的聚集索引（cluster index）仅仅存储varchar、text、blob字段的前768个字节，多余的字节存储在一个独立的overflow page中，这个列也被称作off-page

2）768个字节前缀后面紧跟着20字节指针，指向overflow pages的位置

3）在 innodb_file_format=Antelope情况下，InnoDB中最多能存储10个大字段（需要使用off-page存储）

4）innodbd的默认page size为16KB，InnoDB单行的长度不能超过16k/2=8k个字节，（768+20）*10 < 8k

当innodb_file_format=Barracuda, ROW_FORMAT=DYNAMIC 或者 COMPRESSED

1）innodb 中所有的varchar、text、blob字段数据是否完全off-page存储，根据该字段的长度和整行的总长度而定

2）对off-page存储的列，cluster index中仅仅存储20字节的指针，指向实际的overflow page存储位置

3）如果单行的长度太大而不能完全适配cluster index page，innodb将会选择最长的列作为off-page存储，直到行的长度能够适配cluster index page

MyISAM中的varchar

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

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

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

3）当表中存在varchar字段的时候，row_format可以设定为fixed或者dynamic，使用row_format=fixed存储 varchar字段数据，浪费存储空间，varchar此时会定长存储

4）row_format为fixed和dynamic，varchar的物理实现方式也不同（可以查看源代码文件field.h和field.cc

5）因而myisam的row_format在fixed和dynamic之间发生转换的时候，varchar字段的物理存储方式也将会发生变化

text

1）text和varchar基本相同

2）text会忽略指定的大小这和varchar有所不同，text不能有默认值

3）尾部有空格不会被截断

4）text使用额 外的2个字节来存储数据的大小，varchar根据存储数据的大小选择用几个字节来存储

5）text的65535字节全部用来存储数据，varchar则会 占用1－3个字节去存储数据大小

效率来说基本是char>varchar>text，但是如果使用的是Innodb引擎的话，推荐使用varchar代替