导数据遇到的ERROR 1071 (42000): Specified key was too long问题

来源：互联网发布：淘宝助理下载与安装编辑：程序博客网时间：2024/05/23 16:33

今天在导数据到新搭建的测试环境中遇到了下面的错误提示：
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
应该是键的长度超过了阈值，首先是想到了字符集设置的问题，在将测试环境与线上环境的字符集设置成一致后，依然是这个错误，查看表的定义

CREATE TABLE `__Auth` (  `user` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,  PRIMARY KEY (`user`),  KEY `user` (`user`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED;

user字段确实较大255*3接近了767，通过对比2个实例的参数，发现了一个参数innodb_large_prefix，线上设置了这个参数为on，允许键的长度超出限制。测试环境设置后，正常导入
innodb_large_prefix

Introduced 5.6.3
Command-Line Format –innodb_large_prefix
System Variable Name innodb_large_prefix
Variable Scope Global
Dynamic Variable Yes
Permitted Values Type boolean
Default OFF
Enable this option to allow index key prefixes longer than 767 bytes (up to 3072 bytes) for InnoDB tables that use the DYNAMIC and COMPRESSED row formats. (Creating such tables also requires the option values innodb_file_format=barracuda and innodb_file_per_table=true.) See Section 14.8.8, “Limits on InnoDB Tables” for the relevant maximums associated with index key prefixes under various settings.

For tables using the REDUNDANT and COMPACT row formats, this option does not affect the allowed key prefix length.

0 0