mysql 错误:指定的键太长；最大密钥长度为 767 字节 [英] mysql error: Specified key was too long; max key length is 767 bytes

问题描述

我不得不运行这个命令来改变我的一个表中的一列.

I had to run this command to alter a column in one of my table.

ALTER table XYZ 修改值 VARCHAR(1024);

ALTER table XYZ modify value VARCHAR(1024);

当我运行它时，出现此错误.

When I run it, I get this error.

第 1 行的 ERROR 1071 (42000):指定的密钥太长；最大密钥长度为 767 字节

ERROR 1071 (42000) at line 1: Specified key was too long; max key length is 767 bytes

原始列大小是 250.这是奇怪的部分.我有一个配置相同的第二台 MySQL 服务器，它正在与第一台服务器进行 2 路复制.当我在我的第二个 MySQL 服务器上运行这个alter table"命令时，我没有收到这个错误.事实上，在alter命令运行完毕后，这条命令被复制到我的主mysql服务器上，没有任何错误.

The original column size was 250. Here is the strange part. I have a 2nd MySQL server with identical configurations and it is doing 2 way replication with the 1st server. When I run this "alter table" command on my 2nd MySQL server, I didn't get this error. In fact, after the alter command finished running, this command was replicated to my primary mysql server without any error.

我的两台 mysql 服务器设置为主动-主动，但只有其中一台正在使用中.所以澄清一下，我在活跃使用的服务器上遇到了这个最大密钥长度"错误.但它在未被使用的第二台服务器上运行良好.

My two mysql servers are setup as active-active, but only one of them is being actively use. So to clarify, I got this "max key length" error on the actively used server. But it ran fine on the 2nd server that was not being use.

有人知道为什么在我的第二台服务器上运行 find 命令吗?

Does anyone have any idea why the command ran find on my 2nd server?

谢谢../肯

推荐答案

您可能需要调整此表的索引.

You might need to adjust the index on this table.

DROP INDEX index_name ON tbl_name;
CREATE INDEX index_name ON tbl_name (col1,col2(100));

其中 col2 是示例中的值列，100 是索引中包含的最大字符数.

Where col2 is the value column from your example and 100 is the max characters to include in the index.

如果您不确定表上的索引，请尝试以下操作(将 tbl_name 替换为您的表名).

If you are unsure of the indexes on the table, try the following (replacing tbl_name with your table name).

SHOW INDEXES IN tbl_name;

这篇关于mysql 错误:指定的键太长；最大密钥长度为 767 字节的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！