转MySQL utf8mb4后，无索引计划的VARCHAR(255)需改191吗？

Great question—this is one of the most frequent gotchas teams run into when switching MySQL databases to utf8mb4, so let’s unpack it step by step.

First, let’s recap why the 191 limit exists for indexed columns:

• InnoDB has a default index prefix length limit of 767 bytes (for older versions or when innodb_large_prefix is disabled).
• Since utf8mb4 uses up to 4 bytes per character, the maximum VARCHAR length for an indexed column is 767 / 4 = ~191 (we round down to avoid exceeding the byte limit). That’s why setting an indexed VARCHAR to 255 throws an error—255 * 4 = 1020 bytes, which is way over the 767 limit.

Now, for non-indexed VARCHAR(255) columns:

• As you tested, these don’t throw errors because the 767-byte limit only applies to index entries, not the actual storage of the column data. MySQL can handle storing 255 utf8mb4 characters just fine here.

So, do you need to shorten them to 191? It depends on your future plans:

• If you’re 100% sure this column will never have an index added: Leave it at 255. There’s no technical reason to change it—storage works, performance isn’t impacted, and you retain the full length capacity if you need it later.
• If there’s any chance you’ll add an index to this column down the line: Shorten it to 191 now. This avoids a future headache where trying to add an index suddenly fails, forcing you to modify the column (and potentially truncate data if any entries are longer than 191 characters) in the middle of a feature rollout.

One quick side note: If you’re running MySQL 5.7.7 or newer, innodb_large_prefix is enabled by default, which raises the index prefix limit to 3072 bytes. With this setting, even indexed VARCHAR(255) columns work (255 * 4 = 1020 < 3072). But if you need to maintain compatibility with older MySQL versions or environments where this setting is disabled, sticking to 191 for potentially indexed columns is still the safer bet.

内容的提问来源于stack exchange，提问作者Mike Pearson