执行以下命令时:
ALTER TABLE `mytable` ADD UNIQUE (
`column1` ,
`column2`
);
我得到了这个错误信息:
#1071 - Specified key was too long; max key length is 767 bytes
columnn1和column2的信息:
column1 varchar(20) utf8_general_ci
column2 varchar(500) utf8_general_ci
我认为varchar(20)只需要21个字节，而varchar(500)只需要501个字节。所以总字节数是522，小于767。为什么我得到了错误消息?
#1071 - Specified key was too long; max key length is 767 bytes
当前回答
对于这个问题，我自己的解决方案比降低表的VARCHAR大小更简单，也更安全。
情况:CentOS 7服务器运行Plesk Obsidian 18.0.37和MariaDB 5.5。我试图从运行MariaDB 10.1的服务器导入MySQL转储。
解决方案:从MariaDB 5.5升级到10.6。
这些步骤大致基于以下指南和以下指南:
mysqldump -u admin -p`cat /etc/psa/.psa.shadow` --all-databases --routines --triggers > /root/all-databases.sql systemctl stop mariadb cp -a /var/lib/mysql/ /var/lib/mysql_backup Configure MariaDB repositories according to the official guide Make sure you meet Plesk's minimum version requirements detailed here yum install MariaDB-client MariaDB-server MariaDB-compat MariaDB-shared systemctl start mariadb In my case, the server failed to start here with an error: "Can't start server: Bind on TCP/IP port. Got error: 22: Invalid argument". The fix was to replace bind-address as follows in /etc/my.cnf and re-run the command: [mysqld] # OLD (broken) #bind-address = ::ffff:127.0.0.1 # NEW bind-address = 127.0.0.1 MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql_upgrade -uadmin plesk sbin packagemng -sdf rm -f /etc/init.d/mysql systemctl daemon-reload
其他回答
如果有人在InnoDB和utf8字符集试图在VARCHAR(256)字段上放置UNIQUE索引时遇到问题，请将其切换到VARCHAR(255)字段。255似乎是限制。
关于为什么你会得到错误消息的答案已经被很多用户回答了。我的答案是如何修复和使用它。
从这个链接中查阅。
打开MySQL客户端(或MariaDB客户端)。它是一个命令行工具。 它会问你的密码，输入正确的密码。 使用以下命令选择您的数据库:
数据库修改
设置全局innodb_large_prefix=on;
查询OK, 0行受影响(0.00秒)
set global innodb_file_format=Barracuda;
查询OK, 0行受影响(0.02秒)
去phpMyAdmin或类似的数据库，以便于管理。>选择数据库>查看表结构>进入“操作”页签。>将ROW_FORMAT更改为DYNAMIC并保存更改。 进入表的结构选项卡>单击唯一按钮。 完成了。现在应该没有错误了。
这个修复的问题是，如果你导出数据库到另一个服务器(例如从本地主机到真实主机)，你不能在该服务器上使用MySQL命令行。你不能让它在那里工作。
Specified key was too long; max key length is 767 bytes
You got that message because 1 byte equals 1 character only if you use the latin-1 character set. If you use utf8, each character will be considered 3 bytes when defining your key column. If you use utf8mb4, each character will be considered to be 4 bytes when defining your key column. Thus, you need to multiply your key field's character limit by, 1, 3, or 4 (in my example) to determine the number of bytes the key field is trying to allow. If you are using uft8mb4, you can only define 191 characters for a native, InnoDB, primary key field. Just don't breach 767 bytes.
我发现这个查询在检测哪些列的索引违反了最大长度方面很有用:
SELECT
c.TABLE_NAME As TableName,
c.COLUMN_NAME AS ColumnName,
c.DATA_TYPE AS DataType,
c.CHARACTER_MAXIMUM_LENGTH AS ColumnLength,
s.INDEX_NAME AS IndexName
FROM information_schema.COLUMNS AS c
INNER JOIN information_schema.statistics AS s
ON s.table_name = c.TABLE_NAME
AND s.COLUMN_NAME = c.COLUMN_NAME
WHERE c.TABLE_SCHEMA = DATABASE()
AND c.CHARACTER_MAXIMUM_LENGTH > 191
AND c.DATA_TYPE IN ('char', 'varchar', 'text')
您使用的是什么字符编码?有些字符集(如UTF-16等等)每个字符使用一个以上的字节。
