mysql #1170錯誤（42000） BLOB/TEXT Column Used in Key Specification Without a Key Length

mysql  #1170錯誤（42000） BLOB/TEXT Column Used in Key Specification  Without  a  Key  Length

一下是本人翻譯國外的網站的解決方案

When creating a new table or altering an existing table with primary key,unique constraints and indexes ,or when defining  a new index with altering table manipulation statement in MySQL databases,the following error may occur and prohibit the command from completing:Error （42000） BLOB/TEXT Column Used in Key Specification  Without  a  Key  Length

在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的長度。

本文英文地址http://www.mydigitallife.info/2007/07/09/mysql-error-1170-42000-blobtext-column-used-in-key-specification-without-a-key-length/

註：英文原文略有修改，本文翻譯如意差錯，請留言，以待修正！