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的複合式索引，也是有索引鍵長度長度限制的。

MySQL索引長度限制問題