MySQL数据批量导入完毕后执行执行如下SQL(创建索引):
CREATE INDEX `recipe_form_index` USING BTREE ON `audit_form`(`zone_id`,`type`,`event_nos`)
错误信息:
1071 - Specified key was too long; max key length is 767 bytes
原因是event_nos的字段值长度允许最大是1000,而在库中实际存储最大长度,查询如下:
mysql> select length(event_nos) from `audit_form` order by CHAR_LENGTH(`event_nos`) DESC limit 1;
+-------------------+
| length(event_nos) |
+-------------------+
| 89 |
+-------------------+
1 row in set
mysql>
由此可见最大长度是89,由此在建立索引的语句调正如下:
CREATE INDEX `recipe_form_index` USING BTREE ON `audit_form`(`zone_id`,`type`,`event_nos`(89));
当然,不建议限制长度为89,最好比实际大一点(最大是255),还是要随机应变的!
MySQL慢查询分析案例
分享到:
相关推荐
今天在为数据库中的某两个字段设置unique索引的时候,出现了Specified key was too long; max key length is 1000 bytes错误
本文将详细提供mysql建库时提示Specified key was too long max key length is 1000 bytes的问题的解决方法,有需求的朋友可以参考
nacos-server-1.1.4 windows下的运行程序 springcloud alibaba
ALTER TABLE table1 ALTER COLUMN ... 您可能感兴趣的文章:sql server建库、建表、建约束技巧mysql建库时提示Specified key was too long max key length is 1000 bytes的问题的解决方法Mysql 建库建表技巧分享SQL S
max key length is 767 bytes 解决方案1: 直接缩短到255长度 解决方案2: 修改数据库配置, set innodb_large_prefix=on 吐槽一下, 1.2.0版本比1.1.4版本多创建一个数据表, 为甚么前面的sql有引擎,有...
Illuminate\Database\QueryException : SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table `users` add unique `users_email_...
- When x86-64 support is compiled in, you could enable/disable long mode 1G pages support without recompile using new CPUID option in .bochsrc. Configure options: --enable-mmx, --enable-sse, --...
String Keys are UTF8 encoded and limited to 60 bytes if not specified otherwise (maximum is 255 chars). Support for long string Keys with the RaptorDBString class. Duplicate keys are stored as a WAH ...
On production servers, 4096 bytes is a good setting for performance ; reasons. ; Note: Output buffering can also be controlled via Output Buffering Control ; functions. ; Possible Values: ; On = ...