MySQL #1170 error (42000) blob/Text Column Used in key specification without a key length

MySQL #1170 error (42000) blob/Text Column Used in key specification without a key length

Here is my solution to translating foreign websites.

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

In a MySQL database, when creating a new table or changing an existing table, the table has a primary key and is subject to unique uniqueness and index constraints, or when you define an index to change the operation statement of a data table, the following error message may appear and is completed by the current operation command. The error message is that the blob or text field uses a key with no length specified.

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.

The error occurs because MySQL can only set blob/text fields to index blob/text data with n characters, therefore, errors often occur when fields are defined as text/blob or data types that are the same as text/blob, such as tinytext, mediumtext, longtext, tinyblob, mediumblob, and longblob, in addition, the current operation is to set this field as a primary key or index. If the text/BLOB 'key length' is not specified, the field is changed and the size is dynamic. Therefore, MySQL cannot guarantee the uniqueness of the field. Therefore, when the text/BLOB field is used as the index, the value of N must be provided for MySQL to determine the key length, but MySQL does not support text/BLOB restrictions, text (88) no.

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.

When you try to convert a non-text or non-blob type field such as varchar or enum in the data table to text/blob, this field has been defined as a unique limit or index, this error will also pop up, and the command for changing the data table will fail to be executed.

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.

The solution is to remove the unique restriction and index from the text/BLOB field, or set another field as the primary key. If you do not want to do this and want to add restrictions on text/blob, then you can try to change this field to the varchar type and give it a limited length. By default, the varchar can be limited to 255 characters and must be specified on the right of the declared type, for example, varchar (200) can contain only 200 characters.

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

Sometimes, even if you do not use the text/BLOB type or homogeneous type in the data table, error1170 also occurs. This problem occurs when you set a varchar field as the primary key, however, the length or number of characters is incorrectly set. In fact, varchar can only accept up to 256 strings, but you mistakenly set it to varchar (512) and other incorrect settings, these incorrect settings will force MySQL to automatically convert varchar (512) to the smallint type, and this field will be set to primary key, unique limit or index, then, the error 1170 is displayed. To solve the problem, specify a length less than 256 For the varchar field.

English address http://www.mydigitallife.info/2007/07/09/mysql-error-1170-42000-blobtext-column-used-in-key-specification-without-a-key-length/

Note: the original English text is slightly modified. Please leave a message to be corrected if this article is incorrect!