ERROR 1071 (42000): Specified key was too long； max key length is 767 bytes

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. 使用前缀索引解决这个问题这个问题

注：

参考

https://www.cnblogs.com/kerrycode/p/9680881.html