SQL postgresql 9.2 中 varchar(n) 的最大长度是多少，哪个最好使用 varchar(n) 或 text？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22980068/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
what is the maximum length of varchar(n) in postgresql 9.2 and which is best to use varchar(n) or text?
提问by KRISHNA
Hi I am using postgresql 9.2 and I want to use varchar(n) to store some long string but I don't know the maximum length of character which varchar(n) supports. and which one is better to use so could you please suggest me? thanks
嗨，我正在使用 postgresql 9.2，我想使用 varchar(n) 来存储一些长字符串，但我不知道 varchar(n) 支持的最大字符长度。哪个更好用，你能推荐我吗？谢谢
回答by cowbert
tl;dr: 1 GB (each character (really: codepoint) may be represented by 1 or more bytes, depending on where they are on a unicode plane - assuming a UTF-8 encoded database). You should always use textdatatype for arbitrary-length character data in Postgresql now.
tl; dr：1 GB（每个字符（实际上：代码点）可以由 1 个或更多字节表示，具体取决于它们在 unicode 平面上的位置 - 假设使用 UTF-8 编码的数据库）。现在你应该总是text在 Postgresql 中对任意长度的字符数据使用数据类型。
Explanation:
varchar(n)and textuse the same backend storage type (varlena): a variable length byte array with a 32bit length counter. For indexing behavior textmay even have some performance benefits. It is considered a best practice in Postgres to use texttype for new development; varchar(n)remains for SQL standard support reasons. NB: varchar()(with empty brackets) is a Postgres-specific alias for text.
说明：
varchar(n)并text使用相同的后端存储类型 ( varlena)：具有 32 位长度计数器的可变长度字节数组。对于索引行为text甚至可能有一些性能优势。在 Postgres 中使用text类型进行新开发被认为是最佳实践；varchar(n)仍然是出于 SQL 标准支持的原因。注意：（varchar()带空括号）是 Postgres 特定的text.
See also: http://www.postgresql.org/about/
回答by Dai
According to the official documentation ( http://www.postgresql.org/docs/9.2/static/datatype-character.html):
根据官方文档（http://www.postgresql.org/docs/9.2/static/datatype-character.html）：
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. If you desire to store long strings with no specific upper limit, use text or character varying without a length specifier, rather than making up an arbitrary length limit.)
在任何情况下，可以存储的最长字符串大约是 1 GB。（数据类型声明中允许 n 的最大值小于该值。更改此值没有用，因为对于多字节字符编码，字符数和字节数可能完全不同。如果您希望存储没有特定上限的长字符串，使用没有长度说明符的文本或字符变化，而不是构成任意长度限制。）
Searching online reveals that the maximum value allowed varies depending on the installation and compilation options, some users report a maximum of 10485760characters (10MiB exactly, assuming 1-byte-per-character fixed encoding).
在线搜索显示允许的最大值因安装和编译选项而异，一些用户报告最大10485760字符数（精确到 10MiB，假设每字符固定编码 1 个字节）。
By "the installation and compilation options" I mean that you can always build PostgreSQL from source yourself and before you compile PostgreSQL to make your own database server you can configure how it stores text to change the maximum amount you can store - but if you do this then it means you might run into trouble if you try to use your database files with a "normal", non-customized build of PostgreSQL.
通过“安装和编译选项”，我的意思是您始终可以自己从源代码构建 PostgreSQL，并且在编译 PostgreSQL 以创建自己的数据库服务器之前，您可以配置它存储文本的方式以更改您可以存储的最大数量 - 但如果您这样做这意味着如果您尝试将数据库文件与“正常”、非定制的 PostgreSQL 版本一起使用，您可能会遇到麻烦。
