本文的初衷僅供自己做備忘筆記, 內(nèi)容大多從網(wǎng)上搜集和整理, 并非都是自己原創(chuàng).
參考的來源我會(huì)在后面注明, 對(duì)于可能遺漏的來源, 還請(qǐng)相關(guān)原創(chuàng)作者提醒, 非常感謝.

參考來源:
https://www.cnblogs.com/kerrycode/p/9680881.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

問題描述

在MySQL 5.6版本的數(shù)據(jù)庫(kù)中修改InnoDB表字段長(zhǎng)度時(shí)遇到了"ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes"錯(cuò)誤, 原作者做了如下總結(jié).

我們先來創(chuàng)建一個(gè)測(cè)試表，構(gòu)造這樣的錯(cuò)誤。

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> 

其實(shí)這個(gè)“ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes”錯(cuò)誤是指超出索引字節(jié)的限制，并不是指字段長(zhǎng)度限制。在官方文檔“Limits on InnoDB Tables”有關(guān)于這方面的介紹、描述
MySQL 5.6文檔內(nèi)容如下:

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文檔內(nèi)容如下：

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.

如果啟用了系統(tǒng)變量innodb_large_prefix（默認(rèn)啟用，注意實(shí)驗(yàn)版本為MySQL 5.6.41,默認(rèn)是關(guān)閉的，MySQL 5.7默認(rèn)開啟），則對(duì)于使用DYNAMIC或COMPRESSED行格式的InnoDB表，索引鍵前綴限制為3072字節(jié)。如果禁用innodb_large_prefix，則對(duì)于任何行格式的表，索引鍵前綴限制為767字節(jié)。

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

對(duì)于使用REDUNDANT或COMPACT行格式的InnoDB表，索引鍵前綴長(zhǎng)度限制為767字節(jié)。例如，您可能會(huì)在TEXT或VARCHAR列上使用超過255個(gè)字符的列前綴索引達(dá)到此限制，假設(shè)為utf8mb3字符集，并且每個(gè)字符最多包含3個(gè)字節(jié)。

嘗試使用超出限制的索引鍵前綴長(zhǎng)度會(huì)返回錯(cuò)誤。要避免復(fù)制配置中出現(xiàn)此類錯(cuò)誤，請(qǐng)避免在主服務(wù)器上啟用enableinnodb_large_prefix（如果無法在從服務(wù)器上啟用）。

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

注意：上面是767個(gè)字節(jié)，而不是字符，具體到字符數(shù)量，這就跟字符集有關(guān)。GBK是雙字節(jié)的，UTF-8是三字節(jié)的

解決方案

1. 啟用系統(tǒng)變量innodb_large_prefix

注意：光有這個(gè)系統(tǒng)變量開啟是不夠的。必須滿足下面幾個(gè)條件：

• 系統(tǒng)變量innodb_large_prefix為ON
• 系統(tǒng)變量innodb_file_format為Barracuda
• ROW_FORMAT為DYNAMIC或COMPRESSED

如下測(cè)試所示：

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

2b638e8e.png

2. 使用前綴索引解決這個(gè)問題

之所以要限制索引鍵值的大小，是因?yàn)樾阅軉栴}，而前綴索引能很好的解決這個(gè)問題。不需要修改任何系統(tǒng)變量。

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