問題描述

我有一個主鍵是 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 列在沒有密鑰長度的密鑰規范中使用

這篇關于MySQL 錯誤:沒有密鑰長度的密鑰規范的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！