mysql 1071,MySQL 问题: 1071 (42000): Specified key was too long

今天在一台安装了 MySQL 5.5 的主机上, 建立资料表时, 出现以下错误:

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

要看到这个错误, 可以在 MySQL 5.5/5.6 (或 Mariadb) 上尝试执行以下指令建立数据库:

[root@localhost ~]# mysql -u root -p

MariaDB [(none)]> use opencli;

MariaDB [opencli]> create table test (

-> id int not null,

-> code_value1 varchar(500) not null,

-> code_value2 varchar(500) not null,

-> unique index unique_index (id, code_value1, code_value2)

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

以上 SQL Query 在 MySQL 5.7 执行并没有问题, 这个题题在 MySQL 5.6 的官方文件 “Limits on InnoDB Tables” 可以找到原因:

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 the innodb_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.

Attempting to use an index key prefix length that exceeds the limit returns an error. To avoid such errors in replication configurations, avoid enabling innodb_large_prefix on the master if it cannot also be enabled on slaves.

上面有提到可以开启 innodb_large_prefix 解决问题。在 MySQL 5.7, innodb_large_prefix 默认是开启, 而在 MySQL 5.5 及 MySQL 5.6 则默认是关闭, 而往后的 MySQL 版本会将 innodb_large_prefix 删除, 只会在开启的状态。关闭的原因主要是想与较旧的 MySQL 5.1 版本兼容。

要解决这个问题便要开启 innodb_large_prefix, 同时将 innodb_file_format 设定为 “barracuda” (默认是 “Antelope”), 以及资料表的 ROW_FORMAT 要设定为 “DYNAMIC”, 可以执行以下指令解决:

[root@localhost ~]# mysql -u root -p

MariaDB [(none)]> use opencli;

MariaDB [opencli]> set global innodb_file_format = BARRACUDA;

MariaDB [opencli]> set global innodb_large_prefix = ON;

MariaDB [opencli]> create table test (

-> id int not null,

-> code_value1 varchar(500) not null,

-> code_value2 varchar(500) not null,

-> unique index unique_index (id, code_value1, code_value2)

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

Query OK, 0 rows affected (0.00 sec)

以上设定只会暂时生效, 当 MySQL 重新启动后便会失效, 要保留在下次重新启动后有效, 需要修改 my.cnf:

vi /etc/my.cnf

在 [mysqld] 段落, 加入以下两行:

innodb-file-format = BARRACUDA

innodb-large-prefix = ON

这样下次重新启动 MySQL 后便会保留这两个设定。

你可能感兴趣的内容: