[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