执行以下命令时:

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

当前回答

当你达到极限的时候。设置如下参数。

INNODB utf8 VARCHAR(255) INNODB utf8mb4 VARCHAR

其他回答

对于这个问题,我自己的解决方案比降低表的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

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.

根据下面给出的列,这两个变量字符串列使用utf8_general_ci排序规则(隐含utf8字符集)。

在MySQL中,utf8字符集每个字符最多使用3个字节。因此,它需要分配500*3=1500字节,这比MySQL允许的767字节要大得多。这就是为什么您会得到1071错误。

换句话说,您需要基于字符集的字节表示来计算字符数,因为并非每个字符集都是一个字节表示(正如您所假设的那样)。例如,MySQL中的utf8每个字符最多使用3个字节,767/3≈255个字符,而对于utf8mb4,最多使用4个字节表示,767/4≈191个字符。

众所周知,MySQL

column1 varchar(20) utf8_general_ci
column2  varchar(500) utf8_general_ci

我发现这个查询在检测哪些列的索引违反了最大长度方面很有用:

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')

在查询之前运行此查询:

SET @@global.innodb_large_prefix = 1;

这将把限制增加到3072字节。