mysql中的varchar到底能存多长的字符

ls0398

　　这个问题真的不简单。
　　我本地的数据库是mysql5.5
　　先看一下建表语句：
　　

• CREATE TABLE `shop` (
  
•   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  
•   `shop_id` int(11) NOT NULL COMMENT '商店ID',
  
•   `goods_id` smallint(6) NOT NULL COMMENT '物品ID',
  
•   `pay_type` tinyint(1) NOT NULL COMMENT '支付方式',
  
•   `price` decimal(10,2) NOT NULL COMMENT '物品价格',
  
•   `comment` varchar(21839) NOT NULL COMMENT '备注',
  
•   PRIMARY KEY (`id`),
  
•   UNIQUE KEY `shop_id` (`shop_id`,`goods_id`)
  
• ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='商店物品表'
  

　　

　　再看一下表结构：
　　

• mysql> describe shop;
  
• +----------+----------------+------+-----+---------+----------------+
  
• | Field    | Type           | Null | Key | Default | Extra          |
  
• +----------+----------------+------+-----+---------+----------------+
  
• | id       | int(11)        | NO   | PRI | NULL    | auto_increment |
  
• | shop_id  | int(11)        | NO   | MUL | NULL    |                |
  
• | goods_id | smallint(6)    | NO   |     | NULL    |                |
  
• | pay_type | tinyint(1)     | NO   |     | NULL    |                |
  
• | price    | decimal(10,2)  | NO   |     | NULL    |                |
  
• | comment  | varchar(21839) | NO   |     | NULL    |                |
  
• +----------+----------------+------+-----+---------+----------------+
  

　　

　　当我试图给varchar字段的长度加1时，杯具了：
　　

• mysql> ALTER TABLE `shop` CHANGE `comment` `comment` VARCHAR( 21840 ) NOT NULL COMMENT '备注';
  
• ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
  

　　

　　为什么varchar只能存放21839个字符呢？
　　---------------------------华丽无敌的分隔线---------------------------
　　让我们先从最简单的情况入手。我们先创建如下的表，就一个varchar字段:
　　

• mysql> explain table_test;
  
• +-------+----------------+------+-----+---------+-------+
  
• | Field | Type           | Null | Key | Default | Extra |
  
• +-------+----------------+------+-----+---------+-------+
  
• | abc   | varchar(21844) | NO   |     | NULL    |       |
  
• +-------+----------------+------+-----+---------+-------+
  

　　

　　首先要知道的是，mysql的记录行长度是有限制的，不是无限长的，这个长度是64K，即65535个字节，对所有的表都是一样的。
　　另外要知道的是编码。
　　
utf8编码一个字符占3个字节；
　　
gbk编码一个字符占2个字节；
　　
latin1编码一个字符占1个字节。
　　可以用如下的程序来验证一下，已知程序文件的编码是utf-8：
　　

• $str = '中';
  
• echo mb_strlen($str);
  
• 
  
• $str = mb_convert_encoding($str, "gbk", "utf-8");
  
• echo mb_strlen($str);
  
• 
  
• $str = mb_convert_encoding($str, "latin1", "gbk");
  
• echo mb_strlen($str);
  

　　

　　输出：
　　

• 321
  

　　

　　因为我们的表的编码是utf8，所以65535 / 3 = 21845，这就是varchar能存放的最大长度了。
　　
但当我试图将varchar的长度设置成21845时报错了，说长度超过最大长度了，为什么呢？
　　这是因为mysql对于变长类型的字段会有1－2个字节用来保存字符长度。
　　
当字符数小于等于255时，mysql只用1个字节来记录，因为2的8次方减1只能存到255。
　　
当字符数多于255时，就得用2个字节来存长度了。
　　
所以实际上我们可用的字节数是65535 - 2 = 65533字节。
　　
因此，varchar的最大长度只能到 65533 / 3 = 21844 余 1。
　　咦，还有一个字节没用到呢。
　　
为了证明我们的确还有一个字节，我们可以往表中再建一个占1个字节的tinyint字段看看能不能成功。如下：
　　

• mysql> explain table_test;
  
• +-------+----------------+------+-----+---------+-------+
  
• | Field | Type           | Null | Key | Default | Extra |
  
• +-------+----------------+------+-----+---------+-------+
  
• | abc   | varchar(21844) | NO   |     | NULL    |       |
  
• | def   | tinyint(1)     | NO   |     | NULL    |       |
  
• +-------+----------------+------+-----+---------+-------+
  

　　

　　当我们试图将tinyint字段类型改为占2个字节的smallint时，mysql报错了。可见，我们的理解是正确的。
　　---------------------------华丽无敌的分隔线---------------------------
　　有了上面的铺垫，再来看我们的问题就简单了。
　　先了解一下各种数值类型所占的字节。

int
4字节
smallint
2字节
tinyint
1字节
decimal
变长　　对于decimal类型我觉得需要详细的说一下，手册上说的不是很明白。
　　让我们先看一下英文手册的原文：
　　

• Values for DECIMAL (and NUMERIC) columns are represented using a binary format that packs nine decimal (base 10) digits into four bytes.
  
• Storage for the integer and fractional parts of each value are determined separately.
  
• Each multiple of nine digits requires four bytes, and the “leftover” digits require some fraction of four bytes.
  
• The storage required for excess digits is given by the following table.
  

　　

　　官方的翻译如下：
　　

• 使用二进制格式将9个十进制(基于10)数压缩为4个字节来表示DECIMAL列值。
  
• 每个值的整数和分数部分的存储分别确定。
  
• 每个9位数的倍数需要4个字节，并且“剩余的”位需要4个字节的一部分。
  
• 下表给出了超出位数的存储需求：
  

　　

　　下面这个表给出了剩余数字与字节长度的对应关系。

Leftover Digits
Number of Bytes
0
0
1
1
2
1
3
2
4
2
5
3
6
3
7
4
8
4　　对于上面这段文字，我不知道你明不明白，反正我是没有明白。
　　我来举个例子，相信你就明白了：
　　提出一个问题：decimal(10,2)占几个字节？
　　要搞清楚这个问题，我们需要先弄清楚几个数字的含义。
　　
10指的是整数与小数部分的总长度，2指的是小数部分的长度。
　　
那么整数部分就只有10 - 2 = 8位了。
　　
因为整数与小数的存储是各自独立确定的，所以他们各自所占空间的总和就是所占的总空间了。
　　
对应上表可知，整数部分8位占了4个字节，小数部分2位占了1个字节，所以decimal(10,2)总共占了4 + 1 = 5个字节。
　　同理，如果是decimal(6,2)，整数部分(6 - 2 ＝ 4)位占2字节，小数部分2位占1字节，总共占3字节。
　　---------------------------华丽无敌的分隔线---------------------------
　　现在我们来算一下上面的表的varchar字段到底能存多少字符：
　　下表列出每个字段所占的字节数：

int(11)
4字节
int(11)
4字节
smallint(6)
2字节
tinyint(1)
1字节
decimal(10,2)
5字节　　余下的字节数是: 65535 - (4 + 4 + 2 + 1 + 5) - 2 =  65535 - 16 - 2 = 65517。
　　65535是总字节数，括号内是除varchar字段外其他字段所占字节数，－2是字符串长度计数字节数，
　　因为是编码是utf8，所以字符数要除以3，65517 / 3 = 21839。
　　这个数就是该表的varchar类型能存放的最大字符数了，这样我们就解答了本文开头提出的问题。
　　总结一下：

• 1.mysql记录行的长度是65535字节;     
  
• 2.utf8编码占3字节，gbk编码占2字节，latin1编码占1字节;     
  
• 3.对于变长字段如varchar，mysql会用额外的字节来存储字符长度，255个字符以内用1个字节存，多于255个字符用2字节存;     
  
• 4.decimal类型的字段长度不固定，整数与小数部分所占字节数总和为总字节数，可以各自按表推算。
  

　　参考文献：

• http://dev.mysql.com/doc/refman/5.5/en/column-count-limit.html
  
• http://stackoverflow.com/questions/9153176/mysql-decimal-storage
  
• http://dev.mysql.com/doc/refman/5.1/zh/column-types.html#char