MySQL错误“Specified key was too long; max key length is 1000 bytes”的解决办法

经过查询才知道,是Mysql的字段设置的太长了,于是我把这两个字段的长度改了一下就好了。 

建立索引时,数据库计算key的长度是累加所有Index用到的字段的char长度后再按下面比例乘起来不能超过限定的key长度1000: 
latin1 = 1 byte = 1 character 
uft8 = 3 byte = 1 character 
gbk = 2 byte = 1 character 
举例能看得更明白些,以GBK为例: 
CREATE UNIQUE INDEX `unique_record` ON reports (`report_name`, `report_client`, `report_city`); 
其中report_name varchar(200), report_client varchar(200), report_city varchar(200) 
(200 + 200 +200) * 2 = 1200 > 1000,所有就会报1071错误,只要将report_city改为varchar(100)那么索引就能成功建立。 
如果表是UTF8字符集,那索引还是建立不了。
阅读更多

关于“Specified key was too long; max key length is 1000 bytes“问题的解决办法

04-15

大家好,我使用的数据库是mysql, 版本号5.7.11. 在新增一个表时报“Specified key was too long; max key length is 1000 bytesrn”的错误,网络上查找的方法大概是两种,一种是修改表的默认字符集,一种是修改定义表的长度。我现在奇怪的是,这个表定义是哪一个地方出错了?原因是什么?现摘抄表的定义和错误信息如下,请大家帮助看下是什么原因。谢谢!rnrn CREATE TABLE graph_local (rn id mediumint(8) unsigned NOT NULL auto_increment,rn graph_template_id mediumint(8) unsigned NOT NULL default '0',rn host_id mediumint(8) unsigned NOT NULL default '0',rn snmp_query_id mediumint(8) NOT NULL default '0',rn snmp_index varchar(255) NOT NULL default '',rn PRIMARY KEY (id),rn KEY host_id (host_id),rn KEY graph_template_id (graph_template_id),rn KEY snmp_query_id (snmp_query_id),rn KEY snmp_index (snmp_index)rn ) ENGINE=MyISAM COMMENT='Creates a relationship for each item in a custom graph.';rn rn[SQL] CREATE TABLE graph_local (rn id mediumint(8) unsigned NOT NULL auto_increment,rn graph_template_id mediumint(8) unsigned NOT NULL default '0',rn host_id mediumint(8) unsigned NOT NULL default '0',rn snmp_query_id mediumint(8) NOT NULL default '0',rn snmp_index varchar(255) NOT NULL default '',rn PRIMARY KEY (id),rn KEY host_id (host_id),rn KEY graph_template_id (graph_template_id),rn KEY snmp_query_id (snmp_query_id),rn KEY snmp_index (snmp_index)rn ) ENGINE=MyISAM COMMENT='Creates a relationship for each item in a custom graph.';rn[Err] 1071 - Specified key was too long; max key length is 1000 bytesrnrn

Hibenate无法建表:ERROR SchemaExport:275 - Specified key was too long; max key length is 767 bytes

12-03

