欢迎各位兄弟 发布技术文章

这里的技术是共享的

You are here

首页

1071 - Specified key was too long; max key length is 1000 bytes [duplicate] 有大用

星期三, 2017-02-08 16:04 — adminshiping1

MySQL Database Migration fails with Specified key was too long max key length is 1000 bytes

 

 

Symptoms

 

Getting error below inside fisheye-debug.log when migrating database to MySQL server:

    
1
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 1000 bytes
2
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
   

Cause

 

There is a known bug with MySQL related to MyISAM, the UTF8 character set and indexes that you can check here.

Resolution

 

  1. Make sure MySQL is configured with the InnoDB storage engine.

  2. Change the storage engine used by default so that new tables will always be created appropriately:

       
    1
    set GLOBAL storage_engine='InnoDb';
       

    For MySQL 5.6 and later, use the following:

    mysql> SET GLOBAL default_storage_engine = 'InnoDB';

  3. And finally make sure that you're following the instructions provided in Migrating to MySQL.


1071 - 指定的密钥太长; 最大密钥长度为1000字节[重复]
 
达票0向下投票最喜爱的 

这个问题已经有一个答案在这里：

我得到以下错误当我导入Sqlbackup我有：

SQL-Befehl：

CREATE TABLE jos_hikashop_zone_link (
    zone_parent_namekey VARCHAR(255) NOT NULL,
    zone_child_namekey VARCHAR(255) NOT NULL,
    PRIMARY KEY (zone_parent_namekey, zone_child_namekey)
)
ENGINE = MYISAM
AVG_ROW_LENGTH = 49
CHARACTER SET utf8
COLLATE utf8_general_ci
MySQL meldet: Dokumentation

1071 - 指定的密钥太长; 最大密钥长度为1000字节

我试图“设置GLOBAL storage_engine ='InnoDb'”之前，我导入的文件。

我可以不得到一个新的SQLImportfile。

我在本地机器上使用XAMPP。

分享改善这个问题

标志着由重复的社区♦ 在14:11 10月19日'16

此问题已标记为与现有问题的完全重复。

 
   
您指定了一个包含510个字符的组合键，但每个UTF-8字符可以大到4个字节，因此您要求的大小超过2K的键，MySQL不喜欢。缩小复合键的大小以继续。 -  蒂姆Biegeleisen 10月18日在'16 13:42
   
我怎么能这样做？ -  丹尼尔Lenzendorf 10月18日在'16 13:43

1答案  正确答案

达票0向下投票接受


由于您的主键是在两个UTF8 VARCHAR（255）列上的多列主键，索引大小是两个列的大小，添加在一起。

UTF8列上的索引自动分配所有可能的空间，其中每个字符最多可占用3个字节。因此，您的索引大小为255个字符* 3字节* 2列= 1530字节。

MyISAM对索引的限制为1000字节。InnoDB有一个更小的限制（767字节），除非你在MySQL 5.7.7+，在这种情况下的限制是3072字节默认。

尝试减少索引大小。通常，索引大小越小，搜索性能就越好。
 

 

 
up vote0down votefavorite 

This question already has an answer here:

I get following error when I Import the Sqlbackup i have:

SQL-Befehl:

CREATE TABLE jos_hikashop_zone_link (
    zone_parent_namekey VARCHAR(255) NOT NULL,
    zone_child_namekey VARCHAR(255) NOT NULL,
    PRIMARY KEY (zone_parent_namekey, zone_child_namekey)
)
ENGINE = MYISAM
AVG_ROW_LENGTH = 49
CHARACTER SET utf8
COLLATE utf8_general_ci
MySQL meldet: Dokumentation

1071 - Specified key was too long; max key length is 1000 bytes

I have tried to "set GLOBAL storage_engine='InnoDb'" before I Import the file.

I can`t get a new SQLImportfile.

I am using XAMPP on a local machine.

shareimprove this question

marked as duplicate by Community♦ Oct 19 '16 at 14:11

This question was marked as an exact duplicate of an existing question.

 
   
You specified a composite key of 510 characters, but each UTF-8 character could be as large as 4 bytes, hence you are requesting a key over 2K in size, which MySQL doesn't like. Shrink the size of your composite key in order to proceed. – Tim Biegeleisen Oct 18 '16 at 13:42
   
how can i do this? – Daniel Lenzendorf Oct 18 '16 at 13:43

1 Answer  正确答案

up vote0down voteaccepted

Because your primary key is a multi-column primary key on both UTF8 VARCHAR(255) columns, the index size is the size of both columns, added together.

Indexes on UTF8 columns automatically allocate all possible space, where each character could take up to 3 bytes. Therefore, your index size is 255 characters * 3 bytes * 2 columns = 1530 bytes.

MyISAM has a limit of 1000 bytes for indexes. InnoDB has an even smaller limit (767 bytes) unless you're on MySQL 5.7.7+, in which case the limit is 3072 bytes by default.

Try to reduce your index size. Typically, the smaller your index size, the better your seek performance will be.


来自  http://stackoverflow.com/questions/40109903/1071-specified-key-was-too-long-max-key-length-is-1000-b...


普通分类: 
mysql
Powered by Drupal

友情链接

校园好文网

 www.shipingzhong.cn  个人技术网_前端_后台    备案号:苏ICP备18010659号-2