选择哪个大小的(n)varchar列？

Question

在TDWTF上有一场略微激烈的讨论，关于DB中varchar列的大小问题。

例如，考虑一个包含人名（仅名字，没有姓氏）的字段。很容易看出它不会太长。大多数人的名字都少于10个字符，而20个以上的人很少。如果你将该列设置为varchar(50)，那么它肯定可以容纳你遇到的所有姓名。

但是对于大多数DBMS来说，无论你是varchar(50)还是varchar(255)，它们的大小和速度都没有区别。

那么为什么人们要尽可能地缩小列的大小呢？我知道有些情况下确实需要限制字符串的长度，但大多数情况并非如此。只有在极少数情况下遇到极长的姓名时，才会有更大的好处。

---

补充：人们想要关于“大小或速度没有区别”的声明的参考资料。好的，这里它们：

对于MSSQL：http://msdn.microsoft.com/en-us/library/ms176089.aspx

存储大小是实际输入数据的长度+2个字节。

针对MySQL: http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html

如果列值需要0-255字节，则为L+1字节，如果值可能需要超过255字节，则为L+2字节

我找不到Oracle的文档，也没有使用其他DBMS。但我认为在其他地方也不会有所不同。

- Vilx-

---

我想提供一个参考：“对于大多数DBMS来说，无论您使用varchar（50）还是varchar（255），大小或速度都没有区别”。 - Umair Ahmed

请参见https://dev59.com/wHVC5IYBdhLWcg3wz0l9。 - Lance Roberts

这不是重复问题 - 这是一个更一般性的问题，不仅限于 MSSQL。 - Vilx-

关于姓名的错误观念我认识的一个朋友无法在线预订航班，因为他的名字对于系统来说太长了。他来自泰国，那里的名字通常超过10甚至20个字符。 - Luke H

如果有人需要Oracle的参考资料，这里是来自数据库概念的引用：“对于每一行，Oracle数据库将每个列中的值存储为可变长度字段...例如，假设您声明一个最大大小为50个字符的VARCHAR2列。在单字节字符集中，如果在特定行中仅给出10个字符的VARCHAR2列值，则该行的行片段中的列仅存储10个字符（10个字节），而不是50个。” - default locale

Answer 1

我只能代表Oracle发言。如果您输入值“SMITH”，VARCHAR2（50）和VARCHAR2（255）占用的空间完全相同且执行相同。

然而，通常不建议将所有文本列声明为VARCHAR2（4000），原因是列长度实际上是另一个约束条件。约束是业务规则的数据库实现，因此它们绝对应该在数据库端定义。

例如，您可以在列上定义CHECK约束，以便它可以接受的值仅为“Y”和“N”。这样可以使您的应用程序无需处理“y”和“n”甚至“1”和“0”。检查约束确保您的数据符合预期标准。然后，您的应用程序代码可以对其要处理的数据的性质做出有效的假设。

列长度定义也是同样的情况。您将某些内容声明为VARCHAR2（10），因为您不希望它接受“ABC123ZYX456”（出于任何原因！）的条目。

在澳大利亚，我将STATE列定义为varchar2(3)，因为我不希望人们输入'New South Wales'或'South Australia'。列定义几乎强制它们被输入为'NSW'和'SA'。从这个意义上说，VARCHAR2(3)几乎是一个检查约束，就像实际指定CHECK IN ('NSW'，'SA'，'VIC'等)约束一样。

简而言之，适当的列长度是编码业务规则的一种方式。它们是另一种形式的约束。它们带来了所有约束的优点（并遭受许多相同的缺点）。它们确保，在某种程度上，数据的“清洁度”与“适当”的约束所帮助的一样。

我也不认同把这些东西放在客户端应用程序中更容易修改的论点。如果有20,000人使用应用程序，那就需要20,000个更新。但如果只有一个数据库，那只需要一个更新。如果真的“更容易修改客户端应用程序”，那么数据库可能会被视为一个巨大的位桶，所有聪明的逻辑都由客户端代码处理。这是一个很大的讨论话题，但既然所有关系型数据库管理系统都可以让你在数据库本身中定义约束等内容，那么可以明确地说，这样的基本逻辑应该属于后端。

- Howard Rogers

---

这是一个很好的回答，但它引出了另一个问题：那么，一个名字的合理长度限制是什么？是否存在这样的限制？ - Pavel Minaev

