Mysql::Error: 指定的键太长；最大密钥长度为 767 字节:CREATE INDEX

我正在使用 rails 2.3.5 应用程序，在女巫我有这个领域

t.string "trip_cities", :limit =>256

还有这个索引

add_index "bookings", ["trip_cities"], :name =>旅行城市"

当我尝试执行时:

bundle exec rake db:test:load

我收到这个错误 Mysql::Error: Specified key was too long;最大密钥长度为 767 字节:CREATE INDEX 'trip_cities' ON 'bookings' ('trip_cities') 并且不太知道如何解决这个问题.

解决方案

听起来默认排序规则使用 UTF8 字符集.

MySQL 按字节而不是字符限制键的长度.由于 MySQL 使用的 UTF8 实现允许每个字符 3 个字节，因此 UTF8 列上的键的最大长度是字符键长度的 3 倍(如果未明确指定，键长度是字段的全长).

在这种情况下，最大密钥长度为 256 * 3，即 768.您需要限制键的长度或更改列的排序规则.

I'm working with rails 2.3.5 application, in witch I have this field

t.string   "trip_cities",             :limit => 256

And this index

add_index "bookings", ["trip_cities"], :name => "trip_cities"

When I try to execute:

bundle exec rake db:test:load

I receive this error Mysql::Error: Specified key was too long; max key length is 767 bytes: CREATE INDEX 'trip_cities' ON 'bookings' ('trip_cities') and don't quite know how to resolve this.

解决方案

It sounds like the default collation uses the UTF8 character set.

MySQL limits the length of keys by bytes, not characters. Since the UTF8 implementation MySQL uses allows for 3 bytes per character, the max length of a key on a UTF8 column is 3 times the key length in characters (the key length is the full length of the field if not explicitly specified).

In this case the max key length would be 256 * 3 which is 768. You need to either limit the length of the key or change the collation of the column.