JSW

Mysql错误：Specified key was too long; max key length is 767 bytes 解决方案

Mysql "Index column size too large. The maximum column size is 767 bytes

🕐

背景

解决 Mysql "Index column size too large. The maximum column size is 767 bytes."异常

ERROR 1709 (HY000) at line 213 in file: 'z_blog.sql': Index column size too large. The maximum column size is 767 bytes.

原因

由于MySQL的Innodb引擎表索引字段长度的限制为767`字节，因此对于多字节字符集的大字段或者多字段组合，创建索引时会出现此错误。

如果是utf8mb4字符集，由于utf8mb4是4字节字符集，则默认支持的索引字段最大长度是191字符（767字节/4字节每字符≈191字符），因此在varchar (255)或char (255) 类型字段上创建索引会失败。

如果是utf8字符集，由于utf8是3字节字符集，则默认支持的索引字段最大长度是255字符（767字节/3字节每字符≈255字符），则varchar (255)或char (255)不会失败。

处理步骤

添加到 my.cnf 中去

innodb_large_prefix = ON
innodb-file-per-table = ON
innodb_file_format = BARRACUDA

MariaDB的配置文件/etc/my.cnf.d/server.cnf中使用以下配置:

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
innodb_large_prefix = ON
innodb-file-per-table = ON
innodb_file_format = BARRACUDA

[client]
default-character-set=utf8mb4

重启mysq，查看结果

MariaDB [(none)]> SHOW GLOBAL VARIABLES WHERE variable_name IN ('innodb_file_format', 'innodb_large_prefix', 'innodb_file_per_table');
+-----------------------+-----------+
| Variable_name         | Value     |
+-----------------------+-----------+
| innodb_file_format    | Barracuda |
| innodb_file_per_table | ON        |
| innodb_large_prefix   | ON        |
+-----------------------+-----------+
3 rows in set (0.00 sec)

MariaDB [(none)]> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)

解决方案

show variables like 'innodb_large_prefix';
show variables like 'innodb_file_format';

-- 有问题的时候
create table test (id varchar(256),key (id));

--
set global innodb_large_prefix=1;
set global innodb_file_format=BARRACUDA;
create table test(id varchar(256),key (id)) row_format=dynamic;
create table test(id varchar(255),name varchar(255),name1 varchar(255),name2 varchar(255),name3 varchar(5),key (id,name,name1,name2,name3)) row_format=dynamic;

参考