如何将索引的varchar(255)从utf8更改为utf8mb4，并且仍然保持在767最大密钥长度以下? [英] How can I alter an indexed varchar(255) from utf8 to utf8mb4 and still stay under the 767 max key length?

问题描述

我有一个需要支持表情符号的mysql列，这意味着将utf8列转换为utf8mb4.但是只要列被索引(不是唯一的)，我的varchar(255)就不合适了.

I have an mysql column that needs to support emoji, and that means converting a utf8 column into a utf8mb4. But my varchar(255) won't fit, so long as the column is indexed (not unique).

如何保留索引并获得utf8mb4归类?

How can I keep the index, and get the utf8mb4 collation?

我试图将长度减少到191，但不幸的是我的某些行更长，并且出现以下错误:#1406 - Data too long for column 'column_name' at row 33565(这并不是非常有用，因为我没有自动递增的列，不知道如何对第33565行进行罚款.

I've tried to just reduce the length to 191 but unfortunately some of my rows are longer and I get this error: #1406 - Data too long for column 'column_name' at row 33565 (which isn't terribly helpful since I don't have an auto-increment column and have no idea how to fine row 33565).

推荐答案

我认为它与行的最大数据长度有关，至少在我所知道的字符串数据类型中，存在这样的限制.为了避免这种情况，请尝试分离表的数据，例如使用一对一关系将表分成两个表.

I think it is connected with maximum data length of the row, there is such limitation, at least for string data types as I know. To avoid this try to separate table's data, e.g. split table into two tables using one-to-one relation.

关于最大密钥长度:我尝试创建带有索引utf8mb4字段的表，该表已成功创建，密钥长度为191，但是当我将其设置为192时，它抛出了错误-指定的密钥为太长;最大密钥长度为767字节.

About the maximum key length: I have tried to create table with indexed utf8mb4 field, it was successfully created with key length 191, but when I set it to 192, it threw an error - Specified key was too long; max key length is 767 bytes.

这篇关于如何将索引的varchar(255)从utf8更改为utf8mb4，并且仍然保持在767最大密钥长度以下?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！