MySQL error: key specification without a key length
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.
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 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.
Reference: MySQL Error 1170 (42000): BLOB/TEXT Column Used in Key Specification Without a Key Length
mysql - used in key specification without a key length
You can not define index by TEXT
/ BLOB
columns without specifying index length:
BLOB and TEXT columns also can be indexed, but a prefix length must be
given.
Logically - that is because these data types represent huge data and, therefore, index can not be created by whole field.
BLOB/TEXT column 'bestilling' used in key specification without a key length
you can't set a text as a primary key. Mysql only can Index some characters, and type text could be too big to index.
take a look here:
http://www.mydigitallife.info/mysql-error-1170-42000-blobtext-column-used-in-key-specification-without-a-key-length/
MySQL error: key specification without a key length
BLOB/TEXT column 'value' used in key specification without a key length
Looks like this is the issue (having the same issue right now), these two lines:
INDEX `IDX_ALPHANUM_INFO_TEXT_ATTRIBUTE_ID_VALUE` (`attribute_id`, `value`),
INDEX `IDX_ALPHANUM_INFO_TEXT_ENTITY_TYPE_ID_VALUE` (`entity_type_id`, `value`),
Need to have numerical values listed as such:
INDEX `IDX_ALPHANUM_INFO_TEXT_ATTRIBUTE_ID_VALUE` (`attribute_id`, `value`(255)),
INDEX `IDX_ALPHANUM_INFO_TEXT_ENTITY_TYPE_ID_VALUE` (`entity_type_id`, `value`(255)),
Plug that in, and it will work. The trick is getting it to insert correctly. For brevity's sake I won't post the whole function, but in Mage_Eav_Model_Entity_Setup::createEntityTables
at about line 1341, you need to modify these lines:
->addIndex($this->getIdxName($eavTableName, array('attribute_id', 'value')),
array('attribute_id', 'value'))
->addIndex($this->getIdxName($eavTableName, array('entity_type_id', 'value')),
array('entity_type_id', 'value'))
As follows:
->addIndex($this->getIdxName($eavTableName, array('attribute_id', 'value')),
array('attribute_id', $type == 'text' ? array('name' => 'value', 'size' => 255) : 'value'))
->addIndex($this->getIdxName($eavTableName, array('entity_type_id', 'value')),
array('entity_type_id', $type == 'text' ? array('name' => 'value', 'size' => 255) : 'value'))
I'm not quite sure what you should set the size value to, but I would think that setting it to the full 64k would defeat the purpose of indexing in the first place. Hopefully someone that knows a little bit more about sql than I will chime in.
Hope that helps.
1170 - BLOB/TEXT column 'XXX' used in key specification without a key length
long
is probably not the type you think it is. You probably want bigint
:
CREATE TABLE `ContentRepository`.`无标题` (
`weng_id` bigint NOT NULL,
`content_type` tinyint(0) NOT NULL DEFAULT 0,
. . .
)
long
is a large string (mediumtext
). It is not suitable for indexing in general.
Related Topics
How Can Prepared Statements Protect from SQL Injection Attacks
How to Return Rows That Have the Same Column Values in MySQL
MySQL - Get Row Number on Select
Aggregate Columns With Additional (Distinct) Filters
What Is the Purpose of System Table Master..Spt_Values and What Are the Meanings of Its Values
Join Between Tables in Two Different Databases
Simplest Way to Do a Recursive Self-Join
Accessing an Sqlite Database in Swift
SQL Update from One Table to Another Based on a Id Match
Cross Join VS Inner Join in Sql
Is the SQL Where Clause Short-Circuit Evaluated
How to List All Foreign Keys Referencing a Given Table in SQL Server