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

MySQL版本5.6.35

在一个长度为512字符的字段上创建unique key报错

CREATE DATABASE dpcs_metadata DEFAULT CHARACTER SET utf8;
select * from information_schema.SCHEMATA;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | dpcs_metadata      | utf8                       | utf8_general_ci        | NULL     |
| def          | mysql              | utf8                       | utf8_bin               | NULL     |
| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | test               | utf8                       | utf8_bin               | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
5 rows in set (0.00 sec)

use dpcs_metadata;

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;

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

 MySQL用1到2个额外字节记录该字段的长度，当字段长度小于等于255时使用1个字节记录字段长度，当长度大于255时使用2~4个字节记录字段长度，字段file_path的长度为512*3+2=1538个字节，超过系统默认767字节数限制

select * from information_schema.character_sets where character_set_name in('latin1', 'utf8', 'utf8mb4');
+--------------------+----------------------+----------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION          | MAXLEN |
+--------------------+----------------------+----------------------+--------+
| latin1             | latin1_swedish_ci    | cp1252 West European |      1 |
| utf8               | utf8_general_ci      | UTF-8 Unicode        |      3 |
| utf8mb4            | utf8mb4_general_ci   | UTF-8 Unicode        |      4 |
+--------------------+----------------------+----------------------+--------+
3 rows in set (0.00 sec)

根据上图所示，采用utf8编码的字段最大长度为255个字符时，255*3+1=756，是小于最大767限制的，可以创建成功，如下

create table raw_log_meta_data(
       id bigint NOT NULL AUTO_INCREMENT,
       app_id varchar(64),
       user_id varchar(128),
       file_path varchar(256),
       device_id varchar(128),
       update_time DATETIME,
       PRIMARY KEY (id),
       UNIQUE KEY (user_id),
       UNIQUE KEY (file_path)
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

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

desc raw_log_meta_data;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| app_id      | varchar(64)  | YES  |     | NULL    |                |
| user_id     | varchar(128) | YES  | UNI | NULL    |                |
| file_path   | varchar(255) | YES  | UNI | NULL    |                |
| device_id   | varchar(128) | YES  |     | NULL    |                |
| update_time | datetime     | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

根据文档所述

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

Property	Value
Command-Line Format	--innodb-large-prefix[={OFF|ON}]
Introduced	5.6.3
System Variable	innodb_large_prefix
Scope	Global
Dynamic	Yes
Type	Boolean
Default Value	OFF

 

Enable this option to allow index key prefixes longer than 767 bytes (up to 3072 bytes) for InnoDB tables that use DYNAMIC or COMPRESSED row format. (Creating such tables also requires the option values innodb_file_format=barracuda and innodb_file_per_table=true.) See Section 14.6.1.6, “Limits on InnoDB Tables” for maximums associated with index key prefixes under various settings.

For tables that use REDUNDANT or COMPACT row format, this option does not affect the permitted index key prefix length.

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

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 theinnodb_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.

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

1. innodb_file_format=barracuda

2. innodb_file_per_table=true

3. ROW_FORMAT=DYNAMIC or COMPRESSED

mysql> show variables like 'innodb_large_prefix';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | OFF   |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_file_format';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| innodb_file_format | Antelope |
+--------------------+----------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

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> show variables like 'innodb_large_prefix';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_file_format';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| innodb_file_format | Barracuda |
+--------------------+-----------+
1 row in set (0.00 sec)

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)