[MySQL] specified key was too long max key length is 767bytes
来源:互联网 发布:js简单相册 编辑:程序博客网 时间:2024/06/01 08:05
一、问题描述
描述:创建唯一索引时,数据库报错:
specified key was too long max key length is 767bytes
数据库版本:5.5.22-log
引擎: InnoDB
数据库结构为:
CREATE TABLE `cdkey` ( `id` int(11) NOT NULL AUTO_INCREMENT, `cdkey` varchar(200) NOT NULL COMMENT '邀请码', `created_time` datetime NOT NULL COMMENT '创建时间', `updated_time` datetime NOT NULL COMMENT '修改时间', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='邀请码'
创建语句如下:
alter table `cdkey` add index `INDEX` (`cdkey`);
运行报错:
二、问题分析
参考自:http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes
允许索引字段前缀的字节限制为 767 bytes,且:
- 当字符集为 utf8mb4 时,一个字符对应 4 bytes;
- 当字符集为 utf8 时,一个字符对应 3 bytes;
所以对于(一)中的问题:
字符集为 utf8mb4, 所以可申请索引的 varchar 长度为:767/4 ≈ 191;
而字符集为 utf8 时,可设置的创建索引 varchar 长度为: 767/3 ≈ 255
三、版本差异
官方文档:innodb_large_prefix
在 5.6.3 之后的版本,字节限制提高到了 3072 bytes.
测试环境:5.7.9
引擎: InnoDB
数据库结构:
CREATE TABLE `test_index2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(1000) NOT NULL, `uid` int(11) NOT NULL, `created_time` datetime NOT NULL, `updated_time` datetime NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
运行索引创建命令:
alter table `test_index2` add index `INDEX` (`name`);
报错为:
从(二)可得:
字符集为 utf8mb4 时,限制 varchar 字符为:3072/4 = 768;
字符集为 utf8 时,限制 varchar 字符为:3072/3 = 1024;
因此字符集为 utf8mb4 时,将 name 字段索引前缀长度控制在 768 以内即可成功创建索引。
0 0
- MySQL: ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
- MySQL: ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
- mysql [Err] 1071 - Specified key was too long; max key length is 767 bytes
- MySQL:Specified key was too long; max key length is 767 bytes
- MySQL: ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
- mysql报错:Specified key was too long; max key length is 767 bytes
- MySQL-001-specified key was too long ; max key length is 767 bytes
- [MySQL] specified key was too long max key length is 767bytes
- mysql: Specified key was too long; max key length is 767 bytes
- mysql Specified key was too long; max key length is 767 bytes
- mysql [42000][1071] Specified key was too long; max key length is 767 bytes
- MySQL: ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
- MySQL: ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
- 关于MySQL字符集问题:Specified key was too long; max key length is 767 bytes
- mysql:Specified key was too long; max key length is 767 bytes
- MySQL错误“Specified key was too long; max key length is 1000 bytes”
- MySQL错误“Specified key was too long; max key length is 1000 bytes”的解决办
- MySQL错误“Specified key was too long; max key length is 1000 bytes”的解决办法
- 加密解密算法java实现(4)—MD5
- NSString copy string
- 面试问题: 什么是ORMapping... 最朴素的说法!!
- 浅谈jquery中,html、val与text三者属性取值的联系与区别
- Fastdfs_install
- [MySQL] specified key was too long max key length is 767bytes
- Android EditText 设置键盘 搜索,回车
- 大师的审美、观点与论断
- 关于自己对嵌入式的学习方面的经验(新人自学向)
- 【android】几种模拟按键、屏幕点击的方法
- 字符编解码的故事(ASCII,ANSI,Unicode,Utf-8区别)
- RabbitMQ使用场景练习:Headers(六)
- https原理和tcp握手机制
- Scala基础笔记