MySQL 错误：最大列大小为 767 字节
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30761867/
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 error: The maximum column size is 767 bytes
提问by user3570615
When I run a program which does something with MySQL, I got this error message:
当我运行一个对 MySQL 执行某些操作的程序时，我收到以下错误消息：
2015-06-10 15:41:12,250 ERROR app.wsutils 419 INCRON: Error: ('HY000', '[HY000] [MySQL][ODBC 5.2(w) Driver][mysqld-5.7.7-rc-log]Index column size too large. The maximum column size is 767 bytes. (1709) (SQLExecDirectW)')
2015-06-10 15:41:12,250 错误 app.wsutils 419 INCRON: 错误: ('HY000', '[HY000] [MySQL][ODBC 5.2(w) 驱动程序][mysqld-5.7.7-rc-log]索引列大小太大。最大列大小为 767 字节。(1709) (SQLExecDirectW)')
I Googled a little bit, and found this error might be relating to the innodb_large_prefixoption. However, I am using MySQL 5.7.7 rc, which has already set innodb_large_prefixto be "ON" (checked in MySQL Workbench), allowing up to 3072 bytes. I am not sure if that is the problem with innodb_large_prefixor not.
我用谷歌搜索了一下，发现这个错误可能与innodb_large_prefix选项有关。但是，我使用的是 MySQL 5.7.7 rc，它已经设置innodb_large_prefix为“ON”（在 MySQL Workbench 中检查），最多允许 3072 个字节。我不确定这是否是问题所在innodb_large_prefix。
Anyway, does anyone have an idea how to fix this problem?
无论如何，有没有人知道如何解决这个问题？
回答by Sasank Mukkamala
With the help of the answer given by BK435, I did the following and solved the problem.
在BK435给出的答案的帮助下，我做了以下并解决了问题。
set global innodb_file_format = BARRACUDA;
set global innodb_large_prefix = ON;
create table test (........) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
回答by BK435
Your column that you are trying to index is too large and your settings must not be correct for innodb_large_prefix. There are a couple prerequisitesparameters that also have to be set in order for innodb_large_prefixto work correctly.
您尝试索引的列太大，并且您的设置不能正确innodb_large_prefix。为了正常工作，还必须设置几个先决条件参数innodb_large_prefix。
You can check to make sure that innodb_large_prefixis set by running:
您可以通过运行来检查以确保设置了innodb_large_prefix：
show global variables like 'innodb_lar%';
show global variables like 'innodb_lar%';
Here are a couple prerequisites for using innodb_large_prefix:
以下是使用 innodb_large_prefix 的几个先决条件：
You need to set your global variable innodb_file_format=BARRACUDA
您需要设置全局变量 innodb_file_format= BARRACUDA
to check settings run: show global variables like 'innodb_fil%';
检查设置运行： show global variables like 'innodb_fil%';
At the table level you have to use ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED
在表级别，您必须使用 ROW_FORMAT=DYNAMIC 或 ROW_FORMAT=COMPRESSED
for Innodb, rows are stored in COMPACT format (ROW_FORMAT=COMPACT) by default.
对于Innodb，默认情况下，行以 COMPACT 格式（ROW_FORMAT=COMPACT）存储。
回答by Son Nguyen
Just add the following options to my.cnf
只需将以下选项添加到 my.cnf
[mysqld]
innodb_file_format=Barracuda
innodb_file_per_table=1
innodb_large_prefix=1
Then, restart mysql server the problem will be resolved.
然后，重启mysql服务器问题就解决了。
回答by Techifylogic
I was using MariaDB version 10.1.38 and used all of the below given commands but it did not work -
我使用的是 MariaDB 版本 10.1.38 并使用了以下所有给定的命令，但它不起作用 -
set global innodb_large_prefix = ON;
Query OK, 0 rows affected (0.00 sec)
set global innodb_file_per_table = ON;
Query OK, 0 rows affected (0.00 sec)
set global innodb_file_format = Barracuda;
Query OK, 0 rows affected (0.00 sec)
SET GLOBAL innodb_default_row_format = 'DYNAMIC';
Because after you restart your MySQL (or MariaDB), these settings will not reflect back using the command at the mysql prompt: show variables like 'innodb%';
因为在您重新启动 MySQL（或 MariaDB）后，这些设置将不会在 mysql 提示符下使用命令反映回来： show variables like 'innodb%';
Then I edited My.ini and added these settings in the file at below location- C:\xampp\mysql\bin\my.ini
然后我编辑了 My.ini 并将这些设置添加到以下位置的文件中 - C:\xampp\mysql\bin\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'
来源：https: //www.experts-exchange.com/questions/28675824/Why-am-I-unable-to-turn-innodb-large-prefix-ON-successfully-Every-time-I-reboot-mySql- on-my-Ubuntu-VPS-it-reset-to-OFF.html
回答by Tymoteusz Motylewski
I had the same error despite having innodb_large_prefixconfigured correctly.
尽管innodb_large_prefix配置正确，但我遇到了同样的错误。
The issue was in used collation. My db had default collation set to utf8mb4_bin(you can check it in phpmyadmin "Operations" tab for database). It means it uses 4 bytes per char, while utf8 collation (e.g. utf8_unicode_ci) uses 3 bytes per char.
问题在于使用的整理。我的数据库的默认排序规则设置为utf8mb4_bin（您可以在数据库的 phpmyadmin“操作”选项卡中检查它）。这意味着它每个字符使用 4 个字节，而 utf8 排序规则（例如 utf8_unicode_ci）每个字符使用 3 个字节。
in this case you can either use different collation e.g. by adding DEFAULT CHARSET=utf8at the end of the CREATE TABLEstatement, or limit the index size by using just a part of the column value
like KEY 'identifier' (column1(5),column2(10)).
在这种情况下，您可以使用不同的排序规则，例如通过DEFAULT CHARSET=utf8在CREATE TABLE语句末尾添加，或者仅使用列值的一部分来限制索引大小，例如KEY 'identifier' (column1(5),column2(10)).
See also related question: #1071 - Specified key was too long; max key length is 767 bytes
另请参阅相关问题：#1071 - 指定的密钥太长；最大密钥长度为 767 字节
回答by Zhenya
In my case (MySQL version 5.6) the issue was that I was trying to create a table with a column that can have up to 256 characters (the db uses utf8 collation), so 3 bytes per 1 utf8 character = 256*3=768 bytes. The fix was to simply have 255 characters instead of 256.
就我而言（MySQL 5.6 版），问题是我试图创建一个包含最多 256 个字符的列的表（数据库使用 utf8 排序规则），因此每 1 个 utf8 字符 3 个字节 = 256*3=768字节。解决方法是简单地使用 255 个字符而不是 256 个字符。
I could also set innodb_large_prefix, like others suggest, but in my case it was easier to just have fewer symbols.
我也可以像其他人建议的那样设置 innodb_large_prefix，但在我的情况下，使用更少的符号更容易。
回答by Simon Lippens
I had this problem because I tried to create a String primary key with varchar(254). Easy to overlook sometimes.. So double check your index type and length as well :)
我遇到这个问题是因为我试图用 varchar(254) 创建一个 String 主键。有时很容易被忽视..所以还要仔细检查你的索引类型和长度:)
回答by Himanshu Patel
回答by T Wheeler
Set the below system variables:
设置以下系统变量：
innodb_buffer_pool_size.................................... 702545920
innodb_file_format......................................... Barracuda
innodb_file_format_check................................... ON
innodb_file_format_max..................................... Barracuda
innodb_file_per_table...................................... ON
innodb_large_prefix........................................ ON
innodb_log_file_size....................................... 50331648
Also, make sure when you create your schema you create it as Latin1. That is what finally fixed me.
此外，请确保在创建架构时将其创建为Latin1。这就是最终固定我的原因。
