偶遇ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
今天在對一張表加索引時候出現如下報錯:
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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30135314/viewspace-2654164/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL:Specified key was too long; max key length is 767 bytesMySql
- Specified key was too long; max key length is 1000 bytes
- 解決 Specified key was too long ... 767 bytes 的本質問題
- mysql specified key was too long與Index column size too large. The maximum column size is 767 bytes.MySqlIndex
- 執行遷移檔案報錯 1071 Specified key was too long.
- [報錯:字元太長] SQLSTATE [42000]: Syntax error or access violation: 1071 Specified key was t oo long;字元SQLError
- Laravel 5.4 常見錯誤:Specified key was too longLaravel
- Laravel 5.5 資料遷移問題:Specified key was too longLaravel
- mysql 索引長度 767 錯誤 ERROR 1071MySql索引Error
- Index column size too large. The maximum column size is 767 bytesIndex
- Index column size too large. The maximum column size is 767 bytes.Index
- insert:key too large to index…Index
- 1709 - Index column size too large. The maximum column size is 767 bytes.Index
- [Err] 1709 - Index column size too large. The maximum column size is 767 bytes.Index
- error: "net.ipv4.ip_conntrack_max" is an unknown keyError
- MySQL建立表失敗:Index column size too large. The maximum column size is 767 bytesMySqlIndex
- MySQL 5.7 到 5.6,出現 Index column size too large. The maximum column size is 767 bytesMySqlIndex
- Error running ‘Application’Command line is too longErrorAPP
- MySQL資料庫index column size too large. the maximum column size is 767 bytes問題解決MySql資料庫Index
- mysql5.7 資料匯入5.6 Index column size too large. The maximum column size is 767 bytes.MySqlIndex
- python -- 解決字典【KEY ERROR】PythonError
- laravel出現The cipher and / or key length are invalid 的Bug除錯Laravel除錯
- MGETkey[key…]
- error: unknown error 22 setting key 'kernel.shmmax'ErrorHMM
- Laravel5.4 資料庫遷移錯誤 SQLSTATE [42000] Syntax error or access violation 1071Laravel資料庫SQLError
- js keyup、keypress和keydown事件JS事件
- Max length of title attribute in htmlHTML
- With KEY & With Table KEY 的使用
- keyup,keypress,keydown事件的區別事件
- mysql中key 、primary key 、unique key 與index區別MySqlIndex
- GPG key retrieval failed: [Errno 14] HTTP Error 404: Not FoundAIHTTPError
- Redis熱點key大keyRedis
- keycloak~關於session idle和session max的解釋Session
- WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!AIENQ
- spark RDD,reduceByKey vs groupByKeySpark
- valueforkey和objectforkey區別Object
- js的事件屬性altKey,ctrlKey,shiftKey介紹JS事件
- DUMP-CX_SY_OPEN_SQL_DB-DBSQL_DUPLICATE_KEY_ERRORSQLError