hibernate.cfg.xml 文件:rn[code=Java]rnrnrnrn rn jdbc:mysql://localhost/hibernate_sessionrn com.mysql.jdbc.Driverrn rootrn rootrn org.hibernate.dialect.MySQLDialectrn truern rn rn rnrn[/code]rnrnUser.hbm.xml文件:rn[code=Java]rnrnrnrn rn rn rn rn rn rn rn rn rnrn[/code]rnrnUser文件:rn[code=Java]rnpackage com.bjsxt.hibernate;rnrnimport java.util.Date;rnrnpublic class User rn rn private String id;rn rn private String name;rn rn private String password;rn rn private Date createTime;rn rn private Date expireTime;rnrn public String getId() rn return id;rn rnrn public void setId(String id) rn this.id = id;rn rnrn public String getName() rn return name;rn rnrn public void setName(String name) rn this.name = name;rn rnrn public String getPassword() rn return password;rn rnrn public void setPassword(String password) rn this.password = password;rn rnrn public Date getCreateTime() rn return createTime;rn rnrn public void setCreateTime(Date createTime) rn this.createTime = createTime;rn rnrn public Date getExpireTime() rn return expireTime;rn rnrn public void setExpireTime(Date expireTime) rn this.expireTime = expireTime;rn rnrn[/code]rnrnExportDB.java文件rn[code=Java]rnpackage com.bjsxt.hibernate;rnrnimport org.hibernate.cfg.Configuration;rnimport org.hibernate.tool.hbm2ddl.SchemaExport;rnrnpublic class ExportDB rnrn public static void main(String[] args) rn rn //读取hibernate.cfg.xml文件rn Configuration cfg = new Configuration().configure();rn rn SchemaExport export = new SchemaExport(cfg);rn rn export.create(true, true);rn rnrnrn[/code]rnrn当执行ExportDB.java文件时,报错如下:rndrop table if exists Userrn13:01:24,981 WARN JDBCExceptionReporter:48 - SQL Warning: 1051, SQLState: 42S02rn13:01:24,990 WARN JDBCExceptionReporter:49 - Unknown table 'user'rncreate table User (id varchar(255) not null, name varchar(255), password varchar(255), createTime datetime, expireTime datetime, primary key (id))rn13:01:24,992 ERROR SchemaExport:274 - Unsuccessful: create table User (id varchar(255) not null, name varchar(255), password varchar(255), createTime datetime, expireTime datetime, primary key (id))rn[color=#FF0000]13:01:24,992 ERROR SchemaExport:275 - Specified key was too long; max key length is 767 bytes[/color]rn---------------------------------------------rnrn已经在mysql里手动建立了hibernate_first数据库,但不能自动创建表User;rn就是不明白哪里的key太大?如何改呢?rnrnrn

ORACE-01467 sort key too long

05-05

