MySQL错误:没有密钥长度的密钥规范 [英] MySQL error: key specification without a key length

问题描述

我有一个主键为varchar(255)的表.在某些情况下，255个字符不够用.我尝试将字段更改为文本，但出现以下错误:

I have a table with a primary key that is a varchar(255). Some cases have arisen where 255 characters isn't enough. I tried changing the field to a text, but I get the following error:

BLOB/TEXT column 'message_id' used in key specification without a key length

我该如何解决?

我还应该指出，该表具有包含多个列的复合主键.

edit: I should also point out this table has a composite primary key with multiple columns.

推荐答案

发生错误是因为MySQL只能索引BLOB或TEXT列的前N个字符.因此，该错误主要发生在存在TEXT或BLOB的字段/列类型或属于TEXT或BLOB类型的字段/列类型(例如TINYBLOB，MEDIUMBLOB，LONGBLOB，TINYTEXT，MEDIUMTEXT和LONGTEXT您尝试创建主键或索引.如果使用完整的BLOB或TEXT而没有长度值，则MySQL无法保证该列的唯一性，因为它具有可变大小和动态大小.因此，当使用BLOB或TEXT类型作为索引时，必须提供N的值，以便MySQL可以确定密钥长度.但是，MySQL不支持TEXT或BLOB上的密钥长度限制. TEXT(88)根本行不通.

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/column type of TEXT or BLOB or those belong to TEXT or BLOB types such as TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, and LONGTEXT that you try to make a 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 an index, the value of N must be supplied so that MySQL can determine the key length. However, MySQL doesn’t support a key length limit on TEXT or BLOB. TEXT(88) simply won’t work.

当您尝试将表列从non-TEXT和non-BLOB类型(例如VARCHAR和ENUM)转换为TEXT或BLOB类型时，错误也会弹出.定义为唯一约束或索引. Alter Table SQL命令将失败.

The error will also pop up 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 SQL command will fail.

该问题的解决方案是从索引或唯一约束中删除TEXT或BLOB列，或将另一个字段设置为主键.如果您不能这样做，并且想在TEXT或BLOB列上设置限制，请尝试使用VARCHAR类型并在其上设置长度限制.默认情况下，VARCHAR的最大长度限制为255个字符，并且必须在声明后立即在括号内隐式指定其限制，即VARCHAR(200)将其长度限制为最多200个字符.

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

有时，即使您在表中未使用TEXT或BLOB相关类型，也可能会出现错误1170.例如，当您指定VARCHAR列作为主键，但错误地设置了它的长度或字符大小时，就会发生这种情况. VARCHAR最多只能接受256个字符，因此诸如VARCHAR(512)之类的任何内容都将迫使MySQL自动将VARCHAR(512)转换为SMALLTEXT数据类型，如果使用该列，则随后在键长错误1170上失败作为主键或唯一或非唯一索引.要解决此问题，请为VARCHAR字段指定一个小于256的数字.

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

参考: 查看全文