主页 / dba / 问题 / 130891
Accepted
amir jj
Asked: 2016-03-02 03:08:38 +0800 CST

ERROR 1071 (42000)：指定的密钥太长；最大密钥长度为 767 字节

  • 772

我有一个带有字符集的数据库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
mysql character-set

1 个回答

  1. Best Answer

    解决了 ：

    我通过创建这样的新表来解决这个问题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

    如果有人对此发表评论并完成此问题，我将不胜感激。

    • 0

相关问题