MySQL 5.7 Specified key was too long报错

相关参数说明

innodb_large_prefix

Command-Line Format	`--innodb-large-prefix=ON`
Deprecated	Yes
System Variable	`innodb_large_prefix`
Scope	Global
Dynamic	Yes
Type	Boolean
Default Value	`ON`

When this option is enabled, index key prefixes longer than 767 bytes (up to 3072 bytes) are allowed for InnoDB tables that use DYNAMIC or COMPRESSED row format.

innodb_file_format

Command-Line Format	`--innodb-file-format=value`
Deprecated	Yes
System Variable	`innodb_file_format`
Scope	Global
Dynamic	Yes
Type	String
Default Value	`Barracuda`
Valid Values	`Antelope` `Barracuda`

Enables an InnoDB file format for file-per-table tablespaces. Supported file formats are Antelope and Barracuda. Antelope is the original InnoDB file format, which supports REDUNDANT and COMPACT row formats. Barracuda is the newer file format, which supports COMPRESSED and DYNAMIC row formats.

当前参数查询

mysql> show variables where Variable_name in ('innodb_file_format','innodb_file_per_table','innodb_large_prefix','innodb_default_row_format');
+---------------------------+-----------+
| Variable_name             | Value     |
+---------------------------+-----------+
| innodb_default_row_format | dynamic   |
| innodb_file_format        | Barracuda |
| innodb_file_per_table     | ON        |
| innodb_large_prefix       | ON        |
+---------------------------+-----------+
4 rows in set (0.00 sec)

报错模拟

Specified key was too long; max key length is 767 bytes

如果将innodb_large_prefix关闭，索引长度限制767个字节

utf8字符集下，一个字符最多占用3字节，则可创建255个字符的索引，767//3=255
utf8mb4字符集下，一个字符最多可占用4字节，则可创建191个字符的索引，767//4=191

utf8字符集下测试

mysql> set global innodb_large_prefix=off;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists test_t;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test_t(col1 varchar(255), key(col1)) charset=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table if exists test_t;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test_t(col1 varchar(256), key(col1)) charset=utf8;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

utf8mb4字符集下测试

mysql> drop table if exists test_t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table test_t(col1 varchar(191), key(col1)) charset=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table if exists test_t;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test_t(col1 varchar(192), key(col1)) charset=utf8mb4;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Specified key was too long; max key length is 3072 bytes

innodb_large_prefix开启，索引可以超过767bytes，但是还是不能超过3072bytes

mysql> set global innodb_large_prefix=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables where Variable_name in ('innodb_file_format','innodb_file_per_table','innodb_large_prefix','innodb_default_row_format');
+---------------------------+-----------+
| Variable_name             | Value     |
+---------------------------+-----------+
| innodb_default_row_format | dynamic   |
| innodb_file_format        | Barracuda |
| innodb_file_per_table     | ON        |
| innodb_large_prefix       | ON        |
+---------------------------+-----------+
4 rows in set (0.01 sec)

innodb_large_prefix开启

utf8字符集下，一个字符最多占用3字节，则可创建1024个字符的索引，3072/3=1024
utf8mb4字符集下，一个字符最多可占用4字节，则可创建768个字符的索引，3072/4=768

utf8字符集下测试

mysql> drop table if exists test_t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table test_t(col1 varchar(1024), key(col1)) charset=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table if exists test_t;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test_t(col1 varchar(1025), key(col1)) charset=utf8;
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

utf8mb4字符集下测试

mysql> drop table if exists test_t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table test_t(col1 varchar(768), key(col1)) charset=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table if exists test_t;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test_t(col1 varchar(769), key(col1)) charset=utf8mb4;
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes