MySQL 错误：没有密钥长度的密钥规范

MySQL error: key specification without a key length

提问by GSto

I have a table with a primary key that is a varchar(255). Some cases have arisen where 255 characters isn't enough. I tried changing the field to a text, but I get the following error:

我有一个主键是 varchar(255) 的表。在某些情况下，255 个字符是不够的。我尝试将字段更改为文本，但出现以下错误：

BLOB/TEXT column 'message_id' used in key specification without a key length

how can I fix this?

我怎样才能解决这个问题？

edit: I should also point out this table has a composite primary key with multiple columns.

编辑：我还应该指出这个表有一个多列的复合主键。

回答by OMG Ponies

The error happens because MySQL can index only the first N chars of a BLOB or TEXTcolumn. So The error mainly happens when there is a field/column type of TEXTor BLOB or those belong to TEXTor BLOBtypes such as TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, and LONGTEXTthat you try to make a primary key or index. With full BLOBor TEXTwithout the length value, MySQL is unable to guarantee the uniqueness of the column as it's of variable and dynamic size. So, when using BLOBor TEXTtypes 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 TEXTor BLOB. TEXT(88)simply won't work.

发生错误是因为 MySQL 只能索引 BLOB 或TEXT列的前 N ​​个字符。因此，该错误主要发生在字段/列类型为TEXT或 BLOB 或属于TEXT或BLOB类型如TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, 并且LONGTEXT您尝试创建主键或索引时。BLOB无论TEXT是否有完整的长度值，MySQL 都无法保证列的唯一性，因为它是可变的和动态的大小。因此，当使用BLOBorTEXT类型作为索引时，必须提供 N 的值，以便 MySQL 可以确定键长度。但是，MySQL 不支持TEXT或的密钥长度限制BLOB。TEXT(88)根本行不通。

The error will also pop up when you try to convert a table column from non-TEXTand non-BLOBtype such as VARCHARand ENUMinto TEXTor BLOBtype, with the column already been defined as unique constraints or index. The Alter Table SQL command will fail.

该错误也将弹出当您尝试表列从转换non-TEXT和non-BLOB类型，如VARCHAR和ENUM成TEXT或BLOB类型，与已被定义为唯一约束或索引的列。更改表 SQL 命令将失败。

The solution to the problem is to remove the TEXTor BLOBcolumn 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 TEXTor BLOBcolumn, try to use VARCHARtype and place a limit of length on it. By default, VARCHARis 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.

问题的解决方法是从索引或唯一约束中删除TEXT或BLOB列或设置另一个字段为主键。如果您不能这样做，并且想对TEXTorBLOB列设置限制，请尝试使用VARCHARtype 并对其设置长度限制。默认情况下，VARCHAR限制为最多 255 个字符，并且必须在声明后立即在括号内隐式指定其限制，即仅VARCHAR(200)将其限制为 200 个字符。

Sometimes, even though you don't use TEXTor BLOBrelated type in your table, the Error 1170 may also appear. It happens in a situation such as when you specify VARCHARcolumn as primary key, but wrongly set its length or characters size. VARCHARcan only accepts up to 256 characters, so anything such as VARCHAR(512)will force MySQL to auto-convert the VARCHAR(512)to a SMALLTEXTdatatype, 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 VARCHARfield.

有时，即使您没有在表中使用TEXT或BLOB关联类型，也可能会出现错误 1170。它发生在诸如将VARCHAR列指定为主键，但错误地设置其长度或字符大小的情况下。VARCHAR最多只能接受 256 个字符，因此任何诸如VARCHAR(512)将强制 MySQL 将 自动转换VARCHAR(512)为SMALLTEXT数据类型的内容，如果该列用作主键或唯一或非唯一索引，则随后失败并在键长度上出现错误 1170。要解决此问题，请指定小于 256 的数字作为VARCHAR字段大小。

Reference: MySQL Error 1170 (42000): BLOB/TEXT Column Used in Key Specification Without a Key Length

