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

小开

最佳答案

发生错误是因为MySQL只能索引BLOB或TEXT列的前N个字符。因此，当存在TEXT或BLOB的字段/列类型或属于TEXT或BLOB类型的字段/列类型(如TINYBLOB， MEDIUMBLOB， LONGBLOB， TINYTEXT， MEDIUMTEXT和LONGTEXT时，您试图创建主键或索引，则错误主要发生。完整的BLOB或TEXT没有长度值，MySQL无法保证列的唯一性，因为它是可变的和动态的大小。因此，当使用BLOB或TEXT类型作为索引时，必须提供N的值，以便MySQL可以确定键的长度。但是，MySQL不支持在TEXT或BLOB上设置键长度限制。TEXT6根本不起作用。

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

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

有时，即使你没有在你的表中使用TEXT或BLOB相关类型，错误1170也会出现。当你指定VARCHAR列作为主键，但错误地设置了它的长度或字符大小时，就会发生这种情况。VARCHAR最多只能接受256个字符，因此诸如VARCHAR(512)之类的东西将迫使MySQL自动将VARCHAR(512)转换为SMALLTEXT数据类型，如果列被用作主键或唯一或非唯一索引，则会失败，键长度错误为1170。要解决这个问题，指定一个小于256的数字作为VARCHAR字段的大小。

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

小开

你应该定义你想要索引TEXT列的前导部分。

InnoDB对每个索引键有一个768字节的限制，你不能创建一个超过这个长度的索引。

这将很好地工作:

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

注意，键大小的最大值取决于列字符集。对于像LATIN1这样的单字节字符集，它是767字符，对于UTF8只使用255字符(MySQL只使用BMP，每个字符最多需要3字节)

如果你需要整个列都是PRIMARY KEY，计算SHA1或MD5哈希并将其用作PRIMARY KEY。

小开

添加另一个varChar(255)列(默认为空字符串而不是null)，以在255个字符不够时保存溢出，并将此PK更改为使用两个列。然而，这听起来不像一个设计良好的数据库模式，我建议找一个数据建模师来看看你所拥有的，并对其进行重构以获得更多的规范化。

小开

你可以在alter table请求中指定键的长度，就像这样:

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

小开

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

相反，使用一个代理int键作为主键(使用auto_increment)，并使用长键作为辅助UNIQUE。

小开

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

小开

另一种很好的处理方法是创建没有唯一约束的TEXT字段，并添加一个兄弟VARCHAR字段，该字段是唯一的，并且包含TEXT字段的摘要(MD5、SHA1等)。当您插入或更新TEXT字段时，计算并存储整个TEXT字段的摘要，这样您就可以对整个TEXT字段(而不是一些前面的部分)有一个可以快速搜索的唯一性约束。

小开

MySQL不允许索引BLOB， TEXT和长VARCHAR列的全值，因为它们包含的数据可能很大，隐式DB索引也很大，这意味着索引没有好处。

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

在进一步讨论之前，让我们先定义一些重要的术语。索引的选择性是总不同索引值和总行数的比值。下面是一个测试表的例子:

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

如果我们只索引第一个字符(N=1)，那么索引表将如下表所示:

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

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

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

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

在这个场景中，IS=2/3=0.66，这意味着我们增加了索引的选择性，但我们也增加了索引的大小。诀窍是找到最小的数字N，这将导致最大的索引的选择性。

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

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

首先让我们来看看最常见的姓氏:

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)

如你所见，姓氏巴巴是最常见的。现在我们将找到最常出现的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)

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

下面是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    |
+-----+-----------+

下面是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)

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

你可以在我的帖子MySQL中的前缀索引中阅读其他计算N的方法。

小开

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

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

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

小开

这个问题的解决方案是，在你的CREATE TABLE语句中，你可以在列创建定义之后添加约束UNIQUE ( problemtextfield(300) )，例如为TEXT字段指定长度为300字符的key。那么problemtextfield TEXT字段的第一个300字符必须是唯一的，之后的任何差异将被忽略。

小开

像这样使用

@Id
@Column(name = "userEmailId", length=100)
private String userEmailId;

小开

为了索引，你必须将列类型更改为varchar或integer。

小开

到mysql edit table->修改列类型为varchar(45)

小开

到目前为止还没有人提到……使用utf8mb4，它是4字节，也可以存储表情符号(我们不应该更多地使用3字节utf8)，我们可以避免像Incorrect string value: \xF0\x9F\x98\...这样的错误，我们不应该使用典型的VARCHAR (255)，而是VARCHAR (191)，因为如果utf8mb4和VARCHAR(255)相同部分的数据存储在页外，并且您不能为列VARCHAR(255)创建索引，但对于VARCHAR(191)可以。这是因为ROW_FORMAT=COMPACT或ROW_FORMAT= redundancy的最大索引列大小为767字节。

对于较新的行格式ROW_FORMAT=DYNAMIC或ROW_FORMAT=COMPRESSED(需要较新的文件格式innodb_file_format=Barracuda而不是较旧的Antelope)，最大索引列大小为3072。当innodb_large_prefix=1时，从MySQL >= 5.6.3开始使用(MySQL < = 5.7.6默认禁用，MySQL >= 5.7.7默认启用)。因此，在本例中，我们可以对索引列使用VARCHAR(768)用于utf8mb4(或VARCHAR(1024)用于旧utf8)。选项innodb_large_prefix自5.7.7起已弃用，因为它的行为是内置的MySQL 8(在这个版本中该选项被移除)。

小开

我得到这个错误时，添加一个索引的文本类型列表。您需要声明每个文本类型要使用的大小。

将大小放入括号()内

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

添加索引

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

添加唯一索引

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

小开

我知道有点晚了，但是删除的Unique Key约束解决了这个问题。我没有使用TEXT或LONGTEXT列作为PK，但我试图使它独特的。我得到了1170 error，但当我删除UK时，错误也被删除了。

我不完全明白为什么。

小开

DROP该表并再次运行Spring Project。这可能会有所帮助。有时候你重写了foreignKey.

小开

如果您的数据类型是TEXT -您将不得不将其更改为VARCHAR
处理1:查询

ALTER TABLE table_name MODIFY COLUMN col_name datatype;
ALTER TABLE my_table MODIFY COLUMN my_col VARCHAR(255);

方案2:GUI (MySQL工作台)
step1 -在文本框中写入 < / p >

step2 -编辑数据类型，应用 < / p >

小开

我曾经用过这个，也犯过你提到的错误:

CREATE INDEX idx_col1 ON my_table (col1);

然后我把它换成了这个，并解决了:

CREATE INDEX idx_col1 ON my_table (col1(255));