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

MySQL>  use test; create table test(a varchar(512) primary key, b varchar(1024));
Database changed
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

 

drop table if exists test; 
create table test(test varchar(767) primary key)charset=latin5; 
-- 成功 

drop table if exists test; 
create table test(test varchar(768) primary key)charset=latin5; 
-- 错误  
-- ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes 

drop table if exists test; 
create table test(test varchar(383) primary key)charset=GBK; 
-- 成功 

drop table if exists test; 
create table test(test varchar(384) primary key)charset=GBK; 
-- 错误  
-- ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes 

drop table if exists test; 
create table test(test varchar(255) primary key)charset=UTF8; 
-- 成功 

drop table if exists test; 
create table test(test varchar(256) primary key)charset=UTF8; 
-- 错误  
-- ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

原因：
　　MySQL的varchar主键只支持不超过768个字节,或者768/2=384个双字节,或者768/3=256个三字节的字段,而GBK是双字节的，UTF-8是三字节的。

变量设置：

mysql> show variables like '%char%';
+--------------------------+--------------------------------------------------+
| Variable_name            | Value                                            |
+--------------------------+--------------------------------------------------+
| character_set_client     | latin1                                           |
| character_set_connection | latin1                                           |
| character_set_database   | utf8                                             |
| character_set_filesystem | binary                                           |
| character_set_results    | latin1                                           |
| character_set_server     | utf8                                             |
| character_set_system     | utf8                                             |
| character_sets_dir       | /opt/schooner/ac_3.1/mysql/share/mysql/charsets/ |
+--------------------------+--------------------------------------------------+
8 rows in set (0.00 sec)

转载于:https://www.cnblogs.com/jing99/p/7768286.html