错误:指定的键太长;最大密钥长度是1000字节。

script/generate acts_as_taggable_on_migration
rake db:migrate

causes

原因

Mysql::Error: Specified key was too long; max key length is 1000 bytes: CREATE  INDEX `index_taggings_on_taggable_id_and_taggable_type_and_context` ON `taggings` (`taggable_id`, `taggable_type`, `context`)

What should I do?

我应该做什么?

Here is my database encoding:

这是我的数据库编码:

mysql> SHOW VARIABLES LIKE 'character\_set\_%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | latin1 | 
| character_set_connection | latin1 | 
| character_set_database   | utf8   | 
| character_set_filesystem | binary | 
| character_set_results    | latin1 | 
| character_set_server     | latin1 | 
| character_set_system     | utf8   | 
+--------------------------+--------+
7 rows in set (0.00 sec)

5 个解决方案

#1

This is solely a MySQL issue -

这只是一个MySQL问题。

MySQL has different engines - MyISAM, InnoDB, Memory...

MySQL有不同的引擎- MyISAM, InnoDB，内存…

MySQL has different limits on the amount of space you can use to define indexes on column(s) - for MyISAM it's 1,000 bytes; it's 767 for InnoDB. And the data type of those columns matters - for VARCHAR, it's 3x so an index on a VARCHAR(100) will take 300 of those bytes (because 100 characters * 3 = 300).

MySQL对用于在列上定义索引的空间数量有不同的限制，对MyISAM来说，它是1,000字节;这是767年为InnoDB。这些列的数据类型很重要——对于VARCHAR，它是3x，所以VARCHAR(100)上的索引将占用300个字节(因为100个字符* 3 = 300)。

To accommodate some indexing when you hit the ceiling value, you can define the index with regard to portions of the column data type:

为了适应某些索引，当您达到上限值时，您可以在列数据类型的部分中定义索引:

CREATE INDEX example_idx ON YOUR_TABLE(your_column(50))

Assuming that your_column is VARCHAR(100), the index in the example above will only be on the first 50 characters. Searching for data beyond the 50th character will not be able to use the index.

假设your_column是VARCHAR(100)，上面示例中的索引仅在前50个字符上。搜索超过第50个字符的数据将不能使用索引。

#2

if this error occur in some proccess like migration, it could be solved by changing config file of MySql (*.ini)

如果在迁移等过程中出现此错误，可以通过修改MySql配置文件(*.ini)来解决。

default-storage-engine=InnoDB

#3

I think one of your fields is a varchar with more than 1000 chars. e.g. context?

我认为你的一个领域是一个有超过1000个字符的varchar。如背景?

Think about the meaning of an index. It's quick access to a row when all your indexed fields are within the where clause. If an index is to long (in case of mysql more than 1000 bytes), it makes no sense to use an index, because it's probably slower than accessing the complete table with a full table scan.

想想指数的含义。当所有索引字段都在where子句中时，它可以快速访问一行。如果索引是长(在mysql超过1000字节的情况下)，使用索引是没有意义的，因为它可能比用全表扫描访问完整的表要慢。

I would suggest to shorten the index, e.g to taggable_id and taggable_type, if those both are the shorter once.

我建议缩短索引，e。g到taggable_id和taggable_type，如果它们都是短的一次。

Cheers - Gerhard

欢呼——格哈德

#4

This seems to be a bug that was reported here: http://bugs.mysql.com/bug.php?id=4541

这似乎是在这里报告的一个bug: http://bugs.mysql.com/bug.php?id=4541。

If you have tried all the answers on this post and still getting the error, you may want to try to run this command on your SQL query window.

如果您已经尝试了这篇文章的所有答案并且仍然得到了错误，那么您可能想尝试在SQL查询窗口中运行这个命令。

set GLOBAL storage_engine='InnoDb';

#5

-2

I had this problem, so my solution was this:

我有这个问题，所以我的解决办法是:

alter table robs_temp.missing_email change email email varchar(300);
ALTER TABLE robs_temp.missing_email add primary key (email); -- Now it works.

According to wikipedia valid emails can't be over 256 characters. Perhaps your data has some upper limit.

根据*，有效的电子邮件不能超过256个字符。也许你的数据有一些上限。

#1