添加索引：BLOB/TEXT column 'xxx' used in key specification without a key length

时间：2021-03-22 00:04:46

问题

1.

将DataFrame数据保存到mysql后，添加索引出现错误提示：
　　BLOB/TEXT column used in key specification without a key length

2.

mysql> CREATE INDEX credit_creditchannel_parse_h_index ON credit_creditchannel_parse_h(custid);

1170 - BLOB/TEXT column 'custid' used in key specification without a key length

原因

出问题的原因是DataFrame对象索引的数据类型是TEXT/BLOB或其从属的类型，当将其作为mysql中的主键的时候，

如果这些数据类型缺少明确的长度值，mysql无法保证主键的唯一性，因为这个主键是一个变量，其长度是动态的。

所以当使用 TEXT/BLOB类型的数据作为索引的时候，数据的长度必须提供给mysql，使其能够明确键的长度。但是mysql不支持对TEXT/BLOB长度的限制。

同样的错误也会出现在试图将非TEXT/BLOB类型的数据列转换成TEXT/BLOB类型，这些列被定义成独立的索引，AlterTable命令会失效。

解决方法

1.

mysql> alter table credit_creditchannel_parse_h modify column custid varchar(9);

Query OK, 481562 rows affected (11.26 sec)

Records: 481562 Duplicates: 0 Warnings: 0

r

mysql> CREATE INDEX credit_creditchannel_parse_h ON credit_creditchannel_parse_h (custid); 
Query OK, 0 rows affected (2.44 sec) Records: 0 Duplicates: 0 Warnings: 0

对于pandas应该如下：

1.指定dtype的code字段的数据类型变为varchar(max)

解决这个问题的方式之一是舍弃TEXT/BLOB列作为索引，或者设置另外的列作为主键。如果不得不设置成主键，而想限制TEXT/BLOB的长度，可以尝试使用VARCHAR并设置其长度。VARCHAR默认长度是255个字符，并且其长度必须在其声明之后在其括号中设置，例如，VARCHAR(200)将其设置成200个字符长度。

将DataFrame数据输出到mysql时强制将索引转换成VARCHAR并限制其长度，其中的code是索引的标签：

data.to_sql('data',engine,if_exists='replace',dtype={'code':VARCHAR(data.index.get_level_values('code').str.len().max())})

2.提前建表,truncate table+data.to_sql(if_exists='append')

相关文章