当前位置： 文江博客 话题详情

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

发布于 2024-08-13 06:28:12 字数 224 浏览 4 评论 0 原文

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

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

如何解决此问题？

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

原文

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:

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.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（20）

残月升风 2024-08-20 06:28:12

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

当您尝试将表列从非TEXT和非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。要解决此问题，请将 VARCHAR 字段的大小指定为小于 256 的数字。

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

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

回复 原文
不念旧人 2024-08-20 06:28:12

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

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

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

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 767 characters for a single-byte charset like LATIN1 and only 255 characters for UTF8 (MySQL only uses BMP which requires at most 3 bytes per character)

If you need your whole column to be the PRIMARY KEY, calculate SHA1 or MD5 hash and use it as a PRIMARY KEY.

回复 原文
赏烟花じ飞满天 2024-08-20 06:28:12

您可以在 alter table 请求中指定密钥长度，例如：

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

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

alter table authors ADD UNIQUE(name_first(20), name_second(20));
回复 原文
葵雨 2024-08-20 06:28:12

MySQL 不允许对 BLOB、TEXT 和长 VARCHAR 列的完整值建立索引，因为它们包含的数据可能很大，并且隐式数据库索引也会很大，意味着没有从索引中受益。

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。

您可以通过两种方法对数据库表进行计算。我将在此数据库转储上进行演示。

假设我们想要将表 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)

如您所见，姓氏 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)

每个前缀出现的次数要多得多，这意味着我们必须增加数字 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，想象一下如果我们的表包含数百万行，这个数字将如何增长。

您可以在我的文章 N 数量的其他方法>MySQL 中的前缀索引。

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

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.

Before we go further let us define some important terms. Index selectivity is 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:

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

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

Let us now see what will happen if we increase number of indexed characters to two (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.

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_name in table employees to the index, and we want to define the smallest number N which will produce the best index selectivity.

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 Baba is the most frequent one. Now we are going to find the most frequently occurring last_name prefixes, beginning with five-letter prefixes.

+-----+--------+
| 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.

Here are results for 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.

+-----+------------+
| 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_name with indexing only first 10 characters. In table definition column last_name is 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.

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

回复 原文
奈何桥上唱咆哮 2024-08-20 06:28:12

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

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

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

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.

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));
回复 原文
嘦怹 2024-08-20 06:28:12 
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

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

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

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

回复 原文
残月升风 2024-08-20 06:28:12

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

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.

回复 原文
素衣风尘叹 2024-08-20 06:28:12

我曾经使用这个，出现与您提到的相同的错误：

CREATE INDEX idx_col1 ON my_table (col1);

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

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

I used to use this one with the same error you mentioned:

CREATE INDEX idx_col1 ON my_table (col1);

Then I replaced it with this one and solved:

CREATE INDEX idx_col1 ON my_table (col1(255));
回复 原文
夏花。依旧 2024-08-20 06:28:12

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

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

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'.

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

回复 原文
凯凯我们等你回来 2024-08-20 06:28:12

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

我不完全明白为什么。

I know it's quite late, but removing the Unique Key Constraint solved the problem. I didn't use the TEXT or LONGTEXT column as PK , but I was trying to make it unique. I got the 1170 error, but when I removed UK, the error was removed too.

I don't fully understand why.

回复 原文
最舍不得你 2024-08-20 06:28:12

到目前为止还没有人提到过......使用 utf8mb4，它是 4 字节，也可以存储表情符号（我们永远不应该再使用 3 字节 utf8），我们可以避免像 In Correct 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。自 MySQL >= 5.6.3 起，当 innodb_large_prefix=1 时可用（默认情况下禁用）对于 MySQL <= 5.7.6，默认情况下启用 MySQL >= 5.7.7）。因此，在这种情况下，我们可以将 VARCHAR(768) 用于 utf8mb4（或 VARCHAR(1024) 用于旧 utf8）作为索引列。选项 innodb_large_prefix 自 5.7.7 起已弃用，因为它的行为是内置的 MySQL 8（在此版本中已删除选项）。

Nobody mentioned it so far... with utf8mb4 which is 4-byte and can also store emoticons (we should never more use 3-byte utf8) and we can avoid errors like Incorrect string value: \xF0\x9F\x98\... we should not use typical VARCHAR(255) but rather VARCHAR(191) because in case utf8mb4 and VARCHAR(255) same part of data are stored off-page and you can not create index for column VARCHAR(255) but for VARCHAR(191) you can. It is because the maximum indexed column size is 767 bytes for ROW_FORMAT=COMPACT or ROW_FORMAT=REDUNDANT.

For newer row formats ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED (which requires newer file format innodb_file_format=Barracuda not older Antelope) maximum indexed column size is 3072. It is available since MySQL >= 5.6.3 when innodb_large_prefix=1 (disabled by default for MySQL <= 5.7.6 and enabled by default for MySQL >= 5.7.7). So in this case we can use VARCHAR(768) for utf8mb4 (or VARCHAR(1024) for old utf8) for indexed column. Option innodb_large_prefix is deprecated since 5.7.7 because its behavior is built-in MySQL 8 (in this version is option removed).

回复 原文
意中人 2024-08-20 06:28:12

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

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.

回复 原文
没有心的人 2024-08-20 06:28:12

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

The solution to the problem is that in your CREATE TABLE statement, you may add the constraint UNIQUE ( problemtextfield(300) ) after the column create definitions to specify a key length of 300 characters for a TEXT field, for example. Then the first 300 characters of the problemtextfield TEXT field would need to be unique, and any differences after that would be disregarded.

回复 原文
好久不见√ 2024-08-20 06:28:12

如果您的数据类型是 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工作台）

第1步 - 在文本框中写入
输入图片此处描述

步骤2 - 编辑数据类型，应用
输入图片此处描述

In case your data type is TEXT - you will have to change it to VARCHAR
solution 1: Query

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

solution 2: GUI (MySQL workbench)

step1 - write in the text box
enter image description here

step2 - edit data type, apply
enter image description here

回复 原文
深海蓝天 2024-08-20 06:28:12

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

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

回复 原文
谎言 2024-08-20 06:28:12

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

You have to change column type to varchar or integer for indexing.

回复 原文
晒暮凉 2024-08-20 06:28:12

转到 mysql 编辑表->将列类型更改为 varchar(45)。

Go to mysql edit table-> change column type to varchar(45).

回复 原文
于我来说 2024-08-20 06:28:12

删除该表并再次运行 Spring 项目。
这可能有帮助。
有时你会覆盖外键。

DROP that table and again run Spring Project.
That might help.
Sometime you are overriding foreignKey.

回复 原文
绮筵 2024-08-20 06:28:12

检查该列是否已建立索引并首先删除其索引。

DROP INDEX index_name ON table_name;

Check if the column is indexed and delete it's index first.

DROP INDEX index_name ON table_name;
回复 原文
陪你搞怪i 2024-08-20 06:28:12

像这样使用

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

Use like this

@Id
@Column(name = "userEmailId", length=100)
private String userEmailId;
回复 原文
~没有更多了~

关于作者

冷了相思

暂无简介

0 文章
0 评论
20 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

不再见

文章 0 评论 0

真是无聊啊

文章 0 评论 0

樱娆

文章 0 评论 0

浅语花开

文章 0 评论 0

烛光

文章 0 评论 0

绻影浮沉

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文