Mysql prompts used in key specification without a key length

Source: Internet
Author: User

Today, the most popular software station editor encountered a problem when writing a small PHP item. When designing the database type, it prompts: xxxx used in key specification without a key length, which cannot be solved by any means, later, I found the answer to this question on the Internet.

In the original Mysql database, only the first N characters of BLOB/TEXT data structures can be indexed. Therefore, such a data type cannot be used as the primary key or UNIQUE. Therefore, the VARCHAR type must be changed to VARCH, but the VARCHAR type cannot exceed 255. If the field size of the VARCHAR type is greater than 255, it will be converted to a small TEXT for processing. So there is also a problem.

The official English explanation is as follows:

  1. 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.
  2. 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.
  3. 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.
  4. 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-

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.