背景
使用工具将 Oracle 表结构转 mysql 表结构后喜提报错：
ERROR 1170 (42000): BLOB/TEXT column 'xml' used in key specification without a key length
关于导入的方式
由于当时使用的是，登录后再 source xx.sql
，所以报错了还不太好找，这不炸了吗：
[root@localhost testddl]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.32 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@localhost [(none)] 23:26:14 > use ddl;
Database changed
root@localhost [ddl] 23:26:20 > source longtextidx.sql ;
ERROR 1170 (42000): BLOB/TEXT column 'xml' used in key specification without a key length
文件的行数都没显示，只有一个 xml
的字段名，表比较多找半天，所以迁移数据一定要在命令行之外使用 -e
或者 <
，要不然指不定就报错了你还不知道哪里报错了。
如：
[root@localhost testddl]# mysql -f ddl < longtextidx.sql
ERROR 1170 (42000) at line 1: BLOB/TEXT column 'xml' used in key specification without a key length
这样就会显示行数。
为什么报错 1170
从 key specification without a key length
这里大概能猜到可能是约束错误，大概率是索引（index
/key
）。
找到那个报错的表，我这里是测试数据就一个表：
[root@localhost testddl]# cat longtextidx.sql
create table longtextidx(id int primary key,xml longtext,key idx_xml(xml));
直接拿到建表语句就能看见关于 xml 的约束 key idx_xml(xml)
出现的问题。
为什么这里指示没有长度限制，我这里猜到是 mysql 对于 BLOB/TEXT 上索引做的限制，因为对这两种数据类型的存储大概率是长数据，这就会导致 2 个问题：
索引选择性 索引树大小
即使建了这个索引也不一定会走，反而增加优化器选择最佳索引的成本和对表 DML 更新二级索引的成本，所以有这个要求对 BLOB/TEXT 做前缀索引的要求。
文档的描述：
*In most respects, you can regard a
BLOB
column as aVARBINARY
column that can be as large as you like. Similarly, you can regard aTEXT
column as aVARCHAR
column.BLOB
andTEXT
differ fromVARBINARY
andVARCHAR
in the following ways:”
For indexes on BLOB
andTEXT
columns, you must specify an index prefix length. ForCHAR
andVARCHAR
, a prefix length is optional. See Section 10.3.5, “Column Indexes”.BLOB
andTEXT
columns cannot haveDEFAULT
values.
如何选择最佳前缀数
使用这个 SQL 去计算选择性：
select count(distinct substring(column,1,n))/count(*) from table;
例如刚才的这个表就可以这么做：
select count(distinct substring(xml,1,50))/count(*) from longtextidx;
截取前 50 个字符判断对于全量的区分度：
当结果接近 1 时，表前 50 个字符能很好地区分数据，每个前缀值几乎唯一 当结果远小于 1 时，表示前 50 个字符的重复值较多，无法很好地区分数据。
取的值越大存储空间就越大，取一个存储空间和选择性的最佳值即可，如 0.6~0.7
创建前缀索引
示例：
create index idx_xml on longtextidx(xml(50));
使用 REDUNDANT
或 COMPACT
行格式的 InnoDB 表，前缀最长为 767 字节。使用 DYNAMIC
或 COMPRESSED
行格式的 InnoDB 表，前缀长度限制为 3072 字节。
前缀限制是以字节为单位测量的，而在 CREATE TABLE
、ALTER TABLE
和 CREATE INDEX
语句中，非二进制字符串类 ( CHAR
, VARCHAR
, TEXT
) 的前缀长度是按字符数解释的，二进制字符串类型（BINARY
、VARBINARY
、BLOB
）的前缀长度是按字节数解释的。在为使用多字节字符集的非二进制字符串列指定前缀长度时，请注意这一点。
参考
https://dev.mysql.com/doc/refman/8.0/en/blob.html
往期回顾
MySQL Resource Group - 使用资源组限制 SQL 使用的 CPU 数 更改账户身份验证插件注意事项 主从切换后导致 Event 停滞 and 主从故障 - Event 导致主从停滞的案例 INSERT 加什么锁 - 聊聊 INSERT 的加锁情况 MySQL 临时表空间 - 临时表空间（Temporary Tablespaces 及相关的变量 MySQL/PG 数据脱敏 - data_masking 插件和 postgresql_anonymizer 扩展初体验 MySQL/PG 对事务 DDL 的支持程度 - 从隐式提交聊到DDL事务 ONLINE DDL 收尾篇|如何有效实现 ONLINE - 关于 ONLINE DDL 的最佳实践 MySQL/PG 索引对于排序规则的依赖性 - 排序规则与索引的相关性问题，涉及到索引是否会失效 如何正确杀会话， KILL processlist_id/KILL QUERY？ - 你知道如何正确杀掉活跃会话吗？
感谢阅读！关注我获取更多精彩内容。
