MySQL有时拒绝使用(1071，“指定键太长；最大键长度为1024字节”)进行更新

Question

我使用的是MySQL 5.7.2.23。

我需要在两个表(u -> m)之间复制一些列，并使用以下update命令：

UPDATE m 
 JOIN u 
  ON u.id=m.user_id  
  AND u.package_name=m.package_name 
 SET 
    m.host_version = u.host_version,
    m.device_model = u.device_model,
    ...
     m.device_name = u.device_name 
 WHERE    shard_id = 96

UPDATE工作

这两个表在(id, package_name)上都有键：

CREATE TABLE u (
  id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  package_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  device_model` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  device_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  host_version` int DEFAULT NULL,
  shard_id` int NOT NULL DEFAULT '0',
  device_group_id` int DEFAULT NULL,
  PRIMARY KEY (shard_id`,`package_name`,`id`),
  UNIQUE KEY by_id` (`id`,`package_name`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE m ( 
  user_id varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  package_name varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  device_name varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  device_model varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  host_version int DEFAULT NULL,
  PRIMARY KEY (package_name,user_id),
  UNIQUE KEY by_id (user_id,package_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Innodb默认行格式是innodb_default_row_format=dynamic和Innodb innodb_large_prefix=ON。

然而，对于某些shardId_-S，我得到了上述错误消息(键太长)。

“解释”显示如下

id            | 1
select_type   | SIMPLE
table         | u
partitions    | <null>
type          | ref
possible_keys | PRIMARY,by_id
key           | PRIMARY
key_len       | 4
ref           | const
rows          | 5
filtered      | 100.0
Extra         | <null>

id            | 1
select_type   | UPDATE
table         | m
partitions    | <null>
type          | eq_ref
possible_keys | PRIMARY,by_id
key           | PRIMARY
key_len       | 1534
ref           | activitydb.u.package_name,activitydb.u.id
rows          | 1
filtered      | 100.0
Extra         | <null>

据我所知，由于3字节的UTF-8编码和将列更改为latin1，密钥为1534，减少了密钥长度并解决了问题。

我不明白的是，为什么MySQL只抱怨某些shard_id值。工作的碎片似乎只有很少的行数。

通常，我们在许多JOIN查询中使用上面的SELECT，而MySQL不抱怨密钥长度。

Answer 1

(尼姆罗德姆的答案更好，但这里有一些解决办法)

• VARCHAR(255) --检查数据；在大多数字段中可能不需要255个。
• 对于某些列来说，COLLATE utf8_unicode_ci可能是不必要的。考虑一下CHARACTER SET ascii。utf8需要3个字节才能达到上述限制；ascii只需要1字节。
• 考虑一下规范这些长字符串。例如，MEDIUMINT UNSIGNED仅为3个字节(1..16M范围)-远远低于3*255。

Answer 2

这种行为在MySql 5.7.23中是新的，是由此承诺引起的。

MySQL创建一个临时表，保存更新后的表的键，rows.This表使用固定大小的列创建，该列在Innodb表中限制为1024字节。

要查看后一个限制，请尝试运行

CREATE TABLE zzz(kkk BINARY(256) PRIMARY KEY) 

MySQL5.7.23返回错误：(1074, "Column length too big for column 'kkk' (max = 255); use BLOB or TEXT instead"))

我不知道为什么这里的最大长度是255，而临时表的最大长度是1024。