【Database】排错：Mysql5.6报错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      

错误是指超出索引字节的限制，并不是指字段长度限制。

MySQL 5.6官方文档内容如下

    By default, the index key prefix length limit is 767 bytes. See Section 13.1.13, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, the index key prefix length limit is raised to 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.

    Attempting to use an index key prefix length that exceeds the limit returns an error. To avoid such errors in replication configurations, avoid enablinginnodb_large_prefix on the master if it cannot also be enabled on slaves.

      The limits that apply to index key prefixes also apply to full-column index keys.

译文：

    默认情况下，索引键前缀长度限制为767字节。参见13.1.13节“创建索引语法”。例如，如果在TEXT或VARCHAR列上的列前缀索引超过255个字符，就可能会达到这个限制，假设使用utf8mb3字符集，每个字符的最大字节数为3字节。当innodb_large_prefix配置选项被启用时，对于使用动态或压缩行格式的InnoDB表，索引键前缀长度限制提高到3072字节。

    试图使用超过限制的索引键前缀长度将返回错误。为了避免在复制配置中出现这种错误，如果不能在从服务器上启用，请避免在主服务器上启用innodb_large_prefix。

    适用于索引键前缀的限制也适用于全列索引键。

官网文档<

https://dev.mysql.com/doc/refman/5.6/en/innodb-limits.html

>

如果启用了系统变量innodb_large_prefix（默认启用，注意实验版本为MySQL  5.6.41,默认是关闭的，MySQL 5.7默认开启），则对于使用DYNAMIC或COMPRESSED行格式的InnoDB表，索引键前缀限制为3072字节。如果禁用innodb_large_prefix，则对于任何行格式的表，索引键前缀限制为767字节。

解决：

 1： 系统变量innodb_large_prefix为ON

 2： 系统变量innodb_file_format为Barracuda

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> 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)