专注系统运维、网络架构，研究技术解决方案，记录我的思想轨迹、工作学习、生活和关注的领域
分类： Mysql/postgreSQL
2013-01-31 16:54:23
在修改表结构时出现了错误：Specified key was too long;max key length is 1000 bytes.
MySQL版本为Server version: 5.1.36， 执行SQL为：
alter table pre_common_diy_data modify column targettplname varchar(255);
如果是按一个字符占两个字节计算 2*255=510 并没有超过1000字符，怎么会报错呢?
在查询相关资料后发现，MySQL MyIsam 存储引擎在创建索引的时候，索引键长度是有一个较为严格的长度限制的，所有索引键最大长度总和不能超过1000，而且不是实际数据长度的总和，而是索引键字段定义长度的总和。
主要字符集的计算方式如下：
latin1 = 1 byte = 1 character
uft8 = 3 byte = 1 character
gbk = 2 byte = 1 character
1、查看MySQL存储引擎，默认存储引擎为MyISAM。
mysql> show engines;
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
4 rows in set (0.00 sec)
2、我们查看MySQL表结构，总索引长度为：(100+80）=180
mysql> desc pre_common_diy_data;
+---------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-----------------------+------+-----+---------+-------+
| targettplname | varchar(100) | NO | PRI | | |
| tpldirectory | varchar(80) | NO | PRI | | |
| primaltplname | varchar(255) | NO | | | |
| diycontent | mediumtext | NO | | NULL | |
| name | varchar(255) | NO | | | |
| uid | mediumint(8) unsigned | NO | | 0 | |
| username | varchar(15) | NO | | | |
| dateline | int(10) unsigned | NO | | 0 | |
+---------------+-----------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
3、查看表的字符集，为utf8字符，那么索引总长度为：180*3=480
mysql> show create table pre_common_diy_data;
+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| pre_common_diy_data | CREATE TABLE `pre_common_diy_data` (
`targettplname` varchar(240) NOT NULL DEFAULT '',
`tpldirectory` varchar(80) NOT NULL DEFAULT '',
`primaltplname` varchar(255) NOT NULL DEFAULT '',
`diycontent` mediumtext NOT NULL,
`name` varchar(255) NOT NULL DEFAULT '',
`uid` mediumint(8) unsigned NOT NULL DEFAULT '0',
`username` varchar(15) NOT NULL DEFAULT '',
`dateline` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`targettplname`,`tpldirectory`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
再查看我们的执行报错SQL：
alter table pre_common_diy_data modify column targettplname varchar(255);
计算总长度：（80+255）*3=1005，已经超过了1000，所以出错。
解决的方法是减少字段的长度：
alter table pre_common_diy_data modify column targettplname varchar(240);
执行成功。
另外对于创建innodb的组合索引，也是有索引键长度长度限制的。
