Mysql之Specified key was too long; max key length is 767 bytes-yellowcong

版权声明:本文为博主yellowcong原创文章,未经博主允许不得转载。 https://blog.csdn.net/yelllowcong/article/details/79980511

今天导数据库的时候,抽风,报了这个错,当时找了资料,也没太好用啊,导致这个问题的原因索引的字段都太长了,mysql受不了,解决方法就是,让mysql支持比较长的索引,然后在插入表的时候,添加ROW_FORMAT=DYNAMIC ,自动格式化索引。

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

这里写图片描述

查看数据库信息

show variables like 'innodb_large_prefix';  
show variables like 'innodb_file_format';

--修改最大索引长度限制
set global innodb_large_prefix=1; 
set global innodb_file_format=BARRACUDA;
-- 添加
set global innodb_file_format_max=BARRACUDA;

这里写图片描述

这里写图片描述

2、修改插入sql的语句添加ROW_FORMAT=DYNAMIC

create table idx_length_test_02
(
  id int auto_increment primary key,
  name varchar(255)
) 
ROW_FORMAT=DYNAMIC default charset utf8mb4;

这里写图片描述

参考文章

https://help.aliyun.com/knowledge_detail/41707.html

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

关于“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

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

ORA-01467: sort key too long的的问题?在线等...........

05-08

select r1.city_code,r1.city_name,r2.* fromrn(SELECT sum(decode(b.sale_code,00002607,1,0)*decode(c.brand_code,10002,1,0)*decode(c.style_code,10043,1,0)) a00002607_10002_10043 , sum(decode(b.sale_code,00003921,1,0)*decode(c.brand_code,10002,1,0)*decode(c.style_code,10043,1,0)) a00003921_10002_10043 , sum(decode(b.sale_code,00003931,1,0)*decode(c.brand_code,10002,1,0)*decode(c.style_code,10043,1,0)) a00003931_10002_10043 , sum(decode(b.sale_code,00004207,1,0)*decode(c.brand_code,10002,1,0)*decode(c.style_code,10043,1,0)) a00004207_10002_10043 , sum(decode(b.sale_code,00004210,1,0)*decode(c.brand_code,10002,1,0)*decode(c.style_code,10043,1,0)) a00004210_10002_10043 , sum(decode(b.sale_code,00004214,1,0)*decode(c.brand_code,10002,1,0)*decode(c.style_code,10043,1,0)) a00004214_10002_10043 , sum(decode(b.sale_code,00004216,1,0)*decode(c.brand_code,10002,1,0)*decode(c.style_code,10043,1,0)) a00004216_10002_10043 , sum(decode(b.sale_code,00004219,1,0)*decode(c.brand_code,10002,1,0)*decode(c.style_code,10043,1,0)) a00004219_10002_10043 from tb_mobile_stock c,sphonesalcfg b,tb_sal_imei a where a.imei=c.imei and b.sale_code = a.sale_code group by c.region_code) r2,(select c.region_code city_code,count(*) sumall from tb_mobile_stock c,sphonesalcfg b,tb_sal_imei a where a.imei=c.imei and b.sale_code = a.sale_code group by c.region_code)r3 where r1.city_code=r2.city_code(+) and r1.city_code=r3.city_code(+) and r2.city_code=r3.city_code rn由于我有很多的sum语句,所以出现了这个错误,有什么好的方法可以解决吗?

mysql :Data too long for column

10-23

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'truename' at row 1rn at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3374)rn at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3308)rn at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1837)rn at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)rn at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2543)rn at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1737)rn at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:998)rn at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)rn at dao.RegeditDao.add_member_info(RegeditDao.java:100)rn at dao.RegeditDao.reg(RegeditDao.java:169)rn at com.jzgc.struts.action.RegeditAction.add(RegeditAction.java:45)rn at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)rn at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)rn at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)rn at java.lang.reflect.Method.invoke(Unknown Source)rn at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:270)rn at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:187)rn at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)rn at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)rn at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)rn at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)rn at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)rn at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)rn at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)rn at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)rn at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:228)rn at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)rn at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)rn at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)rn at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)rn at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:216)rn at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)rn at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:634)rn at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:445)rn at java.lang.Thread.run(Unknown Source)

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

私密
私密原因:
请选择设置私密原因
  • 广告
  • 抄袭
  • 版权
  • 政治
  • 色情
  • 无意义
  • 其他
其他原因:
120
出错啦
系统繁忙,请稍后再试