PostgreSQL:文本和varchar之间的区别(字符不同) [英] PostgreSQL: Difference between text and varchar (character varying)

问题描述

text数据类型和character varying(varchar)数据类型之间有什么区别?

What's the difference between the text data type and the character varying (varchar) data types?

根据文档

如果在不使用长度说明符的情况下使用字符变化，则该类型可接受任何大小的字符串.后者是PostgreSQL扩展.

If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension.

和

此外，PostgreSQL提供了文本类型，该文本类型存储任意长度的字符串.尽管类型文本不是SQL标准中的内容，但其他几种SQL数据库管理系统也都具有它.

In addition, PostgreSQL provides the text type, which stores strings of any length. Although the type text is not in the SQL standard, several other SQL database management systems have it as well.

那有什么区别?

推荐答案

没有什么区别，全部都是varlena(可变长度数组).

There is no difference, under the hood it's all varlena (variable length array).

从Depesz中查看此文章: http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/

Check this article from Depesz: http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/

几个亮点:

总结一下:

To sum it all up:

• char(n)–处理小于n的值时会占用太多空间(将其填充到n)，并且由于添加尾随而可能导致细微的错误 空格，再加上更改限制是有问题的
• varchar(n)–在实时环境中更改限制是有问题的(更改表时需要排他锁)
• varchar –就像文本一样
• 文本–对我来说是赢家 –超过(n)个数据类型，因为它没有问题，而超过varchar –因为它具有独特的名称，

• char(n) – takes too much space when dealing with values shorter than n (pads them to n), and can lead to subtle errors because of adding trailing spaces, plus it is problematic to change the limit
• varchar(n) – it's problematic to change the limit in live environment (requires exclusive lock while altering table)
• varchar – just like text
• text – for me a winner – over (n) data types because it lacks their problems, and over varchar – because it has distinct name

本文进行了详细的测试，以显示所有4种数据类型的插入和选择性能相似.它还详细介绍了在需要时限制长度的其他方法.基于函数的约束或域提供了立即增加长度约束的优势，并且在减少字符串长度约束的情况很少见的基础上，depesz得出结论，对于长度限制，其中之一通常是最佳选择.

The article does detailed testing to show that the performance of inserts and selects for all 4 data types are similar. It also takes a detailed look at alternate ways on constraining the length when needed. Function based constraints or domains provide the advantage of instant increase of the length constraint, and on the basis that decreasing a string length constraint is rare, depesz concludes that one of them is usually the best choice for a length limit.

这篇关于PostgreSQL:文本和varchar之间的区别(字符不同)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！