[数据库/MYSQL]#解决缺陷#设置Unique索引时:"[Err] 1071 - Specified key was too long; max key length is 767 bytes"

1 问题复现

  • 原表结构:
CREATE TABLE `XX_TEMPERATURE` (
	`FLOW_ID` int(11) NOT NULL COMMENT ‘独立的数据表或FTP唯一标识‘, -- 与上述其它表的字段完全无关联,属自创ID
	`TABLE_FLOW_ID` varchar(512) COLLATE utf8_bin DEFAULT NULL COMMENT ‘表,元数据uniqueID‘,  -- 本次博客的重点
  		-- MYSQL_127.0.0.1_3306_root_CJ_TESTDBDatabase^CJ_TEST|DBTable^Person
	`TEMPERATURE_VALUE` int(11) NOT NULL COMMENT ‘(表级)热度值‘,  -- 应保证其值实时更新 
	PRIMARY KEY (`FLOW_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT=‘热度值表‘;
  • 现需求

对 TABLE_FLOW_ID 新增 Unique 索引

  • 正常做法:
ALTER TABLE `XX_TEMPERATURE` ADD CONSTRAINT UNIQUE_INDEX_FOR_TABLE_FLOW_ID_OF_TEMPERATURE_TABLE UNIQUE(`TABLE_FLOW_ID`);

然鹅,出现了如下错误:

[SQL]ALTER TABLE `XX_TEMPERATURE` ADD CONSTRAINT UNIQUE_INDEX_FOR_TABLE_FLOW_ID UNIQUE(`TABLE_FLOW_ID`);
[Err] 1071 - Specified key was too long; max key length is 767 bytes

2 解决方案

本质上,就是 VARCHAR(512) 512>256了,导致的失败。(更深层原因待深究)

方案1: 缩短Unique索引字段的长度

ALTER TABLE `XX_TEMPERATURE` MODIFY `TABLE_FLOW_ID` VARCHAR(255);

方案2: [配置] 系统变量 innodb_large_prefix / 系统变量 innodb_file_format / 表配置项 ROW_FORMAT

要解决 [Err] 1071 - Specified key was too long; max key length is 767 bytes这一缺陷,必须满足如下3个条件:

系统变量 innodb_large_prefix = ON
系统变量 innodb_file_format = Barracuda
表配置项 ROW_FORMAT = DYNAMIC / COMPRESSED
  • step1 配置 系统变量 innodb_large_prefix
set global innodb_large_prefix=on;

相关知识延伸

 -- 系统变量 innodb_large_prefix : 不同版本,开闭情况不同 (MySQL 5.6.41和5.6.33 默认关闭 ; MySQL 5.7 默认开启)
 ---- 如果启用了系统变量innodb_large_prefix,则:对于使用DYNAMIC或COMPRESSED行格式的InnoDB表,索引键前缀限制为3072字节。
 ---- 如果禁用innodb_large_prefix,则:对于任何行格式的表,索引键前缀限制为767字节。
 -- 查看 innodb_large_prefix
 show variables like ‘%innodb_large_prefix%‘;
 -- 设置 innodb_large_prefix -- on / off
 set global innodb_large_prefix=off; -- off
  • step2 配置 系统变量 innodb_file_format
set global innodb_file_format=Barracuda; 

相关知识延伸

 -- 查看
 show variables like ‘%innodb_file_format%‘;-- innodb_file_format = Antelope / innodb_file_format_check = ON / innodb_file_format_max = Barracuda
 -- 设置 
 set global innodb_file_format=Antelope; 
  • step3 配置 表配置项 ROW_FORMAT
ALTER TABLE XX_TEMPERATUREROW_FORMAT = DYNAMIC; 

相关知识延伸

 -- 备注 
 ---- https://www.cnblogs.com/wilburxu/p/9435818.html
 ---- 只有在MYSAM 的数据库引擎才支持属性: FIXED
 ---- fixed--->dynamic: 这会导致CHAR变成VARCHAR
 ---- dynamic--->fixed: 这会导致VARCHAR变成CHAR
 -- 查看
 SHOW TABLE STATUS LIKE "%XX_TEMPERATURE%"; -- ROW_FORMAT 字段 : Compact [ DEFAULT / Compact / FIXED / DYNAMIC / COMPRESSED / ... ]
 -- 设置
 ALTER TABLE XX_TEMPERATUREROW_FORMAT = Compact; 

X 参考资料

[数据库/MYSQL]#解决缺陷#设置Unique索引时:"[Err] 1071 - Specified key was too long; max key length is 767 bytes"

上一篇:tcpcollect基于libpcap 监听mysql sql


下一篇:MySQL基础(二)