怎么解决数据库ERROR 1071 (42000)报错问题

103次阅读

共计 2596 个字符，预计需要花费 7 分钟才能阅读完成。

本篇内容介绍了“怎么解决数据库 ERROR 1071 (42000) 报错问题”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

今天在对一张表加索引时候出现如下报错：

mysql  ALTER TABLE ym_sys_dict ADD INDEX idx_dcode_dvalue (`dict_code`, `dict_value`);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

查阅文档时候，看到如下解释：

For CHAR, VARCHAR, BINARY, and VARBINARY columns, indexes can be created that use only the leading part of column values, using col_name(length) syntax to specify an index prefix length. 
... 
Prefixes can be up to 1000 bytes long (767 bytes for InnoDB tables). Note that prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE statements is interpreted as number of characters ... 
对于 myisam 和 innodb 存储引擎，prefixes 的长度限制分别为 1000 bytes 和 767 bytes。注意 prefix 的单位是 bytes，但是建表时我们指定的长度单位是字符。 
A utf8 character can use up to 3 bytes. Hence you cannot index columns or prefixes of columns longer than 333 (MyISAM) or 255 (InnoDB) utf8 characters.  以 utf8 字符集为例，一个字符占 3 个 bytes。因此在 utf8 字符集下，对 myisam 和 innodb 存储引擎创建索引的单列长度不能超过 333 个字符和 255 个字符

mysql 索引长度限制：

1）单列索引：

mysql 在创建单列索引的时候对列的长度是有限制的 myisam 和 innodb 存储引擎下长度限制分别为 1000 bytes 和 767 bytes。(注意 bytes 和 character 的区别)

2) 组合索引：

对于 innodb 存储引擎，多列索引的长度限制如下：每个列的长度不能大于 767 bytes；所有组成索引列的长度和不能大于 3072 bytes

smallint 占 2 个 bytes，timestamp 占 4 个 bytes，utf8 字符集。utf8 字符集下，一个 character 占三个 byte。

对于这个问题，解决方法有两个：

1）修改参数 innodb_large_prefix，该参数默认为 OFF，修改为 ON

mysql  show variables like  innodb_large_prefix 
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_large_prefix | OFF |
+---------------------+-------+

2）修改字段长度

查看表结构：

mysql  show create table ym_sys_dict \G
*************************** 1. row ***************************
 Table: ym_sys_dict
Create Table: CREATE TABLE `ym_sys_dict` ( `id` int(20) NOT NULL AUTO_INCREMENT,
 `dict_name` varchar(100) NOT NULL COMMENT  字典名称 ,
 `dict_type` varchar(100) NOT NULL COMMENT  字典类型 ,
 `dict_code` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
 `dict_value` varchar(1000) DEFAULT NULL,
 `order_num` int(11) DEFAULT  0  COMMENT  排序 ,
 `remark` varchar(255) DEFAULT     COMMENT  备注 ,
 `del_flag` tinyint(4) DEFAULT  0  COMMENT  删除标记  -1：已删除  0：正常 ,
 PRIMARY KEY (`id`),
 UNIQUE KEY `dict_type` (`dict_type`,`dict_code`)
) ENGINE=InnoDB AUTO_INCREMENT=93 DEFAULT CHARSET=utf8 COMMENT= 数据字典表

经和开发沟通，dict_value 字段长度设置过长，改字段长度为 100

alter table ym_sys_dict modify dict_value varchar(100)；

然后可以正常添加索引

mysql  ALTER TABLE ym_sys_dict ADD INDEX idx_dcode_dvalue (`dict_code`, `dict_value`);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

“怎么解决数据库 ERROR 1071 (42000) 报错问题”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！

正文完