mysql 更改 innodb_large_prefix
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35847015/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql change innodb_large_prefix
提问by yangsunny
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:
我只是在 VM 上设置了 debian 8.3 并在本教程之后安装了 xampp 。一切正常，直到我尝试创建一个新表：
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
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 limitationwhich is limited to 767Byte in Innodband 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.cnfI found is in /opt/lampp/etc/, however, after I added the innodb_large_prefix=1to the file and restarted lampp. I stil get the same error. What did I do wrong?
我收到错误：#1709 - Index column size too large. The maximum column size is 767 bytes.然后我发现这来自prefix limitation限制为 767Byte 的Innodb，我可以通过在 my.cnf 文件中设置 innodb_large_prefix 来解决这个问题。但是我找不到文件，它不在下面/etc/，也没有 - 文件夹，但是，在我将文件添加到文件并重新启动 lampp之后，我找到/etc/mysql/的唯一文件是 。我仍然遇到同样的错误。我做错了什么？my.cnf/opt/lampp/etc/innodb_large_prefix=1
edit: SELECT version()returns 5.6.14, so innodb_large_prefixshould be supported.
编辑：SELECT version()返回5.6.14，所以innodb_large_prefix应该支持。
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.
edit2：我知道我可以通过仅将键的一部分设置为索引来解决这个问题，以使其低于 767Byte。但我想在这里知道如何正确配置 mysql。
回答by Rick James
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:
在 5.6.3 和 5.7.7 之间（也就是说，如果您运行的是 MySQL 5.6 或 MariaDB 10.0），有 4 个步骤：
- SET GLOBAL innodb_file_format=Barracuda;
- SET GLOBAL innodb_file_per_table=ON;
- ROW_FORMAT=DYNAMIC; -- or COMPRESSED (goes on end of CREATE)
- innodb_large_prefix=1
- 设置全局 innodb_file_format=梭子鱼；
- 设置全局 innodb_file_per_table=ON;
- ROW_FORMAT=动态；-- 或 COMPRESSED（在 CREATE 结束时继续）
- innodb_large_prefix=1
Note
笔记
SELECT * FROM information_schema.INNODB_SYS_TABLESPACES;
will provide the file_format and row_format. Some other I_S tables provide clues of file_per_table.
将提供 file_format 和 row_format。其他一些 I_S 表提供了 file_per_table 的线索。
回答by Vladimir Salguero
I'm using Mysql 5.6.17 with WAMP Server I solved the problem by editing the my.ini file Find the category [mysqld] there add the following instructions
我在 WAMP 服务器上使用 Mysql 5.6.17 我通过编辑 my.ini 文件解决了这个问题找到类别 [mysqld] 那里添加以下说明
[mysqld]
innodb_file_format = Barracuda
innodb_large_prefix = 1
innodb_file_per_table = ON
Don't forget to save the changes and restart all services.
不要忘记保存更改并重新启动所有服务。
回答by Techifylogic
For a permanent solution, pls add following in your mariadb My.INI file-
对于永久解决方案，请在您的 mariadb My.INI 文件中添加以下内容-
## Innodb settings to bypass error of max size 737
innodb-file-format=barracuda
innodb-file-per-table=ON
innodb-large-prefix=ON
## Above 3 didnot work so i added below
innodb_default_row_format = 'DYNAMIC'
I was using 10.1.38
我使用的是 10.1.38
回答by Robert Hilson
Go to your xampp and add this query:
转到您的 xampp 并添加以下查询：
`mysql>` set global innodb_file_format = `BARRACUDA`;
`mysql>` set global innodb_large_prefix = `ON`;
回答by Arsath
mysql> set global innodb_file_format = `BARRACUDA`;
mysql> set global innodb_large_prefix = `ON`;
