浅记 ERROR 1071 (42000): Specified key was too long； max key length is 1000 bytes

目录

问题来源

基本信息

问题分析

解决方式

---

问题来源

近来在学习代码的时候会涉及到创建数据库，但是代码一运行，就开始给我反馈上述报错”ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes“，这里浅记一下原由。

基本信息

1、数据库版本

mysql 8.0.12

2、所建数据库信息

默认存储引擎：myisam

数据库字符集：utf8mb4

问题分析

其实问题原因相当的明显，就是索引长度达到长度限制1000bytes，所以报错。

1、不同存储引擎对索引大小的限制

在MySQL 5.6及之前的版本，使用InnoDB作引擎的表的索引大小要小于767B，对于MyISAM的限制则是1000B。在MySQL5.7之后此限制扩展到了3027B

2、不同字符集占的存储空间

如果你设置的一个varchar字段使用的是utf8mb4字符集，那么索引最多可以存储 767 / 4 ＝ 191个这样的字符。因为utf8mb4的字符每个占4B的存储空间，如果varchar使用utf8字符集，那么索引最多可以存储767 / 3 = 254个这样的字符。因为使用utf8存储的字符占3B的存储空间
 

解决方式

1、首先查看表中的索引列

mysql> show index from user;
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| user  |          0 | PRIMARY             |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     |
| user  |          0 | user_name           |            1 | user_name   | A         |        NULL |     NULL |   NULL | YES  | BTREE      |         |               | YES     |
| user  |          1 | idx_user_deleted_at |            1 | deleted_at  | A         |        NULL |     NULL |   NULL | YES  | BTREE      |         |               | YES     |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

 上述user表中有三个索引，key_name 表中有显示：
--PRIMARY主键索引，Seq_in_index索引号为1，从1开始， Collation为”A“表示升序，对应字段为id。

--idx_user_deleted_at 是自建索引，由deleted_at字段组成，对应序号1

2、可以通过删除列的索引，对表中的字段大小的操作可以正常进行

一、使用 DROP INDEX 语句

语法格式：

DROP INDEX <索引名> ON <表名>

语法说明如下：

1.<索引名>：要删除的索引名。

2.<表名>：指定该索引所在的表名

二、使用 ALTER TABLE 语句

根据 ALTER TABLE 语句的语法可知，该语句也可以用于删除索引。具体使用方法是将 ALTER TABLE 语句的语法中部分指定为以下子句中的某一项。

1.DROP PRIMARY KEY：表示删除表中的主键。一个表只有一个主键，主键也是一个索引。

2.DROP INDEX index_name：表示删除名称为 index_name 的索引。

3.DROP FOREIGN KEY fk_symbol：表示删除外键。

注意：如果删除的列是索引的组成部分，那么在删除该列时，也会将该列从索引中删除；如果组成索引的所有列都被删除，那么整个索引将被删除。

3、操作演示