MySQL 問題分析 : ERROR 1071 (42000) : Specified key was too long

（點擊

上方公眾號

，可快速關注）

來源：瀟湘隱者 ，

www.cnblogs.com/kerrycode/p/9680881.html

今天在MySQL 5.6版本的資料庫中修改InnoDB表欄位長度時遇到了」ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes」錯誤,第一次遇到這個錯誤，遂花了點學習、研究過、總結這個問題。 

我們先來創建一個測試表，構造這樣的錯誤。

mysql> use MyDB;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

  

Database changed

mysql> CREATE TABLE `TEST` (

    ->   `CODE_NAME` varchar(100) NOT NULL DEFAULT "",

    ->   `CODE_SEQ` smallint(6) NOT NULL DEFAULT "1",

    ->   `ACTIVE` char(1) DEFAULT "Y",

    ->   `CODE_VALUE1` varchar(250) DEFAULT NULL,

    ->   PRIMARY KEY (`CODE_NAME`,`CODE_SEQ`),

    ->   KEY `IDX_GEN_CODE` (`CODE_NAME`,`CODE_VALUE1`)

    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.02 sec)

  

  

mysql> ALTER TABLE TEST MODIFY CODE_VALUE1 VARCHAR(350);

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

mysql>

其實這個「ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes」錯誤是指超出索引位元組的限制，並不是指欄位長度限制。在官方文檔「Limits on InnoDB Tables」有關於這方面的介紹、描述（詳情請見參考資料）：

MySQL 5.6文檔內容如下 

By default, the index key prefix length limit is 767 bytes. See Section 13.1.13, 「CREATE INDEX Syntax」. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, the index key prefix length limit is raised to 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.

 

Attempting to use an index key prefix length that exceeds the limit returns an error. To avoid such errors in replication configurations, avoid enablinginnodb_large_prefix on the master if it cannot also be enabled on slaves.

 

The limits that apply to index key prefixes also apply to full-column index keys.

MySQL 5.7文檔內容如下：

If innodb_large_prefix is enabled (the default), the index key prefix limit is 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format. If innodb_large_prefix is disabled, the index key prefix limit is 767 bytes for tables of any row format.

 

innodb_large_prefix is deprecated and will be removed in a future release. innodb_large_prefix was introduced in MySQL 5.5 to disable large index key prefixes for compatibility with earlier versions of InnoDB that do not support large index key prefixes.

 

The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character.

 

Attempting to use an index key prefix length that exceeds the limit returns an error. To avoid such errors in replication configurations, avoid enablinginnodb_large_prefix on the master if it cannot also be enabled on slaves.

 

The limits that apply to index key prefixes also apply to full-column index keys.

如果啟用了系統變數innodb_large_prefix（默認啟用，注意實驗版本為MySQL 5.6.41,默認是關閉的，MySQL 5.7默認開啟），則對於使用DYNAMIC或COMPRESSED行格式的InnoDB表，索引鍵前綴限制為3072位元組。如果禁用innodb_large_prefix，則對於任何行格式的表，索引鍵前綴限制為767位元組。

innodb_large_prefix將在以後的版本中刪除、棄用。在MySQL 5.5中引入了innodb_large_prefix，用來禁用大型前綴索引，以便與不支持大索引鍵前綴的早期版本的InnoDB兼容。

對於使用REDUNDANT或COMPACT行格式的InnoDB表，索引鍵前綴長度限制為767位元組。例如，您可能會在TEXT或VARCHAR列上使用超過255個字元的列前綴索引達到此限制，假設為utf8mb3字符集，並且每個字元最多包含3個位元組。

嘗試使用超出限制的索引鍵前綴長度會返回錯誤。要避免複製配置中出現此類錯誤，請避免在主伺服器上啟用enableinnodb_large_prefix（如果無法在從伺服器上啟用）。

適用於索引鍵前綴的限制也適用於全列索引鍵。

注意：上面是767個位元組，而不是字元，具體到字元數量，這就跟字符集有關。GBK是雙位元組的，UTF-8是三位元組的

解決方案：

1：啟用系統變數innodb_large_prefix

注意：光有這個系統變數開啟是不夠的。必須滿足下面幾個條件：

• 
  

  系統變數innodb_large_prefix為ON



• 
  

  系統變數innodb_file_format為Barracuda



