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

这个问题真的不简单。

我本地的数据库是mysql5.5

先看一下建表语句：

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

再看一下表结构：

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

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

1. mysql> ALTER TABLE `shop` CHANGE `comment` `comment` VARCHAR( 21840 ) NOT NULL COMMENT '备注'; 
2. 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字段:

1. mysql> explain table_test; 
2. +-------+----------------+------+-----+---------+-------+ 
3. | Field | Type           | Null | Key | Default | Extra | 
4. +-------+----------------+------+-----+---------+-------+ 
5. | abc   | varchar(21844) | NO   |     | NULL    |       | 
6. +-------+----------------+------+-----+---------+-------+ 

首先要知道的是，mysql的记录行长度是有限制的，不是无限长的，这个长度是64K，即65535个字节，对所有的表都是一样的。

另外要知道的是编码。
utf8编码一个字符占3个字节；
gbk编码一个字符占2个字节；
latin1编码一个字符占1个字节。

可以用如下的程序来验证一下，已知程序文件的编码是utf-8：

1. $str = '中'; 
2. echo mb_strlen($str); 
3.  
4. $str = mb_convert_encoding($str, "gbk", "utf-8"); 
5. echo mb_strlen($str); 
6.  
7. $str = mb_convert_encoding($str, "latin1", "gbk"); 
8. echo mb_strlen($str); 

输出：

1. 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字段看看能不能成功。如下：

1. mysql> explain table_test; 
2. +-------+----------------+------+-----+---------+-------+ 
3. | Field | Type           | Null | Key | Default | Extra | 
4. +-------+----------------+------+-----+---------+-------+ 
5. | abc   | varchar(21844) | NO   |     | NULL    |       | 
6. | def   | tinyint(1)     | NO   |     | NULL    |       | 
7. +-------+----------------+------+-----+---------+-------+
   

当我们试图将tinyint字段类型改为占2个字节的smallint时，mysql报错了。可见，我们的理解是正确的。

---------------------------华丽无敌的分隔线---------------------------

有了上面的铺垫，再来看我们的问题就简单了。

先了解一下各种数值类型所占的字节。

int	4字节
smallint	2字节
tinyint	1字节
decimal	变长

对于decimal类型我觉得需要详细的说一下，手册上说的不是很明白。

让我们先看一下英文手册的原文：

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

官方的翻译如下：

1. 使用二进制格式将9个十进制(基于10)数压缩为4个字节来表示DECIMAL列值。
2. 每个值的整数和分数部分的存储分别确定。
3. 每个9位数的倍数需要4个字节，并且“剩余的”位需要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

本文转自 ustb80 51CTO博客，原文链接：http://blog.51cto.com/ustb80/1072001，如需转载请自行联系原作者