怎么解決數據庫ERROR 1071 (42000)報錯問題

本篇內容介紹了“怎么解決數據庫ERROR 1071 (42000)報錯問題”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

今天在對一張表加索引時候出現如下報錯：

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)報錯問題”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！