参考：MySQL 错误 1170 (42000)：在没有密钥长度的密钥规范中使用的 BLOB/TEXT 列

回答by Quassnoi

You should define which leading portion of a TEXTcolumn you want to index.

您应该定义TEXT要索引的列的哪个前导部分。

InnoDBhas a limitation of 768bytes per index key and you won't be able to create an index longer than that.

InnoDB768每个索引键的字节数有限制，您将无法创建更长的索引。

This will work fine:

这将正常工作：

CREATE TABLE t_length (
      mydata TEXT NOT NULL,
      KEY ix_length_mydata (mydata(255)))
    ENGINE=InnoDB;

Note that the maximum value of the key size depends on the column charset. It's 767characters for a single-byte charset like LATIN1and only 255characters for UTF8(MySQLonly uses BMPwhich requires at most 3bytes per character)

请注意，键大小的最大值取决于列字符集。它767是单字节字符集的字符，LATIN1并且仅255用于UTF8（MySQL仅使用每个字符BMP最多需要3字节）

If you need your whole column to be the PRIMARY KEY, calculate SHA1or MD5hash and use it as a PRIMARY KEY.

如果您需要将整个列作为PRIMARY KEY，请计算SHA1或MD5散列并将其用作PRIMARY KEY.

回答by Mike Evans

You can specify the key length in the alter table request, something like:

您可以在更改表请求中指定密钥长度，例如：

alter table authors ADD UNIQUE(name_first(20), name_second(20));

回答by MrD

MySQL disallows indexing a full value of BLOB, TEXTand long VARCHARcolumns because data they contain can be huge, and implicitly DB index will be big, meaning no benefit from index.

MySQL 不允许索引完整的BLOB,TEXT和 longVARCHAR列，因为它们包含的数据可能很大，并且隐式 DB 索引将很大，这意味着索引没有任何好处。

MySQL requires that you define first N characters to be indexed, and the trick is to choose a number N that's long enough to give good selectivity, but short enough to save space. The prefix should be long enough to make the index nearly as useful as it would be if you'd indexed the whole column.

MySQL 要求您定义要索引的前 N ​​个字符，诀窍是选择一个足够长的数字 N 以提供良好的选择性，但又足够短以节省空间。前缀应该足够长，以使索引几乎与索引整个列时一样有用。

Before we go further let us define some important terms. Index selectivityis ratio of the total distinct indexed values and total number of rows. Here is one example for test table:

在我们进一步讨论之前，让我们定义一些重要的术语。索引选择性是总不同索引值与总行数的比率。这是测试表的一个示例：

+-----+-----------+
| id  | value     |
+-----+-----------+
| 1   | abc       |
| 2   | abd       |
| 3   | adg       |
+-----+-----------+

If we index only the first character (N=1), then index table will look like the following table:

如果我们只索引第一个字符（N=1），那么索引表将如下表所示：

+---------------+-----------+
| indexedValue  | rows      |
+---------------+-----------+
| a             | 1,2,3     |
+---------------+-----------+

In this case, index selectivity is equal to IS=1/3 = 0.33.

在这种情况下，索引选择性等于 IS=1/3 = 0.33。

Let us now see what will happen if we increase number of indexed characters to two (N=2).

现在让我们看看如果我们将索引字符的数量增加到两个（N=2）会发生什么。

+---------------+-----------+
| indexedValue  | rows      |
+---------------+-----------+
| ab             | 1,2      |
| ad             | 3        |
+---------------+-----------+

In this scenario IS=2/3=0.66 which means we increased index selectivity, but we have also increased the size of index. Trick is to find the minimal number N which will result to maximal index selectivity.

在这种情况下，IS=2/3=0.66 这意味着我们增加了索引选择性，但我们也增加了索引的大小。关键是要找到的最小数N，这将导致以最大的索引选择性。

There are two approaches you can do calculations for your database table. I will make demonstration on the this database dump.

