MySQL 创建索引的操作提示失败，这是什么原因导致的？字段类型和索引创建之间有什么关系？
作者：刘晨，网名 bisal ，具有十年以上的应用运维工作经验，目前主要从事数据库应用研发能力提升和技术管理相关的工作，公众号”bisal的个人杂货铺”。
爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
本文约 800 字，预计阅读需要 3 分钟。
背景
同事反馈说某个 MySQL 数据库创建索引提示错误，模拟报错如下：
CREATE INDEX t_reg_code_idx USING BTREE ON t(reg_code)
BLOB/TEXT column 'reg_code' used in key specification without a key length
从该提示可知，给 T 表的 reg_code 列创建一个 BTREE 索引，而这个 reg_code 列的字段类型是 BLOB 或 TEXT。
需要在键的说明中有长度定义，这是什么意思？
表索引前缀长度限制
MySQL 8.0
从 MySQL 8.0 的官方手册可以找到这段对 Index Prefixes 的说明。意思是如果对 BLOB 或者 TEXT 列创建索引，必须指定索引的前缀长度。对于使用 REDUNDANT 或者 COMPACT 行格式的 InnoDB 表，索引前缀最多 767 个字节，对于使用 DYNAMIC 或者 COMPRESSED 行格式的 InnoDB 表，索引前缀的上限最多是 3072 个字节，如果是 MyISAM 表，前缀长度最多可以达到 1000 个字节。
![]()
MySQL 5.7
而 MySQL 5.7 官方手册中，对索引前缀的限制有所不同，InnoDB 表的索引前缀最多可以达到 1000 个字节（此处我认为是错误的，应该是 3072），但前提是设置了 innodb_large_prefix（只对 DYNAMIC 或者 COMPRESSED 行格式生效，对 REDUNDANT 或者 COMPACT 行格式无效），否则只能达到 767 个字节。
![]()
因此可知，MySQL 8.0 在 InnoDB 表的索引前缀长度限制的设置上有所调整，但是限制还是有，这是和 Oracle 等数据库有所不同的一个特性。
验证
通过实验，验证 MySQL 8.0 对于前缀长度的限制。
创建一张 row format 是 COMPACT 的 InnoDB 表，指定前缀长度 10000，提示最大键的长度只能是 767 个字节。
create table test01 (
id int(30) not null auto_increment,
t_a text,
primary key(id),
index idx_t_a(t_a(10000))
) COLLATE='gbk_chinese_ci' ENGINE=InnoDB ROW_FORMAT=COMPACT;
SQL 错误 [1071] [42000]: Specified key was too long; max key length is 767 bytes
创建一张 row format 是 COMPRESSED 的 InnoDB 表，指定前缀长度 10000，提示最大键的长度只能是 3072 个字节。
create table test01 (
id int(30) not null auto_increment,
t_a text,
primary key(id),
index idx_t_a(t_a(10000))
) COLLATE='gbk_chinese_ci' ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
SQL 错误 [1071] [42000]: Specified key was too long; max key length is 3072 bytes
总结
抛开技术问题，和同事追问了下这个操作的背景。原始需求是某个厂商的 ETL 任务需要从源库将数据导入目标库，源库字段是 VARCHAR 类型，目标库定义为 TEXT，才间接引起的这个问题。推测一种可能的原因，因为 VARCHAR、TEXT 都可以存储字符串类型的数据，所以没做区分，另一种可能，为了图省事儿，不用关注源库和目标库字符串类型定义的长度，直接设置了 TEXT 类型，保证肯定能存下。
无论是何种原因，TEXT 这种大字段类型，一般不推荐作为索引检索字段，因为往往它存储了很多字符，索引存储空间会占用更多，索引的区分度也会有影响。
因此，虽然这个问题表象是个技术问题，但实际上来源于不合理的设计，我们在进行应用设计、数据库设计时，如果能多考虑一些合理性，避免一些所谓的省事儿，可能在实际使用过程中，就会更顺畅，相辅相成的。
更多技术文章，请访问：https://opensource.actionsky.com/
关于 SQLE
爱可生开源社区的 SQLE 是一款面向数据库使用者和管理者，支持多场景审核，支持标准化上线流程，原生支持 MySQL 审核且数据库类型可扩展的 SQL 审核工具。
SQLE 获取
