使用数据类型“文本"的任何缺点用于存储字符串? [英] Any downsides of using data type "text" for storing strings?

问题描述

根据PostgreSQL 文档，它们支持字符数据的 3 种数据类型:

According to the PostgreSQL Documentation, they support 3 data-types for character data:

character varying(n), varchar(n)  variable-length with limit
character(n), char(n)             fixed-length, blank padded
text                              variable unlimited length

在我的应用程序中，我遇到了一些令人不快的情况，其中插入/更新查询失败，因为要插入的所需文本超过了 varchar(n) 或 char(n) 限制.

In my application, I came across few unpleasant scenarios where insert/update queries failed as the desired text to be inserted exceeded the varchar(n) or char(n) limit.

对于这种情况，将此类列的数据类型更改为 text 就足够了.

For such cases, changing the data type of such columns to text sufficed.

我的问题是:

• 如果我们将每个字符存储列的数据类型概括并更改为text，在性能/内存方面是否有任何缺点?
• 如果数据类型为 text 的列每次存储 10 个或更少的字符，我应该选择 text 还是 varchar(10)?
• 如果我选择 text 有什么缺点?

• If we generalize and change the data type of every character storing column to text, is there any downside in terms of performance/memory?
• If a column with data type text stores 10 or less characters every time, should I go for text or varchar(10)?
• If I go for text what's the downside?

推荐答案

一般来说，使用 text 在性能/内存方面.相反:text 是最佳选择.其他类型或多或少有相关的缺点.text 字面上是 首选"Postgres 类型系统中字符串类型之间的类型，这会影响函数或运算符的类型解析.

Generally, there is no downside to using text in terms of performance/memory. On the contrary: text is the optimum. Other types have more or less relevant downsides. text is literally the "preferred" type among string types in the Postgres type system, which can affect function or operator type resolution.

特别是，从不使用char(n)(character(n))，除非您知道自己在做什么.char 或 character 只是 character(1) 的缩写，所以都是一样的.内部名称是 bpchar(代表空白填充字符").该类型仅用于与旧代码和标准兼容.现在已经没有什么意义了，浪费内存，而且很可能会引起麻烦:

In particular, never use char(n) (alias for character(n)), unless you know what you are doing. char or character are just short for character(1), so all the same. The internal name is bpchar (stands fore "blank-padded character"). The type is only there for compatibility with old code and standards. It makes very little sense nowadays, wastes memory and is likely to cause trouble:

• 比较 varchar 和 char
• Postgres SQL 中的字符串字段长度

您可以使用 varchar(n) 带长度修饰符(character changed(n) 的别名).但是 varchar(255) 通常表示从其他 RDBMS 遗留下来的误解，它可能是性能的局部最优.在 Postgres 中，长度修饰符 (255) 没有特殊意义，很少有意义.

You can use varchar(n) with length modifier (alias for character varying(n)). But varchar(255) typically indicates a misunderstanding carried over from other RDBMS where it might be a local optimum for performance. In Postgres, the length modifier (255) has no special meaning and rarely makes sense.

• 是否应该为 VARCHAR 列添加任意长度限制?

旧版本在稍后尝试更改 varchar(n) 的长度修饰符时会导致各种问题.大多数这些在现代 Postgres 中已经得到缓解，但是 text 或 varchar(字符变化的别名)没有长度说明符(和一个CHECK 约束 代替)从不有任何这些问题.

Older versions caused various problems when trying to change the length modifier of varchar(n) later. Most of those have been alleviated in modern Postgres, but text or varchar (alias for character varying) without length specifier (and a CHECK constraint instead) never had any of these issues.

CHECK 约束同样快，并且不太可能导致依赖视图、函数、FK 约束等的问题，这些依赖于列类型.它可以做的不仅仅是强制最大字符长度 - 任何可以放入布尔表达式的内容.见:

A CHECK constraint is just as fast and less likely to cause troubles with depending views, functions, FK constraints etc. which depend on the column type. And it can do more than just enforce a maximum character length - anything you can put into a boolean expression. See:

• 更改视图中使用的 PostgreSQL 列

最后，还有"char"(带双引号):单个 ASCII 字母的 1 字节数据类型，用作廉价的内部枚举类型.

Finally, there is also "char" (with double-quotes): a 1-byte data type for a single ASCII letter used as cheap internal enumeration type.

对于 Postgres 中的字符数据，除了 text 之外，我很少使用任何东西.

I rarely use anything but text for character data in Postgres.

这篇关于使用数据类型“文本"的任何缺点用于存储字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！