点赞支持一次好的、详细的讨论！ :)然而，在我的原始帖子中，我没有说过任何新的内容。是的，有些情况下，对字符串进行限制是有意义的，比如密码的哈希值或“州的三个字母代码”。但在绝大多数情况下，varchar字段用于名称和描述，这里没有明显的限制。 - Vilx-

虽然我同意这是“另一个”约束，但它肯定不能替代“其他”约束。例如，即使只有3个字符长，接受42！作为STATE列可能是[不合法的]。 - user166390

@PavelMinaev 请查看关于名称的错误观念。 - Luke H

在应用服务器时代，所有的验证等工作都是在服务器上完成的。因此，在数据库上进行约束检查会浪费 CPU 资源。 - Apurva Singh

Answer 2

我听说查询优化器会考虑varchar的长度，尽管我找不到相关参考资料。

定义varchar的长度有助于表达意图。约束越多，数据越可靠。

- Rob Elliott

---

我同意意图，但我不明白这样做如何使数据更可靠。 - Vilx-

我应该说“可预测”而不是“可靠”。变量能够拥有的值越少，它出错的可能性也就越小。 - Rob Elliott

除非您正在使用C/C++或其他需要担心缓冲区溢出的语言进行编程，否则在您的代码中一个字符串将与另一个字符串完全相同。 - Vilx-

Answer 3

一个重要的区别是指定任意大的限制 [例如 VARCHAR(2000)]，和使用不需要限制的数据类型 [例如 VARCHAR(MAX) 或 TEXT]。

PostgreSQL 将所有固定长度的 VARCHAR 基于其无限制的 TEXT 类型，并动态决定每个值如何存储，包括将其存储在页面之外。在这种情况下，长度说明符实际上只是一种约束，其使用实际上是不鼓励的。(ref)

其他 DBMS 要求用户选择是否需要 "无限制"、页面外存储，通常伴随着方便和/或性能的成本。

如果使用 VARCHAR(<n>) 比使用 VARCHAR(MAX) 或 TEXT 有优势，则必须在设计表时为 <n> 选择一个值。假设存在某些表行或索引条目的最大宽度，则必须应用以下约束：

1. <n> 必须小于或等于 <max width>
2. 如果 <n> = <max width>，则表格/索引只能有 1 列
3. 通常情况下，表格/索引只能有 <x> 列，其中（平均而言）<n> = <max width> / <x>

因此，<n> 的值并不仅作为限制条件，选择 <n> 必须成为设计的一部分。（即使在您的 DBMS 中没有硬限制，也可能出于性能原因将宽度保持在某个限制范围内。）

您可以使用上述规则来分配基于表的预期架构（考虑到未来更改的影响）的<n>的最大值。然而，更有意义的是根据每列中预期的数据定义<n>的最小值。很可能，您将扩展到最接近的“圆整数” - 例如，您将始终使用VARCHAR(10)、VARCHAR(50)、VARCHAR(200)或VARCHAR(1000)中最适合的一个。

- IMSoP

---

这适用于SQL Server（至少2008年版本）。 每个记录只允许大约~8k的“保留数据”。 - user166390

Answer 4

那么为什么人们要尽可能地让列变小呢？我不认为应该让它们变得尽可能小，而是适当调整大小。一些缩小(n)varchar的原因如下：

1）使用更大的字段，所有使用数据库的客户端都必须能够处理完整的大小。例如，考虑一个每个字段具有255个字符的美国地址系统：（类似于您所提到的TDWTF，我相信。）

- 名字 - 姓氏 - 地址行1 - 地址行2 - 城市 - 州 - 邮政编码

现在，您的数据输入屏幕将需要允许并显示每个字段的255个字符。不难，但是大型字段不太可能看起来很好。打印发票时，您将需要换行逻辑来处理大型字段。根据工具，不是那么困难。

但是，我不想为信封格式化地址而遇到问题，该地址可以拥有255个字符的每个字段或任何一个字段。如果字段太长而无法适合，您会截断吗？太棒了，有人的地址行1是“ House Number Streat Number ... blah blah blah ... Appartment number 111”。你会切掉重要的公寓号码吗？你会折叠吗？多少？如果你只是无法在信封上的小空间中放下它怎么办？引发异常并让某人手写它？

2）在varchar（50）和varchar（255）中保存10个字符的数据不会影响大小或速度，但允许255个字符可以占用更多的空间。如果所有字段都那么大，则可能会达到SQL Server 2000的大小限制。（我还没有阅读有关2005和2008是否可以处理超过一页的行。）对于Oracle，您可以使用较大的大小使行链接发生，如果有人实际使用了所有可用字符。

