Postgres SQL中的字符串字段长度

Question

我在SQL数据库中有一个字符串，表示一个url。有些url很短，有些很长。我真的不知道我可能会遇到的最长的URL是什么，所以为了安全起见，我会取一个很大的值，比如256或512。

当我定义最大字符串长度时(例如，使用SQLAlchemy )：

url_field = Column(String(256))

这会占用每一行的空间(存储)吗，即使实际的字符串更短？

我假设这与实现细节有关。我正在使用postgreSQL，但我对sqlite，mysql也很感兴趣。

Answer 1

通常，数据库存储引擎可以做很多你意想不到的事情。但基本上，有两种类型的文本字段，这两种字段给出了内部将发生什么的提示。

char和varchar。Char将为您提供一个固定的字段列，并且根据sql会话中的选项，您可能会收到空格填充的字符串，也可能没有。对于最大长度不超过某一特定长度的文本字段，可以使用Varchar。

可以在块的外部存储一个指针，这样块就可以在查询时保持一个可预测的大小--但这是一个实现细节，可能会因db而异。

Answer 2

在PostgreSQL中，character(n)基本上就是在输入/输出上填充空格的varchar。它很笨拙，应该避免。它消耗的存储空间与填充到最大长度的varchar或text字段相同(见下文)。char(n)是一个历史上的瘤，应该避免-至少在PostgreSQL中它没有任何优势，并且与left(...)之类的东西有一些奇怪的怪癖。

varchar(n)、varchar和text都使用相同的存储空间--没有填充的字符串长度。它只使用字符实际需要的存储空间，而不考虑长度限制。此外，如果字符串为null，PostgreSQL根本不会为其存储值(甚至不是长度标头)，它只是在记录的空位图中设置空位。

限定的varchar(n)基本上与非限定的varchar相同，只是在length(colname) < n上有一个check约束。

不管其他一些评论/答案是怎么说的，char(n)、varchar、varchar(n)和text都是TOASTable类型。它们都可以存储在行外和/或压缩。要控制存储，请使用ALTER TABLE ... ALTER COLUMN ... SET STORAGE。

如果不知道所需的最大长度，只需使用text或不合格的varchar即可。没有空间上的惩罚。

有关更多详细信息，请参阅the documentation on character data types，有关它们如何存储的一些内部信息，请参阅database physical storage，特别是TOAST。

演示：

CREATE TABLE somechars(c10 char(10), vc10 varchar(10), vc varchar, t text);
insert into somechars(c10) values ('  abcdef ');
insert into somechars(vc10) values ('  abcdef ');
insert into somechars(vc) values ('  abcdef ');
insert into somechars(t) values ('  abcdef ');

此查询针对每一列的输出：

SELECT 'c10', pg_column_size(c10), octet_length(c10), length(c10) 
from somechars where c10 is not null;

是：

 ?column? | pg_column_size | octet_length | length 
 c10      |             11 |           10 |      8
 vc10     |             10 |            9 |      9
 vc       |             10 |            9 |      9
 t        |             10 |            9 |      9

pg_column_size是字段中基准面的磁盘大小。octet_length是不带报头的未压缩大小。length是“逻辑”字符串长度。

如您所见，char字段是填充的。这既浪费了空间，又给出了一个对length来说非常令人惊讶的结果，因为输入的字符是9个字符，而不是8个字符。这是因为Pg不能区分您自己输入的前导空格和它作为填充添加的前导空格。

所以，不要使用char(n)。

顺便说一句，如果我在设计一个数据库，我永远不会使用varchar(n)或char(n)。如果应用程序对值有要求，我只需使用text类型并添加适当的check约束。我认为varchar(n)在标准中有一点瑕疵，尽管我猜它对于具有大小限制可能会影响存储的磁盘布局的数据库很有用。

Answer 3

sqllite和mysql都应用postgreSQL标准来存储varchar和chars。这基本上是这样的：

SQL定义了两种主要的字符类型: character varying(n)和character(n)，其中n是正整数。这两种类型都可以存储长度不超过n个字符的字符串。尝试将更长的字符串存储到这些类型的列中将导致错误，除非多余的字符都是空格，在这种情况下，字符串将被截断为最大长度。(这个有点奇怪的异常是SQL标准所要求的。)如果要存储的字符串比声明的长度短，则character类型的值将以空格填充；character varying类型的值将只存储较短的字符串。

如果显式地将值强制转换为字符变化(N)或字符(N)，则过长值将被截断为n个字符，而不会引发错误。(这也是SQL标准所要求的。)

符号varchar(n)和char(n)分别是字符变化(N)和字符(N)的别名。没有长度说明符的字符等同于字符(1)。如果在不使用长度说明符的情况下使用字符变化，则类型接受任何大小的字符串。后者是一个PostgreSQL扩展。

参考资料：

• Pg docs: Character Types