SQL varchar列长度的最佳实践

每当建立一个新的SQL表或向现有表添加一个新的varchar列时，我想知道一件事:length的最佳值是多少。

所以，假设你有一个名为name的列，类型为varchar。所以，你必须选择长度。我想不出一个20个字符的名字，但你永远不会知道。但不是用20，我总是四舍五入到下一个2^n。在这种情况下，我将选择32作为长度。我这样做是因为从计算机科学家的角度来看，数字2^n在我看来比其他数字更even，我只是假设下面的架构可以比其他数字更好地处理这些数字。

另一方面，以MSSQL服务器为例，当您选择创建varchar列时，将默认长度值设置为50。这让我开始思考。为什么50 ?它只是一个随机数，还是基于平均列长，还是什么?

也可能是——或者可能是——不同的SQL服务器实现(如MySQL, MSSQL, Postgres，…)有不同的最佳列长度值。

297538 次浏览
小开

最佳值是基础域中定义的数据的正确值。

对于某些域，VARCHAR(10)适合Name属性，对于其他域，VARCHAR(255)可能是最佳选择。

小开

VARCHAR(255)和VARCHAR(2)占用相同数量的磁盘空间!所以限制它的唯一原因是如果你有特定的需要让它更小。否则都设为255。

具体来说，在进行排序时，较大的列确实会占用更多的空间，因此如果这会影响性能，那么您需要担心它，并将其减小。但如果你只从表中选择了一行，那么你可以把它们都设为255，这没有关系。

看:MySQL的最佳varchar大小是多少?

小开
最佳答案

据我所知，没有一个DBMS有任何“优化”可以使长度为2^n的VARCHAR比长度为非2次幂的max执行得更好。

我认为早期的SQL Server版本实际上对待长度为255的VARCHAR与最大长度更高的VARCHAR不同。我不知道现在是不是还是这样。

对于几乎所有的DBMS，所需的实际存储空间仅由您放入其中的字符数决定，而不是您定义的max长度。因此，从存储的角度来看(很可能也是性能的角度)，将列声明为VARCHAR(100)或VARCHAR(500)没有任何区别。

你应该看到为VARCHAR列提供的max长度是一种约束(或业务规则)，而不是技术/物理的东西。

对于PostgreSQL，最好的设置是使用没有长度限制的text和限制您的业务所需的字符数量的CHECK CONSTRAINT。

如果需求发生变化，更改检查约束比更改表要快得多(因为表不需要重写)。

同样的方法也适用于Oracle和其他应用程序——在Oracle中，它将是VARCHAR(4000)而不是text。

我不知道在SQL Server中的VARCHAR(max)和VARCHAR(500)之间是否有物理存储的区别。但显然，与varchar(8000)相比，使用varchar(max)会有性能影响。

参见这个链接(由Erwin Brandstetter作为评论发布)

编辑2013-09-22

关于bigown的评论:

在9.2之前的Postgres版本中(当我写初始答案时还不可用)，对列定义做了的更改将重写整个表，参见例如在这里。从9.2开始，这种情况不再存在，快速测试证实，对于一个有120万行的表，增加列大小确实只需要0.5秒。

对于Oracle来说，这似乎也是正确的，通过改变一个大表的varchar列所花费的时间来判断。但我找不到任何相关的参考资料。

MySQL 手册上说 "在大多数情况下，ALTER TABLE会对原始表进行临时复制"。我自己的测试也证实了这一点:在一个有120万行的表上运行ALTER TABLE来增加列的大小需要1.5分钟(与我用Postgres进行的测试相同)。然而，在MySQL中，你可以使用“变通方法”来使用检查约束来限制列中的字符数。

对于SQL Server，我找不到一个明确的语句，但增加varchar列的大小的执行时间(同样是上面的120万行表)表明发生了没有重写。

编辑2017-01-24

看来我对SQL Server的看法(至少部分)是错误的。参见这是艾伦·伯特兰的回答，其中显示了nvarchar或varchar列的声明长度对性能有巨大的影响。

小开

经常向您的业务领域专家咨询。如果你是这样，那就寻找一个行业标准。例如，如果有问题的域名是自然人的姓氏，那么对于英国企业，我会去英国Govtalk个人信息数据标准目录并发现一个姓氏将在1到35个字符之间。

小开

添加到a_horis_with_no_name的答案中，您可能会发现以下内容令人感兴趣……

不管你是否声明一个列 VARCHAR(100) or VACHAR(500).

-- try to create a table with max varchar length
drop table if exists foo;
create table foo(name varchar(65535) not null)engine=innodb;


MySQL Database Error: Row size too large.


-- try to create a table with max varchar length - 2 bytes for the length
drop table if exists foo;
create table foo(name varchar(65533) not null)engine=innodb;


Executed Successfully