3）索引具有比叶子页面更严格的大小限制。如果创建的varchars太大，则可能排除索引，特别是组合索引。

另一方面，我的地址有很长的第1行，我已经对不允许完整输入的网站感到沮丧。

- Shannon Severance

---

如果真的有大小限制（比如信封上可用的空间），那么在那里设置限制是有意义的，这一点我在我的问题中已经提到了。但是我更倾向于在客户端应用程序中设置限制，而不是在数据库中设置限制。因为如果限制发生变化，我只需要更改客户端应用程序即可，这通常比进行数据库模式更新要容易得多。 - Vilx-

2. 行大小是一个要点，但由于大多数真实数据不会达到这些限制，因此很不可能遇到如此大的行。 这些大尺寸更多是为了故障安全而不是实际使用。

- Vilx-

我无法理解VARCHAR的过度大小如何是一种安全措施，因为它打开了某人试图填充符合所有数据类型约束但由于整体大小过大而失败的记录的可能性。 - Shannon Severance

这是为那些名字或地址很长的穷人提供的保险措施。我不明白为什么有人要尝试填满所有字段。也许是黑客？如果是这样，那么如果应用程序对他抛出异常，你又何必在意呢？我认为这对你更有利。 - Vilx-

Answer 5

在我看来，对于使用varchar（max）列，简单的答案是您无法将该列用作索引键，如果需要任何索引，您基本上被迫使用全文检索...无论如何，“右大小”列在想要应用任何索引时都是有意义的；更新可变长度列可能是一项昂贵的操作，因为这些操作不是原地进行的，可能会导致一定程度的碎片化。

所有内容均涉及MS SQ-Server。

- another idiot

---

嗯...看起来你是对的。我漏掉了关于varchar(max)索引的部分。 - Vilx-

Answer 6

如果您要打印标签，通常希望字符串不超过35个字符。这就是为什么您需要对要用于接受将用于打印标签的行的Varchar大小进行一定控制的原因。

- Frank Renta

Answer 7

我会用一个问题来回答你的问题：如果 DBMS 在 varchar(50) 和 varchar(255) 之间没有区别，为什么 DBMS 要让你区分呢？为什么 DBMS 不直接说“使用 varchar 来控制 xxx 个字符以内，text/clob 等用于超过此限制的内容”呢？当然，微软/Oracle/IBM 可能会因为历史原因保留长度定义，但像 MySQL 这样有多个存储后端的 DBMS 为什么每个后端都要实现可定义的字符列长度呢？

- Dan

---

因为有时确实需要长度约束？尽管这样做更好的方式是将其作为“约束”而不是列数据类型参数。好吧，我不知道。 :) - Vilx-

没错。除了向后兼容性之外，没有“表面上”的理由存在这样的功能。这暗示着它存在一个深藏的原因。我想其他数据库管理系统可能会出于“跟风”的原因这样做，但我相信开发人员不会为了添加功能而添加功能。至少我不会 :) - Dan

也许这是一个历史遗留问题，源自于80年代数据库的存储优化器相对较为原始，每一点性能的提升都有所帮助。 - Pavel Minaev

这在PostgreSQL中肯定是正确的，它将VarChar（长度）实现为任意长度文本类型的受约束版本，主要是为了兼容性的原因。然而，即使它在语法上是一个列约束，仍然存在如何最好地使用它的问题。 - IMSoP

Answer 8

如果允许数据长度超过255，并且有人通过MS Access链接到数据，则无法将数据用于连接表（作为备忘录字段）。如果将数据导出到Excel，则每个字段将被限制为255个字符。在创建数据集时应考虑与其他程序的兼容性。
数据质量控制是关于控制进入您环境的数据。您需要存储超过255个字符的内容吗？有时需要超过255个字符的数据，但这些情况应该很少见，并且应该用作可用于分析的字段的支持性补充信息。

- jhogan3

Answer 9

大小确实很重要，并且它可以影响性能！在 MSSQL 中，执行计划制定了一些假设，这些假设可能会对规划 varchar 列时造成伤害。执行计划器预测平均行大小为声明长度的50％，再加上一些开销。因此，如果你声明了 varchar(200)，执行计划器会将行大小估计为大约110字节。如果行填充了80％，则执行计划将被迫使用 tempdb 来排序行，而不是在内存中进行排序。我已经提供了证明和完整示例：https://kisunu.no/index.php?pid=20130。

- Leo