mysql索引初探3

Bertha 。 2024-04-17 05:49 12阅读 0赞

创建索引错误：1170 - BLOB/TEXT column 'stu\_name' used in key specification without a key length

操作语句：

create index index\_text on t\_login1(stu\_name,stu\_num,stu\_pwd,login\_type);

表结构:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hfal9jXzEyMw_size_16_color_FFFFFF_t_70][]

出现错误原因：

Mysql数据库对于BLOB/TEXT这样类型的数据结构只能索引前N个字符，所以我们使用varchar来代替text类型

下面借鉴一下文档的说明：

在MySQL数据库中，当创建新表或者更改已存在表，这个表存在主键，并且是unique唯一性约束和索引约束时，或者是在定义一个索引来更改数据表的操作语句的时候，下面的错误信息很可能会出现，并且经过当前操作命令的完成。错误信息为BLOB或者TEXT字段使用了未指定键值长度的键

the error happens because MySQL can index only the first N chars of a BLOB or TEXT column .So the error mainly happens when  there is a field or column type of TEXT or BLOB  or those belongs to TEXT,BLOB suchu as TINYTEXT,MEDIUMTEXT,LONGTEXT ,TINYBLOB,MEDIUMBLOB and LONGBLOB that you are trying to make as primary key or index.With full BLOB or TEXT without the length value,MySQL is unable to guarantee the uniqueness of the column as it's of variable and dynamic size.So,when using BLOB or TEXT types as index,the values of the N must be supplied so that MySQL can determine the key length.However ,MySQL doesn't support limit on TEXT/BLOB .TEXT(88) simply won't work.

错误发生的原因是因为MySQL只能将BLOB/TEXT类型字段设置索引为BLOB/TEXT数据的钱N个字符，因此错误常常发生在字段被定义为TEXT/BLOB类型或者和TEXT/BLOB同质的数据类型，如TINYTEXT,MEDIUMTEXT,LONGTEXT ,TINYBLOB,MEDIUMBLOB 和LONGBLOB，并且当前操作是将这个字段设置成主键或者是索引的操作。在未指定TEXT/BLOB‘键长’的情况下，字段是变动的并且是动态的大小所以MySQL不能够保证字段的唯一性。因此当使用TEXT/BLOB类型字段做为索引时，N的值必须提供出来才可以让MySQL决定键长，但是MySQL不支持在TEXT/BLOB限制，TEXT(88)是不行的。

the error will also popup when you try to convert a table column from non-TEXT and non-BLOB type such as VARCHAR and ENUM into TEXT or BLOB type,with the column already been defined as unique constraints or index ,the alter table commmand will fail.

当你试图将数据表中的一个非TEXT或者非BLOB类型如VARCHAR或ENUM的字段转换成TEXT/BLOB，同时这个字段已经被定义了unique限制或者是索引，这个错误也会弹出，并且更改数据表的命令会执行失败

the solution to the problem is to remove the index or unique constraint from the TEXT/BLOB column ,or set another field as primary key .If you can't do that ,and wanting to place a limit on the TEXT/BLOB column,try to user VARCHAR type and place a limit of length on it.Default,VARCHAR is limited to a maximum of 255 characters and its limit must be specified within a bracket right after is declaration ,i.e VARCHAR(200) will limit to 200 characters long only.

解决方案是将unique限制和索引从TEXT/BLOB字段中移除，或者是设置另一个字段为主键，如果你不愿意这样做并且想在TEXT/BLOB上加限制，那么你可以尝试将这个字段更改为VARCHAR类型，同时给他一个限制长度，默认VARCHAR最多可以限定在255个字符，并且限制要在声明类型的右边指明，如VARCHAR(200)将会限制仅仅200个字符

Sometimes,even though you don't use TEXT orBLOB related type in you table ,the error 1170 may also appear.It happens in situation such as when you specify VARCHAR column as primary key ,but wrongly set  its length or characters size.VARCHAR can only accept up to 256 characters but something like VARCHAR(512) will force MySQL auto-convert the VARCHAR(512) to a SMALLTEXT type ,which subsequently fail with error 1170 on key length if the column is used as primary key or unique or index.To solve this problem ,specify a figure less than 256 as the size of the VARCHAR field

有时候，即使你在数据表中不使用TEXT/BLOB类型或者同质类型，error1170 也会出现，这个问题出现在当你设置一个VARCHAR字段为主键，但是却错误的设置了长度或者字符数，事实上，VARCHAR只能接受最大为256个字符串，但是你错误的设置成VARCHAR(512)等一些错误的设置，这些错误的设置会强制MySQL自动将VARCHAR(512)等转换成SMALLINT类型，同时这个字段被设置成primary key ，unique限制或者index索引等，然后执行操作就出现error 1170错误，解决问题的方法，为VARCHAR域指定小于256的长度。

借鉴地址：[https://blog.csdn.net/shaobingj126/article/details/7195909][https_blog.csdn.net_shaobingj126_article_details_7195909]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hfal9jXzEyMw_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/32e2d06110c84bb78619d9a84335b91e.png
[https_blog.csdn.net_shaobingj126_article_details_7195909]: https://blog.csdn.net/shaobingj126/article/details/7195909