5.6数据导入5.7的数据库时报错：index column size too large. the maximum column size is 767 bytes

mysql快速定位某个字段所在表

select * from information_schema.columns where column_name = 'C_CRCS'

5.6数据导入5.7的数据库时报错：index column size too large. the maximum column size is 767 bytes

查看MySQL 5.7的默认参数配置：

mysql> SHOW VARIABLES LIKE ‘innodb_large_prefix’\G
*************************** 1. row ***************************
Variable_name: innodb_large_prefix
Value: ON
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE ‘innodb_file_format’\G
*************************** 1. row ***************************
Variable_name: innodb_file_format
Value: Barracuda
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE ‘innodb_file_per_table’\G
*************************** 1. row ***************************
Variable_name: innodb_file_per_table
Value: ON
1 row in set (0.01 sec)

查看MySQL 5.6的默认参数配置：

mysql> SHOW VARIABLES LIKE ‘innodb_large_prefix’\G
*************************** 1. row ***************************
Variable_name: innodb_large_prefix
Value: OFF
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE ‘innodb_file_format’\G
*************************** 1. row ***************************
Variable_name: innodb_file_format
Value: Antelope
1 row in set (0.01 sec)

mysql> SHOW VARIABLES LIKE ‘innodb_file_per_table’\G
*************************** 1. row ***************************
Variable_name: innodb_file_per_table
Value: ON
1 row in set (0.00 sec)

从以上比较可以看出5.7和5.6之间参数的默认配置区别，然后我们将5.6的参数配置成和5.7一致

mysql> SET GLOBAL innodb_large_prefix=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.00 sec)

mysql> use testA;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

CREATE TABLE `config_rate` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hospital_uscc` varchar(50) NOT NULL COMMENT '医院唯一代码',
  `score` varchar(11) NOT NULL COMMENT '分数 英文逗号分隔 ',
  `muser` varchar(50) NOT NULL DEFAULT '' COMMENT '最后修改人',
  `ctime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `utime` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_hu` (`hospital_uscc`) USING BTREE
) ENGINE=InnoDB COMMENT='权限评级表' ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.02 sec)

修改参数并且加上row_format=dynamic后表创建成功了。

innodb_large_prefix阿里云解释：

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

启用这个选项，对于使用动态和压缩行格式的InnoDB表，允许超过767字节(最多3072字节)的索引键前缀

Antelope是innodb-base的文件格式，支持ROW_FORMAT=COMPACT，ROW_FORMAT=REDUNDANT行格式，

Barracude是innodb-plugin后引入的文件格式，支持ROW_FORMAT=DYNAMIC，ROW_FORMAT=COMPRESSED格式，同时Barracude也支持Antelope文件格式

DYNAMIC增加了增强的长期可变长度列和支持大型索引键前缀的存储能力

https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html