MySQL索引长度限制问题

在修改表结构时出现了错误：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的组合索引，也是有索引键长度长度限制的。