oracle 9i 高手看看:(下面的SELECT语句出现ORACE-01467 sort key too long,如何改正,当该select语句返回列数小于32列时没问题(rownum <= 31),但超过32列时,就出现01467的错误了????),下面的语句由存储过程生成,我直接把生成后语句贴出来了,rn rnrnrnselect SAMPLING_POINT, SAMP_ORDER, PRODUCT_NAME, NAME, SAMPLE_TYPE, UNITS_DISPLAY, TEST_ORDER_NUMBER, RESULT_ORDER_NUMBER ,max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-04-30 20:00:00' thenrnFORMATTED_ENTRY else '-' end) "2008-04-30 20:00",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-04-30 21:00:00' then FORMATTED_ENTRY else '-' end) "2008-04-30 21:00",rnmax(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-04-30 23:00:00' then FORMATTED_ENTRY else '-' end) "2008-04-30 23:00",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 00:00:00' thenrn FORMATTED_ENTRY else '-' end) "2008-05-01 00:00",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 01:00:00' then FORMATTED_ENTRY else '-' end) "2008-05-01 01:00",rnmax(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 02:00:00' then FORMATTED_ENTRY else '-' end) "2008-05-01 02:00",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 03:00:00' thenrn FORMATTED_ENTRY else '-' end) "2008-05-01 03:00",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 03:10:00' then FORMATTED_ENTRY else '-' end) "2008-05-01 03:10",rnmax(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 04:00:00' then FORMATTED_ENTRY else '-' end) "2008-05-01 04:00",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 05:00:00' thenrnFORMATTED_ENTRY else '-' end) "2008-05-01 05:00",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 06:00:00' then FORMATTED_ENTRY else '-' end) "2008-05-01 06:00",rnmax(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 07:00:00' then FORMATTED_ENTRY else '-' end) "2008-05-01 07:00",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 08:00:00' thenrnFORMATTED_ENTRY else '-' end) "2008-05-01 08:00",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 09:00:00' then FORMATTED_ENTRY else '-' end) "2008-05-01 09:00",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 09:50:00' then FORMATTED_ENTRY else '-' end) "2008-05-01 09:50",max(case whenrnTO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 09:55:00' then FORMATTED_ENTRY else '-' end) "2008-05-01 09:55",max(casernwhen TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 10:00:00' then FORMATTED_ENTRY else '-' end) "2008-05-01 10:00",rnmax(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 10:05:00' then FORMATTED_ENTRY else '-' end) "2008-05-01 10:05",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 10:30:00' thenrn FORMATTED_ENTRY else '-' end) "2008-05-01 10:30",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 11:00:00' then FORMATTED_ENTRY else '-' end) "2008-05-01 11:00",rnmax(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 12:00:00' then FORMATTED_ENTRY else '-' end) "2008-05-01 12:00",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 13:00:00' thenrnFORMATTED_ENTRY else '-' end) "2008-05-01 13:00",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 13:20:00' then FORMATTED_ENTRY else '-' end) "2008-05-01 13:20",rnmax(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 14:00:00' then FORMATTED_ENTRY else '-' end) "2008-05-01 14:00",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 15:00:00' thenrnFORMATTED_ENTRY else '-' end) "2008-05-01 15:00",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 16:00:00' then FORMATTED_ENTRY else '-' end) "2008-05-01 16:00",rnmax(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 17:00:00' then FORMATTED_ENTRY else '-' end) "2008-05-01 17:00",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 18:00:00' thenrn FORMATTED_ENTRY else '-' end) "2008-05-01 18:00",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 19:00:00' thenrnFORMATTED_ENTRY else '-' end) "2008-05-01 19:00",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 19:10:00' then FORMATTED_ENTRY else '-' end) "2008-05-01 19:10",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 20:00:00' thenrnFORMATTED_ENTRY else '-' end) "2008-05-01 20:00",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 20:30:00' thenrnFORMATTED_ENTRY else '-' end) "2008-05-01 20:30",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 21:00:00' then FORMATTED_ENTRY else '-' end) "2008-05-01 21:00",rnmax(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 22:00:00' then FORMATTED_ENTRY else '-' end) "2008-05-01 22:00",max(case when TO_CHAR(SAMPLED_DATE,'YYYY-MM-DD HH24:MI:SS')='2008-05-01 23:00:00' thenrnFORMATTED_ENTRY else '-' end) "2008-05-01 23:00" from ( SELECT LIMS_WEB.SAMPLING_POINT, LIMS_WEB.SAMP_ORDER, LIMS_WEB.PRODUCT_NAME, LIMS_WEB.NAME, LIMS_WEB.SAMPLED_DATE, LIMS_WEB.SAMPLE_TYPE,rn LIMS_WEB.UNITS_DISPLAY, LIMS_WEB.FORMATTED_ENTRY, LIMS_WEB.TEST_ORDER_NUMBER, LIMS_WEB.RESULT_ORDER_NUMBER FROM ( SELECT B.sampled_date FROM ( SELECT distinct A.sampled_date FROM LIMS_WEB A WHERE A.sampled_date < TO_DATE('2008-05-01','YYYY-MM-DD') + 1rnAND A.sampled_date >= TO_DATE('2008-05-01','YYYY-MM-DD') - 30 AND A.PROD_EQUIP='1号' AND A.Workshop_Section = '1号' ORDER BY A.sampled_date DESC ) B WHERE rownum <= 35) SD,rnLIMS_WEB WHERE LIMS_WEB.SAMPLED_DATE = SD.SAMPLED_DATE AND LIMS_WEB.PROD_EQUIP='1号' AND LIMS_WEB.Workshop_Section = '1号' ORDER BY LIMS_WEB.SAMP_ORDER, LIMS_WEB.SAMPLING_POINT,rnLIMS_WEB.TEST_ORDER_NUMBER, LIMS_WEB.RESULT_ORDER_NUMBER ) T group by SAMPLING_POINT, SAMP_ORDER, PRODUCT_NAME, NAME, SAMPLE_TYPE, UNITS_DISPLAY, TEST_ORDER_NUMBER, RESULT_ORDER_NUMBER ORDER BY SAMP_ORDER,rnSAMPLING_POINT, TEST_ORDER_NUMBER, RESULT_ORDER_NUMBERrnrnrn

没有更多推荐了,返回首页