[Err] 1709 - Index column size too large. The maximum column size is 767 bytes.

描述

[Err] 1709 - Index column size too large. The maximum column size is 767 bytes.
[Err] CREATE INDEX `groups_1` ON `groups` (`name`);
 

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

 

解決方案

1：啟用系統變數innodb_large_prefix

 

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

 

    1： 系統變數innodb_large_prefix為ON

    2： 系統變數innodb_file_format為Barracuda

    3： ROW_FORMAT為DYNAMIC或COMPRESSED

 

mysql> show variables like '%innodb_large_prefix%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+
1 row in set (0.00 sec)

 

mysql> set global innodb_large_prefix=on;

 

mysql>  show variables like '%innodb_file_format%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
+--------------------------+-----------+
3 rows in set (0.00 sec)

mysql> set global innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.00 sec)

use zabbix_proxy

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

 

 

 

 

參考連結 https://www.cnblogs.com/kerrycode/p/9680881.html