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)

发布评论
IT虾米网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

关于MYSQL字符集问题(一)详解
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。