MySQL 错误:没有密钥长度的密钥规范
我有一个主键是 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
我该如何解决这个问题?
how can I fix this?
我还应该指出这个表有一个多列的复合主键.
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 类型，列已经定义为唯一约束或索引.更改表 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.要解决此问题，请指定小于 256 的数字作为 VARCHAR 字段的大小.
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.
参考:MySQL 错误 1170 (42000):BLOB/TEXT 列在没有密钥长度的密钥规范中使用
相关文章