做数据库sql导入的时候，报错，

[Err] 1071 - Specified key was too long; max key length is 767 bytes

修正方法

set global innodb_large_prefix=1; 
set global innodb_file_format=BARRACUDA;
set global innodb_file_format_max=BARRACUDA;

每个建表语句添加 ROW_FORMAT=DYNAMIC

DROP TABLE IF EXISTS `api_card`;
CREATE TABLE `api_card` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `verson` int(11) DEFAULT '1',
  `record_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ROW_FORMAT=DYNAMIC ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='场景id';

原因

导致这个问题的原因索引的字段都太长了，解决方法就是，让mysql支持比较长的索引,然后在插入表的时候，添加ROW_FORMAT=DYNAMIC ，自动格式化索引。

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.

 

MySQL 5.7文档内容如下：

If innodb_large_prefix is enabled (the default), the index key prefix limit is 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format. If innodb_large_prefix is disabled, the index key prefix limit is 767 bytes for tables of any row format.

innodb_large_prefix is deprecated and will be removed in a future release. innodb_large_prefix was introduced in MySQL 5.5 to disable large index key prefixes for compatibility with earlier versions of InnoDB that do not support large index key prefixes.

The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACTrow format. 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.

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.

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

innodb_large_prefix将在以后的版本中删除、弃用。在MySQL 5.5中引入了innodb_large_prefix，用来禁用大型前缀索引，以便与不支持大索引键前缀的早期版本的InnoDB兼容。

对于使用REDUNDANT或COMPACT行格式的InnoDB表，索引键前缀长度限制为767字节。例如，可能会在TEXT或VARCHAR列上使用超过255个字符的列前缀索引达到此限制，假设为utf8mb3字符集，并且每个字符最多包含3个字节。

尝试使用超出限制的索引键前缀长度会返回错误。要避免复制配置中出现此类错误，请避免在主服务器上启用enableinnodb_large_prefix（如果无法在从服务器上启用）。

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

注意：上面是767个字节，而不是字符，具体到字符数量，这就跟字符集有关。GBK是双字节的，UTF-8是三字节的