Mysql5.6 建索引时遇到报错:
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
其实这个“ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes”错误是指超出索引字节的限制,并不是指字段长度限制。
如果启用了系统变量innodb_large_prefix(默认启用,注意实验版本为MySQL 5.6.41,默认是关闭的,MySQL 5.7默认开启),则对于使用DYNAMIC或COMPRESSED行格式的InnoDB表,索引键前缀限制为3072字节。如果禁用innodb_large_prefix,则对于任何行格式的表,索引键前缀限制为767字节。
解决方法:
1. 启用系统变量innodb_large_prefix
注意:光有这个系统变量开启是不够的。必须满足下面几个条件:
1: 系统变量innodb_large_prefix为ON
2: 系统变量innodb_file_format为Barracuda
3: ROW_FORMAT为DYNAMIC或COMPRESSED
mysql> create index idx_emp_name on tbl_emp(emp_name);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
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> create index idx_emp_name on tbl_emp(emp_name);
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
mysql> show variables like '%innodb_file_format%';
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
+--------------------------+----------+
3 rows in set (0.00 sec)
mysql> set global innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.00 sec)
mysql> show table status from ssm_crud where name='tbl_emp'\G;
*************************** 1. row ***************************
Name: tbl_emp
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1001
Avg_row_length: 98
Data_length: 98304
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 1036
Create_time: 2020-04-15 10:52:30
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> alter table tbl_emp row_format=dynamic;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show table status from ssm_crud where name='tbl_emp'\G;
*************************** 1. row ***************************
Name: tbl_emp
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 1021
Avg_row_length: 96
Data_length: 98304
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 1036
Create_time: 2020-07-19 08:21:19
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: row_format=DYNAMIC
Comment:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> create index idx_emp_name on tbl_emp(emp_name);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
2. 使用前缀索引解决这个问题这个问题
注:
参考