mysql中的varchar到底能存多长的字符
这个问题真的不简单。我本地的数据库是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
页:
[1]