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;