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) |