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

如果你的数据类型是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 -在文本框中写入

Step2 -编辑数据类型，应用

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

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.

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

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

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.

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

我知道现在已经很晚了，但是删除唯一键约束解决了这个问题。我没有使用TEXT或LONGTEXT列作为PK，但我试图使它独特。我得到了1170的错误，但当我删除了英国，错误也被删除了。

我不完全明白为什么。

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