MySQL Data Type — varchar

1varchar(N)的逻辑意义

从MySQL4.1开始,varchar (N)中的N指的是该字段最多能存储多少个字符(characters),不是字节数。

不管是一个中英文字符或者数字、或者一个汉字,都当做一个字符。在4.1之前,N表示的是最大存储的字节数(bytes)。

2varchar(N)到底能存多长的数据

在mysql reference manual上,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。

CREATE TABLE `vchar1` (   `name` varchar(65533)  NOT  NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `vchar2` (   `name` varchar(65533)  NOT  NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

如果数据表只有一个varchar字段且该字段NOT NULL,那么该varchar字段的最大长度为65533个字节,即65535-2=65533bytes。

CREATE TABLE `vchar3` (   `name` varchar(65532)  DEFAULT  NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `vchar4` (   `name` varchar(65532)  DEFAULT  NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

来个略微复杂点的表结构,->

CREATE TABLE `tv` (
`a` varchar(100) DEFAULT NULL,
`b` varchar(100) DEFAULT NULL,
`c` varchar(100) DEFAULT NULL,
`d` varchar(100) DEFAULT NULL,
`e` varchar(100) DEFAULT NULL,
`f` varchar(100) DEFAULT NULL,
`g` varchar(100) DEFAULT NULL,
`h` varchar(100) DEFAULT NULL,
`i` varchar(N) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

`i` varchar(N) DEFAULT NULL中N最大值可以为多少?

这样计算:已知确定的字段长度为100*8  bytes,8个varchar(100)字段总共需要变长字段表示字节为1*8=8 bytes。每个NULL字段用1bit标识,9个字段都是default null,那么需要用(9+7)/8bit = 2 bytes存储NULL标识位。65535-100*8-1*8-2 = 64725 > 256, 那么字段i的最大长度为64725  – 2 =64723 bytes,即N=64723 。

varchar到底能存多少个字符?这与使用的字符集相关,latin1、gbk、utf8编码存放一个字符分别需要占1、2、3个字节。

3varchar物理存储

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

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

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

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

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

4InnoDB中的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。

5MyISAM中的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字段的物理存储方式也将会发生变化。

参考资料:

http://dev.mysql.com/doc/refman/5.5/en/column-count-limit.html
http://www.pythian.com/blog/text-vs-varchar/
http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html
http://hidba.org/?p=551
https://dev.mysql.com/doc/refman/5.5/en/innodb-table-and-index.html
http://dev.mysql.com/doc/internals/en/myisam-introduction.html

此条目发表在mysql分类目录,贴了, 标签。将固定链接加入收藏夹。

MySQL Data Type — varchar》有 2 条评论

  1. BillWu说:

    全面。。。

  2. BillWu说:

    存储NULL标识位 这里 还要看 是哪种行格式。。。。有些行格式 例如Redundant没有这个标识位。。不过也out了

发表评论

电子邮件地址不会被公开。 必填项已用*标注

This site uses Akismet to reduce spam. Learn how your comment data is processed.