MySQL更改innodb_large_prefix [英] mysql change innodb_large_prefix

问题描述

我只是在VM上安装了debian 8.3，并在教程之后安装了xampp.一切正常，直到我尝试创建新表:

I just setup debian 8.3 on a VM and installed xampp after this Tutorial. Everything is working, until I tried to create a new table:

create table testtable
(
  id int(10) not null auto_increment,
  firstname varchar(255) collate utf8mb4_german2_ci not null,
  lastname varchar(255) collate utf8mb4_german2_ci not null,
  primary key (id),
  unique key (lastname)
)engine = innodb default charset=utf8mb4, collate=utf8mb4_german2_ci

我得到了错误:#1709 - Index column size too large. The maximum column size is 767 bytes. 然后我发现这来自prefix limitation，它在Innodb中限制为767Byte，我可以通过在my.cnf文件中设置innodb_large_prefix来解决此问题.但是我找不到文件，它不在/etc/下，也没有/etc/mysql/文件夹，但是我发现的唯一my.cnf在/opt/lampp/etc/中，但是，在将innodb_large_prefix=1添加到文件中并重新启动lampp.我仍然得到同样的错误.我做错什么了?

I got the error: #1709 - Index column size too large. The maximum column size is 767 bytes. Then I found out this comes from the prefix limitation which is limited to 767Byte in Innodb and I can fix this by set the innodb_large_prefix in the my.cnf file. But I can't find the file, its not under /etc/ and theres no /etc/mysql/-folder, the only my.cnf I found is in /opt/lampp/etc/, however, after I added the innodb_large_prefix=1 to the file and restarted lampp. I stil get the same error. What did I do wrong?

编辑:SELECT version()返回5.6.14，因此应支持innodb_large_prefix.

edit: SELECT version() returns 5.6.14, so innodb_large_prefix should be supported.

edit2 :我知道我可以通过仅将部分密钥设置为索引来获得低于767Byte的方法来解决此问题.但我想在这里知道如何正确配置mysql.

edit2: I know I can work around this by only set part of the the key as index to get under 767Byte. But I want to know here how to config the mysql correctly.

推荐答案

在5.6.3和5.7.7之间(也就是说，如果您正在运行MySQL 5.6或MariaDB 10.0)，有4个步骤:

Between 5.6.3 and 5.7.7 (that is if you are running MySQL 5.6 or MariaDB 10.0), there are 4 steps:

• SET GLOBAL innodb_file_format =梭子鱼;
• 设置全局innodb_file_per_table = ON;
• ROW_FORMAT =动态-或压缩(在CREATE结束)
• innodb_large_prefix = 1

注意

SELECT * FROM information_schema.INNODB_SYS_TABLESPACES;

将提供file_format和row_format.其他一些I_S表提供了file_per_table的线索.

will provide the file_format and row_format. Some other I_S tables provide clues of file_per_table.

这篇关于MySQL更改innodb_large_prefix的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！