ERROR 1071 (42000)：指定的密鑰太長；最大密鑰長度為 767 字節

我有一個帶有字元集的數據庫latin1，我需要將表字元集更改為utf8，但出現錯誤。目前字元集是：

mysql> show variables like "%character%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

我要更改字元集的表是：

mysql> describe spool;
+------------+---------------------+------+-----+-------------------+----------------+
| Field      | Type                | Null | Key | Default           | Extra          |
+------------+---------------------+------+-----+-------------------+----------------+
| username   | varchar(250)        | NO   | MUL | NULL              |                |
| xml        | text                | NO   |     | NULL              |                |
| seq        | bigint(20) unsigned | NO   | PRI | NULL              | auto_increment |
| created_at | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
+------------+---------------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)

當我想更改字元集時，我看到以下錯誤：

mysql> ALTER TABLE spool CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

這是一個公司運作的數據庫，我不想打擾功能，所以我不想改變varchar(250)類型的大小，我想知道是否有解決方案來改變這種編碼。

mysql> show create table spool\G;
*************************** 1. row ***************************
      Table: spool
Create Table: CREATE TABLE `spool` (
 `username` varchar(250) NOT NULL,
 `xml` text NOT NULL,
 `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY `seq` (`seq`),
 KEY `i_despool` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=23490775 DEFAULT CHARSET=utf8

解決了 ：

我通過創建這樣的新表來解決這個問題spool。當我們想創建一個帶有utf8mb4 COLLATE utf8mb4_unicode_ci字元集的表時，我們不能使用varchar(250)，因為250 * 4 = 1000（在這種新編碼中每個字元 4 個字節）。1000 大於此編碼的最大密鑰 767 字節。所以我創建了一個spoolll表varchar(100)：

CREATE TABLE `spoollll` (
 `username` varchar(100) NOT NULL,
 `xml` text NOT NULL,
 `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY `seq` (`seq`),
 KEY `i_despool` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=23492667 DEFAULT CHARSET=utf8 

現在我更改字元集：

mysql> ALTER TABLE spoollll CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

如果有人對此發表評論並完成此問題，我將不勝感激。

引用自：https://dba.stackexchange.com/questions/130891