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

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

当您尝试将表列从 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 Error 1170 (42000): BLOB/TEXT Column Used in Key Specification Without a Key Length

---

您应该定义要索引的 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。

---

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

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

---

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。

有两种方法可以对数据库表进行计算。我将在this database dump上进行演示。

假设我们想要将表employees 中的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，想象一下如果我们的表包含数百万行，这个数字会如何增长。

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

---

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

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

如果使用了太多字节，您可以在 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));

---

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

---

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

---

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

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

---

我知道已经很晚了，但是删除 Unique Key 约束解决了这个问题。我没有将 TEXT 或 LONGTEXT 列用作 PK ，但我试图使其唯一。我得到了 1170 error，但是当我删除 UK 时，错误也被删除了。

我不完全明白为什么。

---

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

---

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

---

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

---

到目前为止没有人提到它......使用 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=REDUNDANT 的最大索引列大小为 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）作为索引列。自 5.7.7 以来不推荐使用选项 innodb_large_prefix，因为它的行为是内置的 MySQL 8（在此版本中已删除选项）。

---

如果您的数据类型是 TEXT - 您必须将其更改为 VARCHAR 解决方案 1：查询

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

https://i.stack.imgur.com/8QfIn.png

https://i.stack.imgur.com/ocMMk.png

---

我曾经使用过你提到的同样的错误：

CREATE INDEX idx_col1 ON my_table (col1);

然后我用这个替换它并解决了：

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

---

您必须将列类型更改为 varchar 或 integer 才能建立索引。

---

转到 mysql edit table->将列类型更改为 varchar(45)。

---

删除该表并再次运行 Spring Project。这可能会有所帮助。有时您会覆盖foreignKey。

---

像这样使用

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

---