text
数据类型和 character varying
(varchar
) 数据类型有什么区别?
如果在没有长度说明符的情况下使用字符变化,则该类型接受任何大小的字符串。后者是 PostgreSQL 扩展。
和
此外,PostgreSQL 还提供了 text 类型,可以存储任意长度的字符串。虽然类型文本不在 SQL 标准中,但其他几个 SQL 数据库管理系统也有它。
那么有什么区别呢?
没有区别,在引擎盖下都是 varlena
(variable length array)。
查看 Depesz 的这篇文章:http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/
几个亮点:
总结一下:char(n) – 处理小于 n 的值时占用太多空间(将它们填充到 n),并且由于添加尾随空格可能导致细微错误,而且更改限制 varchar 是有问题的(n) – 在实时环境中更改限制是有问题的(在更改表时需要排他锁) varchar – 就像文本文本 – 对我来说是赢家 – 超过 (n) 数据类型,因为它没有问题,超过 varchar – 因为它有独特的名字
这篇文章做了详细的测试,表明所有 4 种数据类型的插入和选择的性能是相似的。它还详细介绍了在需要时限制长度的替代方法。基于函数的约束或域提供了立即增加长度约束的优势,并且基于减少字符串长度约束的情况很少见,depesz 得出结论,其中之一通常是长度限制的最佳选择。
正如文档中的“Character Types”所指出的,varchar(n)
、char(n)
和 text
都以相同的方式存储。唯一的区别是需要额外的周期来检查长度(如果给定),以及如果 char(n)
需要填充则需要额外的空间和时间。
但是,当您只需要存储单个字符时,使用特殊类型 "char"
会有一点性能优势(保留双引号——它们是类型名称的一部分)。您可以更快地访问该字段,并且没有存储长度的开销。
我刚刚制作了一张从小写字母中选择的 1,000,000 个随机 "char"
的表格。获取频率分布 (select count(*), field ... group by field
) 的查询大约需要 650 毫秒,而使用 text
字段在相同数据上大约需要 760 毫秒。
"char"
不是 char
??它在当今的 PostgreSQL 11+ 中有效吗? ... 是:"类型 "char"
(注意引号)与 char(1) 的不同之处在于它只使用一个字节的存储空间。它在系统目录内部用作 枚举类型。”,guide/datatype-character。
(此答案是 Wiki,您可以编辑 - 请更正和改进!)
更新 2016 年的基准 (pg9.5+)
并使用“纯 SQL”基准(没有任何外部脚本)
使用任何带有 UTF8 主要基准的 string_generator:
2.1。插入
2.2. SELECT比较和计数
CREATE FUNCTION string_generator(int DEFAULT 20,int DEFAULT 10) RETURNS text AS $f$
SELECT array_to_string( array_agg(
substring(md5(random()::text),1,$1)||chr( 9824 + (random()*10)::int )
), ' ' ) as s
FROM generate_series(1, $2) i(x);
$f$ LANGUAGE SQL IMMUTABLE;
准备特定的测试(示例)
DROP TABLE IF EXISTS test;
-- CREATE TABLE test ( f varchar(500));
-- CREATE TABLE test ( f text);
CREATE TABLE test ( f text CHECK(char_length(f)<=500) );
执行基本测试:
INSERT INTO test
SELECT string_generator(20+(random()*(i%11))::int)
FROM generate_series(1, 99000) t(i);
和其他测试,
CREATE INDEX q on test (f);
SELECT count(*) FROM (
SELECT substring(f,1,1) || f FROM test WHERE f<'a0' ORDER BY 1 LIMIT 80000
) t;
...并使用 EXPLAIN ANALYZE
。
2018 年再次更新 (pg10)
稍加修改即可添加 2018 年的结果并加强建议。
2016 年和 2018 年的结果
我的结果,经过平均,在许多机器和许多测试中:都一样(统计上小于标准偏差)。
推荐
使用文本数据类型,避免使用旧的 varchar(x),因为有时它不是标准,例如在 CREATE FUNCTION 子句中 varchar(x)≠varchar(y)。
通过 CREATE TABLE 中的 CHECK 子句表达限制(具有相同的 varchar 性能!),例如 CHECK(char_length(x)<=10)。在 INSERT/UPDATE 性能损失可以忽略不计的情况下,您还可以控制范围和字符串结构,例如 CHECK(char_length(x)>5 AND char_length(x)<=20 AND x LIKE 'Hello%')
"char"
,而不是 char
,即使在现在的 PostgreSQL 11+ 中也是如此。正如 guide/datatype-character 所说的 “类型 "char"
(注意引号)与 char(1) 的不同之处在于它只使用一个字节的存储空间。它在系统目录内部用作 简单的枚举类型。”。
关于 PostgreSQL 手册
这三种类型之间没有性能差异,除了在使用空白填充类型时增加了存储空间,以及在存储到长度受限的列时需要一些额外的 CPU 周期来检查长度。虽然 character(n) 在其他一些数据库系统中具有性能优势,但在 PostgreSQL 中没有这样的优势;事实上 character(n) 通常是三个中最慢的,因为它有额外的存储成本。在大多数情况下,应改为使用文本或字符变化。
我通常使用文本
参考文献:http://www.postgresql.org/docs/current/static/datatype-character.html
在我看来,varchar(n)
有它自己的优势。是的,它们都使用相同的底层类型等等。但是,需要指出的是,PostgreSQL 中的索引的大小限制为每行 2712 字节。
TL;DR:如果您使用 text
类型没有约束 并且在这些列上有索引,则很可能您的某些列达到了这个限制并且当您尝试插入数据时出现错误,但使用 varchar(n)
,您可以阻止它。
更多细节:这里的问题是PostgreSQL在为text
类型或n
大于2712的varchar(n)
创建索引时没有给出任何异常。但是,它会给出错误当尝试插入压缩大小大于 2712 的记录时。这意味着您可以轻松插入由重复字符组成的 100.000 个字符的字符串,因为它将被压缩远低于 2712,但您可能无法插入一些 4000 个字符的字符串,因为压缩后的大小大于 2712 字节。使用其中 n
不会太多大于 2712 的 varchar(n)
,您可以避免这些错误。
text 和 varchar 具有不同的隐式类型转换。我注意到的最大影响是处理尾随空格。例如 ...
select ' '::char = ' '::varchar, ' '::char = ' '::text, ' '::varchar = ' '::text
如您所料,返回 true, false, true
而不是 true, true, true
。
=
运算符不仅在比较内容,而且还进行一些转换以找到值的通用类型。这在各种语言中是很常见的行为,并且使用的转换也因语言而异。例如,在 JavaScript 中,您可以看到 [0 == '0.0', 0 == '0', '0.0' == '0']
-> [true, true, false]
有点 OT:如果您使用的是 Rails,网页的标准格式可能会有所不同。对于数据输入表单,text
框是可滚动的,但 character varying
(Rails string
) 框是单行的。显示视图只要需要。
http://www.sqlines.com/postgresql/datatypes/text 的一个很好的解释:
TEXT 和 VARCHAR(n) 之间的唯一区别是您可以限制 VARCHAR 列的最大长度,例如,VARCHAR(255) 不允许插入长度超过 255 个字符的字符串。 TEXT 和 VARCHAR 的上限都是 1 Gb,它们之间没有性能差异(根据 PostgreSQL 文档)。
由于使用 varchar 而不是 PostgreSQL 数组的文本,我浪费了太多时间。
PostgreSQL 数组运算符不适用于字符串列。有关详细信息,请参阅以下链接:(https://github.com/rails/rails/issues/13127) 和 (http://adamsanderson.github.io/railsconf_2013/?full#10)。
如果您仅使用 TEXT 类型,则在使用 AWS Database Migration Service 时可能会遇到问题:
使用了大对象 (LOB),但目标 LOB 列不可为空
由于它们未知且有时尺寸很大,大对象 (LOB) 比标准对象需要更多的处理和资源。为了帮助调整包含 LOB 的系统的迁移,AWS DMS 提供了以下选项
如果您只对所有事情都坚持使用 PostgreSQL,那么您可能没问题。但是,如果您要通过 ODBC 或 DMS 等外部工具与您的数据库进行交互,您应该考虑对所有内容都使用不使用 TEXT。
character varying(n)
、varchar(n)
-(两者相同)。 value 将被截断为 n 个字符而不会引发错误。
character(n)
, char(n)
- (两者相同)。固定长度,并将用空白填充直到长度结束。
text
- 无限长度。
例子:
Table test:
a character(7)
b varchar(7)
insert "ok " to a
insert "ok " to b
我们得到结果:
a | (a)char_length | b | (b)char_length
----------+----------------+-------+----------------
"ok "| 7 | "ok" | 2
text
永远不应该,永远 被认为是开箱即用的“varchar 的赢家”,因为它允许我输入任意长度的字符串,但恰恰相反,您应该真的< /b> 在允许您的用户输入任意长度的字符串之前,请考虑您想要存储什么样的数据。而NO,“让前端处理”绝对是不可接受的,而且是一种非常糟糕的开发实践。现在看到很多开发人员这样做真的很令人惊讶。