Chinaunix首页 | 论坛 | 博客
  • 博客访问: 68880
  • 博文数量: 15
  • 博客积分: 638
  • 博客等级: 上士
  • 技术积分: 155
  • 用 户 组: 普通用户
  • 注册时间: 2007-09-26 23:13
文章分类

全部博文(15)

文章存档

2012年(1)

2011年(1)

2009年(13)

分类: Mysql/postgreSQL

2009-10-18 00:40:28

ERROR 1071 (42000) at line 1481952: Specified key was too long; max key length is 1000 bytes
 
at the line:
mysql> CREATE TABLE Smdr_ISM_ifUti_Raw (
    ->   location varchar(16) NOT NULL default '',
    ->   sequence int(11) NOT NULL default '0',
    ->   startTime int(11) NOT NULL default '0',
    ->   period int(11) NOT NULL default '0',
    ->   timeZone int(11) NOT NULL default '0',
    ->   ProcessTime int(11) NOT NULL default '0',
    ->   MonitorLocation varchar(64) default NULL,
    ->   Service varchar(64) default NULL,
    ->   IsmProfile varchar(64) default NULL,
    ->   DEVICENAME varchar(64) default NULL,
    ->   MANAGERIP varchar(64) default NULL,
    ->   PORT varchar(255) default NULL,
    ->   OidGroup varchar(128) default NULL,
    ->   ServiceLevelString varchar(32) default NULL,
    ->   ResultMessage varchar(64) default NULL,
    ->   ServiceLevel double NOT NULL default '0',
    ->   ifInSpeedAvg double NOT NULL default '0',
    ->   ifOutSpeedAvg double NOT NULL default '0',
    ->   ifInSpeedMax double NOT NULL default '0',
    ->   ifOutSpeedMax double NOT NULL default '0',
    ->   TotalTime double NOT NULL default '0',
    ->   NumberPolls double NOT NULL default '0',
    ->   Svc int(11) default '0',
    ->   PRIMARY KEY  (location,sequence,startTime,period,timeZone,ProcessTime),    ->   KEY ISMifUtiRawstp0 (timeZone,period,startTime),
    ->   KEY ISMifUtiRawcst0 (DEVICENAME,MANAGERIP,PORT)
    -> ) TYPE=MyISAM;
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
 
mysql> show variables like 'collation_%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | utf8_general_ci   |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.02 sec)
mysql> show variables like 'character_set_%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
7 rows in set (0.00 sec)

 
==============================================================================
mysql> CREATE TABLE Smdr_ISM_ifUti_Raw (
    ->   location varchar(16) NOT NULL default '',
    ->   sequence int(11) NOT NULL default '0',
    ->   startTime int(11) NOT NULL default '0',
    ->   period int(11) NOT NULL default '0',
    ->   timeZone int(11) NOT NULL default '0',
    ->   ProcessTime int(11) NOT NULL default '0',
    ->   MonitorLocation varchar(64) default NULL,
    ->   Service varchar(64) default NULL,
    ->   IsmProfile varchar(64) default NULL,
    ->   DEVICENAME varchar(64) default NULL,
    ->   MANAGERIP varchar(64) default NULL,
    ->   PORT varchar(255) default NULL,
    ->   OidGroup varchar(128) default NULL,
    ->   ServiceLevelString varchar(32) default NULL,
    ->   ResultMessage varchar(64) default NULL,
    ->   ServiceLevel double NOT NULL default '0',
    ->   ifInSpeedAvg double NOT NULL default '0',
    ->   ifOutSpeedAvg double NOT NULL default '0',
    ->   ifInSpeedMax double NOT NULL default '0',
    ->   ifOutSpeedMax double NOT NULL default '0',
    ->   TotalTime double NOT NULL default '0',
    ->   NumberPolls double NOT NULL default '0',
    ->   Svc int(11) default '0',
    ->   PRIMARY KEY  (location,sequence,startTime,period,timeZone,ProcessTime),    ->   KEY ISMifUtiRawstp0 (timeZone,period,startTime),
    ->   KEY ISMifUtiRawcst0 (DEVICENAME,MANAGERIP,PORT)
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.15 sec)
mysql> show variables like 'character_set_%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
7 rows in set (0.12 sec)
mysql> SHOW VARIABLES LIKE 'collation_%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
 
==========================================================
 
 
原因分析和解决 
latin1 = 1 byte = 1 character
uft8 = 3 byte = 1 character
gbk = 2 byte = 1 character
utf8编码占用字节多,key的长度超了1000,此例中换成gbk就ok了(407*2<1000<407*3)。
 mysql>alter database SM_DB character set gbk;
 
 
阅读(5715) | 评论(1) | 转发(0) |
给主人留下些什么吧!~~

chinaunix网友2011-05-07 17:14:47

这个是主要原因吗 http://01yun.com/jssx/sjk/mysql/20110409/622.html