-- try to create a table with max varchar length with nullable field
drop table if exists foo;
create table foo(name varchar(65533))engine=innodb;


MySQL Database Error: Row size too large.


-- try to create a table with max varchar length with nullable field
drop table if exists foo;
create table foo(name varchar(65532))engine=innodb;


Executed Successfully

不要忘记长度字节(s)和可空字节，这样:

name varchar(100) not null将是1字节(长度)+最多100个字符(latin1)

name varchar(500) not null将是2个字节(长度)+最多500个字符(latin1)

name varchar(65533) not null将是2字节(长度)+最多65533个字符(latin1)

name varchar(65532)将是2个字节(长度)+最多65532个字符(latin1) + 1个空字节

希望这对你有所帮助。

小开

每当我建立一个新的SQL表时，我都觉得2^n更“均匀”……但总结一下这里的答案，简单地定义varchar(2^n)甚至varchar(MAX)对存储空间没有显著影响。

也就是说，在设置较高的varchar()限制时，您仍然应该预料到对存储和性能的潜在影响。例如，假设您创建了一个varchar(MAX)列来保存具有全文索引的产品描述。如果99%的描述只有500个字符长，然后突然有人用维基百科文章替换了这些描述，您可能会注意到意想不到的存储和性能显著下降。

比尔·卡尔文说的另一件事:

有一个可能的性能影响:在MySQL中，临时表 和MEMORY表将VARCHAR列存储为固定长度的列， 填充到最大长度。如果你经常设计VARCHAR列 大于您所需要的最大大小，您将消耗更多的内存 那你就必须这么做。这将影响缓存效率，排序速度等

基本上，只是在稍微大一点的规模上提出合理的业务限制和错误。正如@onedaywhen指出的那样，英国的姓通常在1-35个字符之间。如果你决定将它设为varchar(64)，你不会真的伤害到任何东西……除非你存储的这家伙的姓据说最多有666个字符长。在这种情况下，也许varchar(1028)更有意义。

如果有用的话，下面是varchar 2^5到2^10如果填满的样子:

varchar(32)     Lorem ipsum dolor sit amet amet.


varchar(64)     Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie


varchar(128)    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
vestibulum massa. Nullam dignissim elementum molestie. Vehiculas


varchar(256)    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
vestibulum massa. Nullam dignissim elementum molestie. Vehiculas
velit metus, sit amet tristique purus condimentum eleifend. Quis
que mollis magna vel massa malesuada bibendum. Proinde tincidunt


varchar(512)    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
vestibulum massa. Nullam dignissim elementum molestie. Vehiculas
velit metus, sit amet tristique purus condimentum eleifend. Quis
que mollis magna vel massa malesuada bibendum. Proinde tincidunt
dolor tellus, sit amet porta neque varius vitae. Seduse molestie
lacus id lacinia tempus. Vestibulum accumsan facilisis lorem, et
mollis diam pretium gravida. In facilisis vitae tortor id vulput
ate. Proin ornare arcu in sollicitudin pharetra. Crasti molestie


varchar(1024)   Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
vestibulum massa. Nullam dignissim elementum molestie. Vehiculas
velit metus, sit amet tristique purus condimentum eleifend. Quis
que mollis magna vel massa malesuada bibendum. Proinde tincidunt
dolor tellus, sit amet porta neque varius vitae. Seduse molestie
lacus id lacinia tempus. Vestibulum accumsan facilisis lorem, et
mollis diam pretium gravida. In facilisis vitae tortor id vulput
ate. Proin ornare arcu in sollicitudin pharetra. Crasti molestie
dapibus leo lobortis eleifend. Vivamus vitae diam turpis. Vivamu
nec tristique magna, vel tincidunt diam. Maecenas elementum semi
quam. In ut est porttitor, sagittis nulla id, fermentum turpist.
Curabitur pretium nibh a imperdiet cursus. Sed at vulputate este
proin fermentum pretium justo, ac malesuada eros et Pellentesque
vulputate hendrerit molestie. Aenean imperdiet a enim at finibus
fusce ut ullamcorper risus, a cursus massa. Nunc non dapibus vel
Lorem ipsum dolor sit amet, consectetur Praesent ut ultrices sit
小开

我最近没有检查过这一点，但我知道在过去使用Oracle时，JDBC驱动程序会在查询执行期间保留一块内存来保存返回的结果集。内存块的大小取决于列定义和取值大小。所以varchar2列的长度会影响内存的预留量。这在几年前给我带来了严重的性能问题，因为我们总是使用varchar2(4000)(当时的最大值)，而且垃圾收集的效率比现在低得多。

小开

在某种意义上你是对的，尽管任何低于2^8个字符的数据仍然会被注册为一个字节。

如果你考虑到基字符会留下VARCHAR <255表示消耗相同的空间。

255是一个很好的基线定义，除非您特别希望减少过多的输入。


提交答案