如何在MySQL 5.1中增加密钥长度? [英] How do I increase key length in MySQL 5.1?

问题描述

运行创建索引的Rails迁移时，我得到:

When running a Rails migration that creates an index, I get:

Mysql::Error: Specified key was too long; max key length is 1000 bytes: CREATE UNIQUE INDEX `index_matches_on_foo_and_bar_id_and_baz_id` ON `matches` (`foo`, `bar_id`, `baz_id`)

我需要设置哪个MySQL变量来增加此变量，在哪里设置它以使其影响所有会话，而不仅是当前的客户端会话?

What MySQL variable do I need to set to increase this, and where do I set it so it affects all sessions, and not just the current client session?

推荐答案

InnoDB实际上仅在索引中允许768个BYTES.另外，请注意，UTF-8编码的字符串每个字符占用3个字节，因此在这种情况下，索引中只能使用768/3个字符.

InnoDB actually only allows 768 BYTES in the index. Also, note that UTF-8 encoded strings take up 3 bytes per character, so you only have 768 / 3 chars to play with in the index in that case.

一个可能的解决方案是限制索引中字段使用的长度.但是，由于您还需要唯一索引，因此这可能不是您可以接受的解决方案.使用以下内容限制使用的字段的长度.

A possible solution is to limit the length of the field use in the index. However, since you also want a unique index, that might not be an acceptable solution for you. Use the following to limit the length of the fields used.

CREATE UNIQUE INDEX `index_matches_on_foo_and_bar_id_and_baz_id` ON `matches` (`foo`(100), `bar_id`(100), `baz_id`(100))

这篇关于如何在MySQL 5.1中增加密钥长度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！