mysql提示used in key specification without a key length
來(lái)源：程序員人生 發(fā)布時(shí)間：2014-03-03 05:24:20 閱讀次數(shù)：6545次
今天最火軟件站小編在寫(xiě)一個(gè)PHP小東西的時(shí)候遇到了一個(gè)問(wèn)題，設(shè)計(jì)數(shù)據(jù)庫(kù)類(lèi)型的時(shí)候提示：xxxx used in key specification without a key length，百思不得其解，后來(lái)在網(wǎng)上找到關(guān)于這個(gè)問(wèn)題的答案。
原來(lái)Mysql數(shù)據(jù)庫(kù)對(duì)于BLOB/TEXT這樣類(lèi)型的數(shù)據(jù)結(jié)構(gòu)只能索引前N個(gè)字符。所以這樣的數(shù)據(jù)類(lèi)型不能作為主鍵，也不能是UNIQUE的。所以要換成VARCH,但是VARCHAR類(lèi)型的大小也不能大于255，當(dāng)VARCHAR類(lèi)型的字段大小如果大于255的時(shí)候也會(huì)轉(zhuǎn)換成小的TEXT來(lái)處理。所以也同樣有問(wèn)題。
官方的英文解釋如下：
- The error happens because MySQL can index only the first N chars of a BLOB or TEXT column. So The error mainly happen when there is a field/column type of TEXT or BLOB or those belongs to TEXT or BLOB types such as TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, and LONGTEXT that you try 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 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.
- 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.
- 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.
- Sometimes, even though you don’t use TEXT or BLOB related type in your 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 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 fail with error 1170 on key length if the column is used as primary key or unique or non-
|
生活不易，碼農(nóng)辛苦
如果您覺(jué)得本網(wǎng)站對(duì)您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈(zèng)
