为什么要为字符变化类型指定长度 [英] Why specify a length for character varying types
问题描述
请参阅字符类型上的Postgres文档，在指定字符变化（varchar）类型的长度方面还不清楚。
Referring to the Postgres Documentation on Character Types, I am unclear on the point of specifying a length for character varying (varchar) types.
假设：
- 字符串的长度与应用程序无关。
- 您不在乎有人将最大大小放入数据库中
- 您有无限的硬盘空间
它确实提到：
短字符串（最多126个字节）的存储要求为1个字节
加上实际字符串，其中包括
个字符的空格。较长的字符串的开销为4个字节，而不是1个字节。
较长的字符串由系统自动压缩，因此磁盘上的
物理需求可能会更少。非常长的值也存储在后台表中，因此不会干扰
快速访问较短的列值。无论如何，可以存储的最长
字符串约为1 GB。 （在数据类型声明中允许n的最大值
小于该值的
。更改此值无用，因为使用多字节
字符编码的字符数和字节可以完全不同
。
The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which includes the space padding in the case of character. Longer strings have 4 bytes of overhead instead of 1. Long strings are compressed by the system automatically, so the physical requirement on disk might be less. Very long values are also stored in background tables so that they do not interfere with rapid access to shorter column values. In any case, the longest possible character string that can be stored is about 1 GB. (The maximum value that will be allowed for n in the data type declaration is less than that. It wouldn't be useful to change this because with multibyte character encodings the number of characters and bytes can be quite different.
这是关于字符串的大小，而不是字段的大小（即听起来好像总是会压缩大型varchar字段中的大字符串，而不是压缩大型varchar字段中的小字符串？）
This talks about the size of string, not the size of field, (i.e. sounds like it will always compress a large string in a large varchar field, but not a small string in a large varchar field?)
我问这个问题，因为更容易（而且懒惰）指定更大的大小，因此您不必担心字符串太大。例如，如果我为地点名称指定varchar（50），我将获得具有更多字符的位置（例如Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogogoch ），但是如果我指定varchar（100）或varchar（500），则我不太会遇到这个问题。
I ask this question as it would be much easier (and lazy) to specify a much larger size so you never have to worry about having a string too large. For example, if I specify varchar(50) for a place name I will get locations that have more characters (e.g. Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch), but if I specify varchar(100) or varchar(500), I'm less likley to get that problem.
因此，您会在varchar之间获得性能下降吗？ （500）和（任意）varchar（5000000）或text（）（如果您最大的字符串说的是400个字符长？
So would you get a performance hit between varchar(500) and (arbitrarily) varchar(5000000) or text() if your largest string was say 400 characters long?
如果有人对此有答案并且知道答案的话，也就不感兴趣了）
Also out of interest if anyone has the answer to this AND knows the answer to this for other databases, please add that too.
我已经在Google上进行了搜索，但是找不到足够的技术说明。
I have googled, but not found a sufficiently technical explanation.
推荐答案
我的理解是，约束对于数据完整性很有用，因此我使用列大小来验证较低层的数据项，并更好地验证数据项。描述数据模型。
My understanding is that having constraints is useful for data integrity, therefore I use column sizes to both validate the data items at the lower layer, and to better describe the data model.
此事的一些链接：
- VARCHAR(n) Considered Harmful
- CHAR(x) vs. VARCHAR(x) vs. VARCHAR vs. TEXT
- In Defense of varchar(x)
这篇关于为什么要为字符变化类型指定长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
