当我们存储长度小于10的字符串时，varchar（10）和varchar（1000）之间有什么区别吗？ [英] Is there any difference between varchar(10) and varchar(1000) when we store string whose length is less than 10?
问题描述
我了解 char 和 varchar 之间的区别，但是我不确定的确切含义varchar最大长度。
当我们存储长度小于10的 string 时， varchar（10）和 varchar（100）之间有什么区别？
我认为这两种情况使用相同的空间并具有相同的性能。如果是这样，为什么我们需要varchar max limit？
仅使用 varchar 代替 varchar（xxx）是否足够？
（已添加）我正在使用MySQL 5.0.67
这完全取决于所使用的DBMS引擎。 SQL本身并没有规定事物的物理存储方式，也没有规定逻辑上的存储方式。
例如，您的DBMS可以在行中分配最大大小的空间，再加上一些额外的字节来存储长度。在这种情况下， varchar（10）和 varchar（1000）之间会有很大的差异，因为您会浪费每行相当多的空间。
或者，它可以对 varchar 数据使用缓冲池并存储行中只有长度和缓冲池起始地址。在这种情况下，每一行都将为 varchar 列存储大小相同的信息，而不管其大小如何，但是将增加一个步骤来提取该列中的实际数据（跟随链接到缓冲池）。
使用 varchar 的原因正是将其命名为 varchar 。它允许您存储大小可变的数据元素。通常， char（10）会给您十个字符，无论如何，如果您插入较短的内容，请用空格填充。您可以在提取时修剪掉尾随空格，但是如果要存储的数据实际上是 hello ， with 您想要保留的尾随空间。
一个不错的DBMS引擎可能会根据 varchar的最大大小来做出权衡。 列。对于简短的，它可以将其直接内联存储在行中，并消耗额外的字节作为大小。
更长的 varchar 列可以外包到一个单独的缓冲池中，以确保行读取保持高效（至少直到 需要大的 varchar
您需要做的是重新询问特定DBMS的问题，以便获得更有针对性的答案。
或者说实话，将您的数据库设计为仅存储最大大小。如果您知道它是10，那么 varchar（1000）就是浪费。如果将来需要扩大此列，那么现在是时候 了（而不是现在）（请参阅 YAGNI ）。
对于MySQL，您需要查看 第14章存储引擎 。
它涵盖了MySQL使用的各种存储引擎（例如InnoDB和MyISAM），并且看起来足够深入，您可以看到信息的物理存储方式。
例如，在MyISAM中，表中存在可变长度数据（ varchar ）通常意味着动态表。这遵循大致类似于我上面提到的缓冲池概念的方案，其优点是可变大小的列浪费了更少的空间，缺点是行可能变得碎片化。
<
可以找到有关InnoDB物理结构的信息< a href = http://dev.mysql.com/doc/refman/5.7/en/innodb-table-and-index.html rel = nofollow>此处。根据使用的是Antelope还是Barracuda文件格式，最终会出现所有信息都是物理行或缓冲池的情况，类似于MyISAM对动态和静态的区别。
I understand the difference between char and varchar but I'm not sure of exact meaning of varchar maximum length.
When we store string whose length is less than 10, is there any difference between varchar(10) and varchar(100)?
I think both cases use same space and have same performance. If so, why do we need varchar max limit?
Is it enough to use just "varchar" instead of "varchar(xxx)"?
(added) I'm using MySQL 5.0.67
This depends entirely on the DBMS engine being used. SQL itself does not mandate how things are stored physically, just how they're seen logically.
For example, your DBMS may allocate space in the row for the maximum size, plus some extra bytes to store the length. In that case, there would be a big difference between varchar(10) and varchar(1000) since you would waste quite a bit of space per row.
Alternatively, it may use a buffer pool for the varchar data and store only the length and the buffer pool "starting address" in the row. In that case, every single row would store identically-sized information for a varchar column regardless of its size, but there would be an added step to extract the actual data in that column (following the link to the buffer pool).
The reason you use a varchar is exactly why it's named varchar. It allows you to store variable-sized data elements. Typically, char(10) gives you ten characters, no matter what, padding it with spaces if you insert something shorter. You can trim trailing spaces off as you extract it but that won't work so well if the data you want to store is actually "hello ", with a trailing space you want preserved.
A decent DBMS engine may decide to make a trade-off depending on the maximum size of the varchar column. For short ones, it could just store it inline in the row and consume the extra bytes for the size.
Longer varchar columns could be "outsourced" to a separate buffer pool to ensure row-reading is kept efficient (at least until you need the large varchar column, anyway).
What you need to do is re-ask the question for your specific DBMS so as to get a more targeted answer.
Or, in all honesty, engineer your database to only store the maximum size. If you know it's 10, then varchar(1000) is a waste. If, in the future, you need to enlarge the column, that is the time to do it, rather than now (see YAGNI).
For MySQL, you'll want to look at Chapter 14 Storage Engines of the online documentation.
It covers the various storage engines (such as InnoDB and MyISAM) that MySQL uses and, looking deep enough, you can see how the information is physically stored.
For example, in MyISAM, the presence of variable length data in a table (varchar included) usually means dynamic tables. This follows a scheme roughly analogous to the buffer pool concept I mentioned above, with the advantage that less space is wasted for variable sized columns, and the disadvantage that rows may become fragmented.
The other storage format (discounting compressed format since it's only really used for read-only tables) is the static one, where data is stored in a single physical row.
Information on the InnoDB physical structures can be found here. Depending on whether you use the Antelope or Barracuda file format, you end up with the "all information is a physical row" or "buffer pool" situation, similar to the MyISAM distinction between dynamic and static.
这篇关于当我们存储长度小于10的字符串时，varchar（10）和varchar（1000）之间有什么区别吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
