描述
[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