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

参考资料

InnoDB Limits

https://dev.mysql.com/doc/refman/5.7/en/innodb-limits.html

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

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_file_format

MySQL 经典案例分析:Specified key was too long

https://cloud.tencent.com/developer/article/1005696

https://blog.csdn.net/alphonse_10/article/details/135480064

https://www.cnblogs.com/igoodful/p/11678695.html

posted @ 2025-06-03 00:28  kahnyao  阅读(157)  评论(0)    收藏  举报