有两种方法可以对数据库表进行计算。我将对此数据库转储进行演示。

Let's say we want to add column last_namein table employeesto the index, and we want to define the smallest number Nwhich will produce the best index selectivity.

假设我们要将表雇员中的列last_name添加到索引中，并且我们希望定义将产生最佳索引选择性的最小数字N。

First let us identify the most frequent last names:

首先让我们找出最常见的姓氏：

select count(*) as cnt, last_name 
from employees 
group by employees.last_name 
order by cnt

+-----+-------------+
| cnt | last_name   |
+-----+-------------+
| 226 | Baba        |
| 223 | Coorg       |
| 223 | Gelosh      |
| 222 | Farris      |
| 222 | Sudbeck     |
| 221 | Adachi      |
| 220 | Osgood      |
| 218 | Neiman      |
| 218 | Mandell     |
| 218 | Masada      |
| 217 | Boudaillier |
| 217 | Wendorf     |
| 216 | Pettis      |
| 216 | Solares     |
| 216 | Mahnke      |
+-----+-------------+
15 rows in set (0.64 sec)

As you can see, the last name Babais the most frequent one. Now we are going to find the most frequently occurring last_nameprefixes, beginning with five-letter prefixes.

如您所见，姓氏Baba是最常见的名字。现在我们要找到最常出现的last_name前缀，从五个字母的前缀开始。

+-----+--------+
| cnt | prefix |
+-----+--------+
| 794 | Schaa  |
| 758 | Mande  |
| 711 | Schwa  |
| 562 | Angel  |
| 561 | Gecse  |
| 555 | Delgr  |
| 550 | Berna  |
| 547 | Peter  |
| 543 | Cappe  |
| 539 | Stran  |
| 534 | Canna  |
| 485 | Georg  |
| 417 | Neima  |
| 398 | Petti  |
| 398 | Duclo  |
+-----+--------+
15 rows in set (0.55 sec)

There are much more occurrences of every prefix, which means we have to increase number N until the values are almost the same as in the previous example.

每个前缀的出现次数要多得多，这意味着我们必须增加数字 N 直到值几乎与前一个示例中的值相同。

Here are results for N=9

这是 N=9 的结果

select count(*) as cnt, left(last_name,9) as prefix 
from employees 
group by prefix 
order by cnt desc 
limit 0,15;

+-----+-----------+
| cnt | prefix    |
+-----+-----------+
| 336 | Schwartzb |
| 226 | Baba      |
| 223 | Coorg     |
| 223 | Gelosh    |
| 222 | Sudbeck   |
| 222 | Farris    |
| 221 | Adachi    |
| 220 | Osgood    |
| 218 | Mandell   |
| 218 | Neiman    |
| 218 | Masada    |
| 217 | Wendorf   |
| 217 | Boudailli |
| 216 | Cummings  |
| 216 | Pettis    |
+-----+-----------+

Here are results for N=10.

这是 N=10 的结果。

+-----+------------+
| cnt | prefix     |
+-----+------------+
| 226 | Baba       |
| 223 | Coorg      |
| 223 | Gelosh     |
| 222 | Sudbeck    |
| 222 | Farris     |
| 221 | Adachi     |
| 220 | Osgood     |
| 218 | Mandell    |
| 218 | Neiman     |
| 218 | Masada     |
| 217 | Wendorf    |
| 217 | Boudaillie |
| 216 | Cummings   |
| 216 | Pettis     |
| 216 | Solares    |
+-----+------------+
15 rows in set (0.56 sec)

This are very good results. This means that we can make index on column last_namewith indexing only first 10 characters. In table definition column last_nameis defined as VARCHAR(16), and this means we have saved 6 bytes (or more if there are UTF8 characters in the last name) per entry. In this table there are 1637 distinct values multiplied by 6 bytes is about 9KB, and imagine how this number would grow if our table contains million of rows.