• 
  

  ROW_FORMAT為DYNAMIC或COMPRESSED

如下測試所示：

mysql> show variables like "%innodb_large_prefix%";

+---------------------+-------+

| Variable_name       | Value |

+---------------------+-------+

| innodb_large_prefix | OFF   |

+---------------------+-------+

1 row in set (0.00 sec)

  

mysql> set global innodb_large_prefix=on;

Query OK, 0 rows affected (0.00 sec)

  

mysql> ALTER TABLE TEST MODIFY CODE_VALUE1 VARCHAR(350);

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

mysql> 

mysql> show variables like "%innodb_file_format%";

+--------------------------+-----------+

| Variable_name            | Value     |

+--------------------------+-----------+

| innodb_file_format       | Antelope  |

| innodb_file_format_check | ON        |

| innodb_file_format_max   | Barracuda |

+--------------------------+-----------+

3 rows in set (0.01 sec)

  

mysql> set global innodb_file_format=Barracuda;

Query OK, 0 rows affected (0.00 sec)

  

mysql> ALTER TABLE TEST MODIFY CODE_VALUE1 VARCHAR(350);

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

mysql> 

  

mysql> 

mysql> show table status from MyDB where name="TEST"G;

*************************** 1. row ***************************

           Name: TEST

         Engine: InnoDB

        Version: 10

     Row_format: Compact

           Rows: 0

 Avg_row_length: 0

    Data_length: 16384

Max_data_length: 0

   Index_length: 16384

      Data_free: 0

 Auto_increment: NULL

    Create_time: 2018-09-20 13:53:49

    Update_time: NULL

     Check_time: NULL

      Collation: utf8_general_ci

       Checksum: NULL

 Create_options: 

        Comment: 

  

mysql>  ALTER TABLE TEST ROW_FORMAT=DYNAMIC;

Query OK, 0 rows affected (0.05 sec)

Records: 0  Duplicates: 0  Warnings: 0

  

mysql> show table status from MyDB where name="TEST"G;

*************************** 1. row ***************************

           Name: TEST

         Engine: InnoDB

        Version: 10

     Row_format: Dynamic

           Rows: 0

 Avg_row_length: 0

    Data_length: 16384

Max_data_length: 0

   Index_length: 16384

      Data_free: 0

 Auto_increment: NULL

    Create_time: 2018-09-20 14:04:05

    Update_time: NULL

     Check_time: NULL

      Collation: utf8_general_ci

       Checksum: NULL

 Create_options: row_format=DYNAMIC

        Comment: 

1 row in set (0.00 sec)

  

ERROR: 

No query specified

  

mysql> ALTER TABLE TEST MODIFY CODE_VALUE1 VARCHAR(350);

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0

2：使用前綴索引解決這個問題

之所以要限制索引鍵值的大小，是因為性能問題，而前綴索引能很好的解決這個問題。不需要修改任何系統變數。

mysql> show index from TEST;

..................................

  

mysql> ALTER TABLE TEST DROP INDEX IDX_GEN_CODE;

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0

  

mysql> CREATE IDX_GEN_CODE TEST ON TEST (CODE_NAME, CODE_VALUE1(12));

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

  

mysql> ALTER TABLE TEST MODIFY CODE_VALUE1 VARCHAR(350);

Query OK, 1064 rows affected (0.08 sec)

Records: 1064  Duplicates: 0  Warnings: 0

問題延伸： 為什麼InnoDB的索引位元組數限制為767位元組？ 而不是800位元組呢？ 這樣限制又是出於什麼具體性能的考慮呢？ 暫時還沒有弄清楚這些細節問題！ 

參考資料：

• 
  

  https://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html



• 
  

  https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

【關於投稿】

如果大家有原創好文投稿，請直接給公號發送留言。

① 留言格式：

【投稿】+《 文章標題》+ 文章鏈接

② 示例：

【投稿】《不要自稱是程序員，我十多年的 IT 職場總結》：http://blog.jobbole.com/94148/

③ 最後請附上您的個人簡介哈~

看完本文有收穫？請轉發分享給更多人

關注「ImportNew」，提升Java技能

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！

本站內容充實豐富，博大精深，小編精選每日熱門資訊，隨時更新，點擊「搶先收到最新資訊」瀏覽吧！

請您繼續閱讀更多來自 ImportNew 的精彩文章: