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)
原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/3791.html