MySQL5.6出现ERROR 1709 (HY000): Index column size too large问题的解决方法

一、问题

mysql 5.6 出现如下问题：

[ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.]

二、解决

根据文档所述

https://dev.mysql.com/doc/refman/5.6/en/create-index.html

Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 767 bytes long for InnoDB tables or 3072 bytes if the innodb_large_prefix option is enabled. For MyISAM tables, the prefix length limit is 1000 bytes.

https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix

innodb_large_prefix

根据以上可知，如果需要在长度大于255字符的字段上创建索引，需要修改以下3个参数

1. innodb_file_format=barracuda

2. innodb_file_per_table=true
3. ROW_FORMAT=DYNAMIC or COMPRESSED

更改参数：

mysql> set global innodb_file_format = BARRACUDA;
mysql> set global innodb_large_prefix = ON;

在创建表时，需要 加 ROW_FORMAT=DYNAMIC 参数：

create table raw_log_meta_data(
       id bigint NOT NULL AUTO_INCREMENT,
       app_id varchar(64),
       user_id varchar(128),
       file_path varchar(512),
       device_id varchar(128),
       update_time DATETIME,
       PRIMARY KEY (id),
       UNIQUE KEY (user_id),
       UNIQUE KEY (file_path)
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.29 sec)