mysql specified key was too long问题详解

1. 问题描述

在使用MySQL数据库时，我们可能会遇到 “Specified key was too long” 的错误。这个错误通常出现在创建索引或唯一约束时，提示我们指定的键值过长。

2. 问题原因

MySQL的字符集和排序规则决定了它对存储、比较和索引字符串的方式。对于InnoDB存储引擎，默认的字符集UTF-8使用最多3个字节来表示一个字符。如果我们在创建索引或约束时指定的字段长度超过了该字符集的最大长度限制，就会出现 “Specified key was too long” 错误。

3. 字符集和排序规则

在解决这个问题之前，我们首先需要了解MySQL字符集和排序规则的相关概念：

3.1 字符集（Character Set）

字符集是一组字符的集合，决定了数据在存储和查询时是如何表示的。常见的字符集有UTF-8、UTF-16、GBK等。

3.2 排序规则（Collation）

排序规则是决定字符比较和排序方式的规则集合。例如，UTF-8字符集的排序规则有utf8_general_ci（不区分大小写）、utf8_general_cs（区分大小写）等。

4. 解决方法

根据 “Specified key was too long” 错误的提示，我们可以采用以下两种方法来解决这个问题：

4.1 方法一：缩短字段长度

一种解决方法是通过缩短字段的长度来避免 “Specified key was too long” 错误。通过分析错误信息中指定的字段，查看该字段在数据库中的实际使用情况，我们可以判断是否可以缩短该字段的长度。

4.2 方法二：修改字符集和排序规则

另一种解决方法是修改数据库的字符集和排序规则，以适应指定字段长度。我们可以通过以下步骤来实现：

4.2.1 查看当前数据库的字符集和排序规则

执行以下命令查看当前数据库的字符集和排序规则：

SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

4.2.2 修改数据库的字符集和排序规则

如果当前数据库的字符集和排序规则不符合需要，可以通过以下命令来修改数据库的字符集和排序规则：

ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

其中，database_name 是你需要修改的数据库名称。utf8mb4 是一种支持4字节Unicode字符的字符集，它可以解决很多特殊字符无法正确存储的问题。utf8mb4_unicode_ci 是utf8mb4字符集的一种排序规则，支持各种语言的排序。

4.2.3 修改表的字符集和排序规则

如果只需要修改特定表的字符集和排序规则，可以通过以下命令来修改表的字符集和排序规则：

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

其中，table_name 是你需要修改的表名。

4.3 案例分析

假设我们有一个表 users，其中有一个字段 username 需要创建唯一索引。现在我们尝试创建索引：

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(256) NOT NULL,
  UNIQUE INDEX unique_username (username)
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

上述代码中，我们使用了字符集utf8mb4和排序规则utf8mb4_unicode_ci来创建表，并指定了字段 username 的长度为256。如果我们的MySQL数据库支持utf8mb4字符集，并且该字符集的排序规则对应的最大字符长度为255，那么在创建索引时就会出现 “Specified key was too long” 错误。

此时，我们可以尝试修改字段 username 的长度为255，即可解决问题：

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(255) NOT NULL,
  UNIQUE INDEX unique_username (username)
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;