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 = BARRACUDAMariaDB的配置文件/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;