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代替