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

问题描述

text 数据类型和 character changed (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 提供了 text 类型，可以存储任意长度的字符串.虽然类型文本不在 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/

几个亮点:

总结一下:

• char(n) – 在处理比 n 短的值时占用太多空间(将它们填充到 n)，并且由于添加尾随可能导致细微错误空格，加上改变限制是有问题的
• varchar(n) – 在实时环境中更改限制是有问题的(更改表时需要排他锁)
• varchar - 就像文本
• text——对我来说是赢家——超过 (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.

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