今天测试遇到了这样的问题:
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
相关测试:
http://kklinux.com/bbs/archiver/tid-2543.html
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%';
+--------------------------+