Mysql::Error: 指定的键太长；最大密钥长度为 1000 字节 [英] Mysql::Error: Specified key was too long; max key length is 1000 bytes

问题描述

script/generate acts_as_taggable_on_migration
rake db:migrate

原因

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

我该怎么办?

这是我的数据库编码:

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)

推荐答案

这完全是 MySQL 的问题 -

This is solely a MySQL issue -

MySQL 有不同的引擎——MyISAM、InnoDB、Memory...

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

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

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

为了在达到上限时容纳一些索引，您可以定义关于列数据类型部分的索引:

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

假设 your_column 是 VARCHAR(100)，则上面示例中的索引将仅位于前 50 个字符上.搜索超过第 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.

这篇关于Mysql::Error: 指定的键太长；最大密钥长度为 1000 字节的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！