Specified key was too long; max key length is 1000

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；