ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
Posted on 2021-12-24 10:57 高&玉 阅读(11849) 评论(0) 编辑 收藏 举报问题背景
zabbix监控导入schema.sql文件时报错,经查看找到如下对应的语句。
MySQL数据库版本:8.0.24
创建语句
create database zabbix;
CREATE TABLE `items` (
`itemid` bigint unsigned NOT NULL,
`type` integer DEFAULT '0' NOT NULL,
`snmp_oid` varchar(512) DEFAULT '' NOT NULL,
`hostid` bigint unsigned NOT NULL,
`name` varchar(255) DEFAULT '' NOT NULL,
`key_info` varchar(2048) DEFAULT '' NOT NULL,
PRIMARY KEY (itemid)
) ENGINE=InnoDB;
CREATE INDEX `index_items_key` ON `items` (`hostid`,`key_info`(1021));
报错信息:指定的key长度超过了3072bytes。
(root@localhost) [zabbix]> CREATE INDEX `index_items_key` ON `items` (`hostid`,`key_info`(1021));
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
解决步骤
查看数据库默认字符集是utf8mb4
(root@localhost) [zabbix]> select @@global.character_set_server;
+-------------------------------+
| @@global.character_set_server |
+-------------------------------+
| utf8mb4 |
+-------------------------------+
utf8mb4字符集一个字符占4个字节
(root@localhost) [zabbix]> select * from information_schema.CHARACTER_SETS where CHARACTER_SET_NAME='utf8mb4';
+--------------------+----------------------+---------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+---------------+--------+
| utf8mb4 | utf8mb4_0900_ai_ci | UTF-8 Unicode | 4 |
+--------------------+----------------------+---------------+--------+
表的行格式(ROW_FORMAT)绝对索引长度:
- ROW_FORMAT= COMPACT 或 REDUNDANT,单列索引支持的最大长达为767bytes。
- ROW_FORMAT= COMPRESSED 或 DYNAMIC,单列索引支持的最大长度为3072bytes。
索引长度计算:
- 索引字段如果没有设置NOT NULL,则需要添加一个字节。
- 定长字段,如INT占4个字节、DATE占3个字节、CHAR(N)占N个字符。(注意字符与字节的区别,字符的长度取决于当前的字符集)
- 变长字段,如VARCHAR(N)占用N个字符+2个字节。
MySQL 8.X版本单个索引列支持最大长度是3072bytes。如果创建的索引列是变长字段VARCHAR,所以支持的最大长度是(3072-1-2)/4≈767。然后需要创建的索引index_items_key指定的索引长度是1021,我又不能更改索引的长度,所以将zabbix数据库的字符集设置成utf8(也可以单独指定表的字符集,但是需要保证相同业务类型表的字符集都一样,否则会导致索引失效),然后重新导入SQL。
Utf8一个字符占3个字节,单个索引最大长度是(3072-1-2)/3=1023
(root@localhost) [zabbix]> select * from information_schema.CHARACTER_SETS where CHARACTER_SET_NAME='utf8';
+--------------------+----------------------+---------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+---------------+--------+
| utf8 | utf8_general_ci | UTF-8 Unicode | 3 |
+--------------------+----------------------+---------------+--------+
创建语句
create database zabbix character utf8;
CREATE TABLE `items` (
`itemid` bigint unsigned NOT NULL,
`type` integer DEFAULT '0' NOT NULL,
`snmp_oid` varchar(512) DEFAULT '' NOT NULL,
`hostid` bigint unsigned NOT NULL,
`name` varchar(255) DEFAULT '' NOT NULL,
`key_info` varchar(2048) DEFAULT '' NOT NULL,
PRIMARY KEY (itemid)
) ENGINE=InnoDB;
CREATE INDEX `index_items_key` ON `items` (`hostid`,`key_info`(1021));
索引创建成功
(root@localhost) [zabbix]> CREATE INDEX `index_items_key` ON `items` (`hostid`,`key_info`(1021));
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
PS:
MySQL 8.X版本单列索引支持的最大长度默认就是3072bytes。
MySQL 5.7版本单列索引支持的最大长度默认是768bytes,如需单列索引最大长度支持3072bytes,需配置参数innodb_large_prefix=ON。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库