这是非常好的结果。这意味着我们可以在last_name只索引前 10 个字符的列上创建索引。在表定义中，列last_name被定义为VARCHAR(16)，这意味着我们为每个条目节省了 6 个字节（如果姓氏中有 UTF8 字符则更多）。在这个表中有 1637 个不同的值乘以 6 个字节大约是 9KB，想象一下如果我们的表包含数百万行，这个数字会如何增长。

You can read other ways of calculating number of Nin my post Prefixed indexes in MySQL.

您可以在我的帖子Prefixed index in MySQL 中阅读其他计算N数的方法。

回答by stealth

I got this error when adding an index to a table with text type columns. You need to declare the size amount you want to use for each text type.

向具有文本类型列的表添加索引时出现此错误。您需要声明要用于每种文本类型的大小数量。

Put the size amount within the parenthesis ( )

将大小数量放在括号 ( ) 内

If too many bytes are used you can declare a size in the brackets for varchar to decrease the amount used for indexing. This is even if you declared a size for a type already like varchar(1000). You don't need to create a new table like others have said.

如果使用了太多字节，您可以在 varchar 的括号中声明一个大小以减少用于索引的数量。即使您为已经像 varchar(1000) 这样的类型声明了大小也是如此。您不需要像其他人所说的那样创建新表。

Adding index

添加索引

alter table test add index index_name(col1(255),col2(255));

Adding unique index

添加唯一索引

alter table test add unique index_name(col1(255),col2(255));

回答by Abhishek Goel

alter table authors ADD UNIQUE(name_first(767), name_second(767));

NOTE: 767is the number of characters limit upto which MySQL will index columns while dealing with blob/text indexes

注意：767是 MySQL 在处理 blob/text 索引时将索引列的字符数限制

Ref : http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

参考：http: //dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

回答by par

Another excellent way of dealing with this is to create your TEXT field without the unique constraint and add a sibling VARCHAR field that is unique and contains a digest (MD5, SHA1, etc.) of the TEXT field. Calculate and store the digest over the entire TEXT field when you insert or update the TEXT field then you have a uniqueness constraint over the entire TEXT field (rather than some leading portion) that can be searched quickly.

解决此问题的另一种极好方法是创建没有唯一约束的 TEXT 字段，并添加一个唯一的同级 VARCHAR 字段，该字段包含 TEXT 字段的摘要（MD5、SHA1 等）。当您插入或更新 TEXT 字段时，计算并存储整个 TEXT 字段的摘要，然后您就可以快速搜索整个 TEXT 字段（而不是某些前导部分）的唯一性约束。

回答by Per Lindberg

Don't have long values as primary key. That will destroy your performance. See the mysql manual, section 13.6.13 'InnoDB Performance Tuning and Troubleshooting'.

不要将长值作为主键。那会毁了你的表现。请参阅 mysql 手册，第 13.6.13 节“InnoDB 性能调整和故障排除”。

Instead, have a surrogate int key as primary (with auto_increment), and your loong key as a secondary UNIQUE.

相反，将代理 int 键作为主键（使用 auto_increment），并将长键作为辅助 UNIQUE。

回答by Charles Bretana

Add another varChar(255) column (with default as empty string not null) to hold the overflow when 255 chars are not enough, and change this PK to use both columns. This does not sound like a well designed database schema however, and I would recommend getting a data modeler to look at what you have with a view towards refactoring it for more Normalization.

添加另一个 varChar(255) 列（默认为空字符串不为空）以在 255 个字符不够时保持溢出，并更改此 PK 以使用两列。然而，这听起来不像是一个设计良好的数据库模式，我建议让数据建模人员查看您拥有的内容，以便对其进行重构以实现更多的规范化。

回答by Alexander Valinurov

Also, if you want to use index in this field, you should use the MyISAM storage engine and the FULLTEXT index type.

另外，如果要在该字段中使用索引，则应使用 MyISAM 存储引擎和 FULLTEXT 索引类型。