MySQL VARCHAR 索引在迁移期间被截断为 255 [英] MySQL VARCHAR index truncated to 255 during migration

问题描述

对于在这种情况下会发生什么，我有点困惑.我们有一个列，例如 SQL Server 中的 VARCHAR(500).迁移工具 ( MySQL Workbench ) 将索引截断为 255.我发现的另一个 post以及 Google 上的许多其他地方，建议 255 是基于 VARCHAR 的索引的限制.从这个此处发布，我看到它是前 255 个字符.但我对在实际实践中如何运作感到有些困惑.换句话说，如果运气不好，我正在寻找实际列中 255 点之后的子字符串(使用like"子句)，会发生什么?或者，如果我使用="、>"等比较列，但在比较字符串中使用超过 255 个，会发生什么.在这种棘手的情况下，它只是忽略索引，还是什么?

I am a little confused as to what happens in this situation. We have a column that is, say, VARCHAR(500) in SQL Server. The migration tool ( MySQL Workbench ) truncates the index to 255. This other post I found, plus many other places on Google, suggest that 255 is the limit for VARCHAR based indexes. From this post here, I see that it is the first 255 characters. But I am sort of confused as to how that works out in actual practice. In other words, if, by dumb luck, I am looking for a substring ( using the "like" clause ) that is after the 255 point in the real column, what would happen? Or what would happen if I compare the column using "=", ">", etc, but use more than 255 in comparison string. In these kind of screwy situations, does it just ignore the index, or what?

对 MySQL 有点陌生，多年来一直在使用 SQL Server，所以只是想了解会发生什么.谢谢.

Sort of new to MySQL, been working with SQL Server for years, so just trying to understand what to expect. Thanks.

推荐答案

限制是按字节应用的，InnoDB 的限制是 767.所以 255 个字符是 UTF8 的限制(MySQL 的 UTF8 实现只支持最多 3 个字节的字符).如果是单字节字符集，你的索引显然可以更大.

The limit is applied by bytes, and the limit is 767 for InnoDB. So 255 characters is the limit if it's UTF8 (MySQL's implementation of UTF8 only supports up to 3 byte characters). If it's a single byte character set, your indexes can obviously be larger.

如果列大于索引，那么使用索引将永远找不到精确匹配，并且必须进行一些表扫描，尽管它会使用索引来接近范围.

If the column is larger than the index, then exact matches will never be found using the index, and some table scans must be done, though it will use the index for ranges to get close.

LIKE 和其他比较运算符仍将正常工作.它们的性能可能较差.

LIKE and other comparison operators will still work normally. They just might be less performant.

这篇关于MySQL VARCHAR 索引在迁移期间被截断为 255的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！